SQL Server Transactions

Transactions can be really important when we are working with databases. For example let’s say you need to execute two or more SQL commands sequentially and if any statement fails, all other statements should not be executed. To achieve this, we can use Transactions.

Mainly transactions has these four properties which are abbreviated as ACID.

1. Atomic
   • Atomic means that all the work in the transaction is treated as a single unit. That is either all statements in the unit must execute, or no statement in the unit must execute.
2. Consistent
   • Consistent means that a completed or roll backed transaction leaves the database in a consistent internal state. A transaction will convert the database from a known starting state to a known ending state. If the transaction commits, the database will be at the known ending state. If the transaction fails, the database will be at the known starting state.
3. Isolated
   • Isolations means that the transaction sees the database in a consistent state. If two transactions try to update the same table, one will go first and then the other will follow. Transactions are committed independently from each other and they are transparent to each other.
4. Durability
   • Durability means that the results of the transaction are permanently stored in the system and ensures that the result or effect of a committed transaction persists in case of a system failure.

There are couple of transaction types.

1. Autocommit Transactions
   • Autocommit mode is the default transaction management mode of the SQL Server Database Engine. Every Transact-SQL statement is committed or rolled back when it completes. If a statement completes successfully, it is committed; if it encounters any error, it is rolled back.
   • But here there is something to note. That is sometimes Database Engine has rolled back an entire batch instead of just one SQL statement. This happens if the error encountered is a compile error, not a run-time error. A compile error prevents the Database Engine from building an execution plan, so nothing in the batch is executed. Let’s the following examples.
   • In this example, there is a compile time error in the third line. So the error prevented anything in the batch from being executed.

     INSERT INTO TestTable VALUES (1, 'aaa');

     
     

     INSERT INTO TestTable VALUES (2, 'bbb');

     
     

     INSERT INTO TestTable VALUSE (3, 'ccc');  -- Syntax error. 

     
     
     

     SELECT * FROM TestTable ;  -- Returns no rows.

   • In this example, there is a run time duplicate primary key error in he third line. The first two INSERT statements are successful and committed, so they remain after the run-time error.
     INSERT INTO TestTable VALUES (1, 'aaa');
     

     
     

     INSERT INTO TestTable VALUES (2, 'bbb');

     
     

     INSERT INTO TestTable VALUES (1, 'ccc');  -- Duplicate key error.

     
     
     

     SELECT * FROM TestTable -- Returns rows 1 and 2.

2. Explicit Transactions
   • An explicit transaction is one in which you explicitly define both the start and end of the transaction. DB-Library applications and Transact-SQL scripts use the BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION, or ROLLBACK WORK Transact-SQL statements to define explicit transactions.
     • BEGIN TRANSACTION
       Marks the starting point of an explicit transaction for a connection.
     • COMMIT TRANSACTION or COMMIT WORK
       Used to end a transaction successfully if no errors were encountered. All data modifications made in the transaction become a permanent part of the database. Resources held by the transaction are freed.
     • ROLLBACK TRANSACTION or ROLLBACK WORK
       Used to erase a transaction in which errors are encountered. All data modified by the transaction is returned to the starting state which it was in at the start of the transaction. Resources held by the transaction are freed.
3. Implicit Transactions
   • When a connection is operating in implicit transaction mode, the instance of the SQL Server Database Engine automatically starts a new transaction after the current transaction is committed or rolled back. If there is an INSERT statement running as an Implicit Transaction, then a separate transaction is created for that. If it was an explicit transaction, several INSERTs would be wrapped together in one transaction. Also for an Implicit Transaction, SQL Server needs to write the transaction log to disk every time . The session running the Implicit Transaction will remain open until a COMMIT/ROLLBACK transaction command is issued.
4. Distributed Transactions
   • At times you might have to execute transactions that span more than one server. These transactions can only be executed if all servers involved have MS Distributed Transaction Coordinator (MS DTC) installed and running. Distributed transactions are also very difficult to debug, so use them sparingly.

Hope this helps.

Happy Coding.

Regards,
Jaliya