Transaction Management in SQL Server

A transaction can be defined as a sequence of operations performed together as a single logical unit of work. A single unit of work must possess the four ACID properties i.e., Atomicity, Consistency, Isolation and Durability. Atomicity means, all the data modification are performed or none of them are performed. Consistency means, all data is in consistent state after a transaction is completed successfully. Isolation means, any data modification made by a transaction must be isolated form the modifications made by other concurrent transactions. Durability means, any change in data by a completed transaction remains permanently in effect in the system. So ultimately, transaction control is implemented for preventing inconsistency in data.

SQL Server features for Transaction Control:
To fulfil the requirements of the ACID properties, SQL Server provides the following features:

Transaction Management
Locking, and
Logging.

Transaction Management:

You can create an explicit transaction in SQL Server by specifying the start and end of the transactions explicitly using BEGIN TRAN[SACTION] and COMMIT TRAN[SACTION] command.

Syntax:

BEGIN TRANSACTION: This statement marts the start of an explicit transaction.
BEGIN TRAN[SACTION] [transaction_name | @tran_name_variable]
COMMIT TRANSACTION: This statement marts the end of an explicit transaction.
COMMIT TRAN[SACTION] [transaction_name | @tran_name_variable]
The autocommit mode is the default transaction management mode of SQL Server.
ROLLBACK TRANSACTION: This statement roll back an explicit or implicit transaction to the beginning of the transaction or to a save point within the transaction.
ROLLBACK TRAN[SACTION] [transaction_name | @tran_name_variable | savepoint_name | @savepoint_variable]
SAVE TRANSACTION: This statement sets a savepoint within a transaction. A save point divides a transaction into logical units so that the transaction can return to the save point, if a part of the transaction is conditionally cancelled.
SAVE TRAN[SACTION] { savepoint_name | @savepoint_variable}

Consider the following case, the code is self explanatory:

 BEGIN TRANSACTION
 update Employee
       set cCurrentPosition = '0015'
        where cEmployeeCode = '000002'
 update Position
        set iCurrentStrength = iCurrentStrength + 1
         where cPositionCode = '0033'

 SAVE TRANSACTION trn1
 
 update Requisition
 set siNoOfVacancy = siNoOfVacancy - 10
         where cRequisitionCode = '0000004'
 update Position
        set iCurrentStrength = iCurrentStrength + 10
         where cPositionCode = '0033'

 if (select  iBudgetedStrength - iCurrentStrength from Position where cPositionCode = '0033') <0
  begin
   print ' Current Strength can't exceed Budgeted Strength. So first transaction is committed. But the 
    Second transaction is committed'
   ROLLBACK TRANSACTION trn1
  end
 else
  begin
   print ' Both the transactions are committed'
   COMMIT TRANSACTION 
  end

Locking in SQL Server

Locking is automatically implemented in SQL Server. It ensures transactional integrity and database consistency. Because of Locking the view of transaction is possible.
SQL Server provides both optimistic and pessimistic concurrency controls. Optimistic concurrency control works on the assumption that resource conflicts between multiple users are unlikely but not impossible. It allows transactions to execute without locking any rosources. So resources are only checked when a transaction has to commit. Whereas Pessimistic concurrency control locks resources for the duration of a transaction.

SQL Server can lock the following resouces:
- RID
- Key
- Page
- Extent
- Table
- Database

It has the following Lock modes:

Shared Locks : Concurrent Read operation is allowed in this mode.
Update Locks : Prevent a common form of deadlock from occuring.
Exclusive Locks : Exclusively restrict concurrent transactions from accessing a resource.
Intent Locks : Show an intenton to acquire a shared of exclusive lock.
Schema Locks : Schema Modification locks are considered during DDL operations.

Deadlock Prevention in SQL Server:

A deadlock is a situation in which two transactions have locks on separate objects and each user is waiting for a lock on the other's object.
SQL Server can set deadlock priority. It scans for sessions that are waiting for a lock request. It provides the SET DEADLOCK_PRIORITY command to customize deadlocking.

SET DEADLOCK_PRIORITY { low | normal | @deadlock_variable}
SQL Server also has the SETLOCK_TIMEOUT command to set the maximum time that a statement waits on a blocked resource. By default, timeout period is not enforced.

SETLOCK_TIMEOUT [timeout_period]

Implementation Recommendation:

When you develop a transactional application in .NET using the classes provided by System.Transactions, you do not need to worry about what kind of transactions you need, or the transaction manager involved. The System.Transactions infrastructure automatically manages these for you. If an application exists that uses COM+ and/or System.EnterpriseServices the System.Transactions infrastructure works with this as well. As such, the recommendation for any application that needs to do transaction management is to use the System.Transactions namespace. Although the System.Transactions namespace supports both explicit and implicit transactions it is generally recommended that applications use implicit transactions.

Search the site