SQL Server Transaction Programming

Transaction Programming

A simplified view of transaction programming is as follows.

• Use DECLARE statement to set up all the local variables which will be used in the transaction statements.
• Use BEGIN TRANSACTION clause to begin transaction.
• After BEGIN TRANSACTION, write all the statements which will be executed as a unit of work for transaction.
• Use system global variables @@error and @@transient in BEGIN block.
• Store values of global variables in the declared variables.

Check the value of these system variables before executing COMMIT or ROLLBACK statement.

Some Tips about Transaction

When you make any change in data within a transaction then lock is acquired on the data. Locking data consumes additional resource. So, transaction consumes additional resources on SQL Server. You should never use transaction for simple single statement SQL Query. 

• Do not use transaction when the user is browsing the data (read operation).
• Use transaction in only that part of code where data is actually going to change.