Manage transactions

May include but is not limited to: BEGIN TRANSACTION, COMMIT, ROLLBACK, SET TRANSACTION ISOLATION LEVEL

A transaction is a group of related tasks, which must succeed or fail as a unit. A transaction is referred as an ACID operation. ACID stands for Atomicity, Consistency, Isolation and Durability.

To start a transaction, simply write BEGIN TRANSACTION. You can optionally specify a transaction name for it. To end it successfully, use the COMMIT clause, to undo the changes, use the ROLLBACK clause, along with the optional name of the transaction. Anything between the BEGIN TRAN(SACTION) and the ending clause is considered to be part of it.

In SQL Server, we distinct some kinds of transactions. There are explicit transactions, which are explicitly marked with the BEGIN TRAN keyword. There are also implicit transactions, which can be used after calling the SET IMPLICIT_TRANSACTIONS ON statement. After this, any SQL statement will be executed as a transaction.

A nice feature to use is SET XACT_ABORT. By default, if a run-time statement error occurs in a SQL statement, only that statement is rolled back. If you specify SET XACT_ABORT, the whole batch will be rolled back instead of just the one causing the error.

The last thing is the question of transaction isolation levels. As transactions usually work with locks, you have the ability to define their behavior in doing so. The key here is the SET TRANSACTION ISOLATION LEVEL statement. It accepts five values, which are:

• READ UNCOMMITTED: this option lets you read rows that have been modified by another transaction, but not committed yet.
• READ COMMITED: specifies that transactions cannot read uncommitted rows from another transaction.
• REPEATABLE READ: specifies that statements cannot read uncommitted data, and that no other transaction is allowed to modify rows that the current one read.
• SNAPSHOT: lets other transactions read the committed data in the sate it was before the beginning of the current transactions, as if they were viewing a snapshot.
• SERIALIZABLE: cannot read uncommitted data, no other transactions can modify read data, cannot insert key if the key falls in the range of read data.