Tham khảo tài liệu 'mastering sql server 2000- p7', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả | 270 CHAPTER 8 ADVANCED TRANSACT-SQL Committing a nested transaction does not write the changes from that transaction permanently to the database it merely makes them available to the outer transaction. Suppose you have the following SQL batch BEGIN TRANSACTION UPDATE titles SET price WHERE title_id TC7777 BEGIN TRANSACTION UPDATE titles SET type potboiler WHERE title_id TC7777 COMMIT TRANSACTION ROLLBACK TRANSACTION In this case the COMMIT TRANSACTION statement tells SQL Server that you re finished with the second transaction that you started. However the ROLLBACK TRANSACTION then rolls back all the work since the first BEGIN TRANSACTION including the inner nested transaction. Although transaction names appear to offer increased readability for your code they interact poorly with nested transactions. In fact you can refer to a transaction by name only if it s the outermost transaction in a batch. Our recommendation is to avoid naming transactions if you plan to ever nest transactions. COMMIT TRANSACTION The syntax of COMMIT TRANSACTION is very similar to that of BEGIN TRANSACTION. There s also an alternative statement with the same purpose COMMIT TRANS ACTION transaction_name @name_variable COMMIT WORK When you issue a COMMIT TRANSACTION statement the most recent transaction you started is marked as ready to commit. When you commit the outermost in a series of nested transactions the changes are written back to the database. Of course if there s only one transaction open the changes are written immediately. It s your responsibility to make sure you ve made all the changes you want before issuing a COMMIT TRANSACTION statement. Once a transaction has been committed it can t be rolled back. Although you can use a name in the COMMIT TRANSACTION statement SQL Server makes no attempt to match this to a name in a BEGIN TRANSACTION statement. The name s purely for your convenience in making your code more readable. TRANSACTIONS 271 COMMIT with or without the .
