A transaction is a block of commands executed as a single unit of work by SQL Server. Transactions can be explicitly delimited using 'BEGIN TRAN' and either 'COMMIT TRAN' or 'ROLLBACK TRAN'. Without these delimiters, SQL Server treats a block of commands as implicitly delimited. Transactions can be nested.

Locks

SQL Server uses data locks to ensure data consistency for each transaction. Data is locked at the beginning of a transaction and the lock is released when the transaction terminates - either with a COMMIT TRAN or ROLLBACK TRAN command. There are three types of locks used by SQL Server:

Shared

Shared locks are allocated to SELECT statements. Multiple shared locks can be granted on the same resource. A shared lock on a resource, prevents an exclusive lock being granted on the same resource, thus preventing any changes to the data occuring for the duration of the transaction

Exclusive

Exclusive locks are granted for insert or delete operations. Exclusive locks prevent other connections from accessing a resource. This prevents other connections from changing the data being operated on, and ensuring that other connections do not receive data that is in the process of being changed.

Update

Update locks combine shared locking with exclusive locking. During an update operation, data is first retrieved under a shared lock. Before making any updates, the connection changes the lock type to exclusive.

Lock Level

Locks can be obtained at the row, page or table level. SQL Server uses statistics from the Optimizer to determine the appropriate lock level required for a transaction, and ensure that unneccessary resources are not locked for each transaction. Lock acquisition uses the 'Two Per Cent' rule. If a transaction affects less than 2% of the rows in a table, then a row-level lock is obtained. If the transaction affects more than 2% of the rows but less than 2% of the pages in a table, then a page-level lock is acquired. If more than 2% of the pages in a table are affected by the transaction, then a table-level lock is acquired. If a transaction subsequently requires more resources than was originally calculated, the lock is escalated to the next highest level. The 2% rule is designed to maximise concurrency by keep locks at the lowest level of granularity for the shortest time possible.

A deadlock occurs between two transactions which both need a lock on resources which are currently blocked by locks held by the other. For example if two connections each hold shared locks on seperate resources, but need to create an exclusive lock on the resource held by the other.

Isolation

The use of locks ensures that transactions receive data that is not in the process of being modified. Each transaction is thus isolated from the effects of other transactions. However, locking will impact concurrency, forcing one transaction to wait for other transactions to release their locks on data. In some circumstances, it may be desirable to access data currently locked by another process. Transactions can be assigned isolation levels to specify the degree of isolation they require. SQL Server provides 5 levels of isolation:

Read Uncommitted

Allows data to be read that is currently being changed by another process. Reading uncommitted data is described as a dirty read: the data being modified could be rolled-back after it has been read, and will therefore be incorrect

Read Committed

This is the default isolation level for a transaction, and prevents a connection from reading data that is in the process of being modified. However, within the same transaction, if the same query is executed twice, it is possible for data to have been modified between reads, leading to non-repeatable reads.

Repeatable Read

Does not allow data that is being read to change for the duration of the transaction. However, repeatable reads do not prevent new data being inserted to the table and can therefore result in phantom reads where the same query in a single transaction returns two different results

Serialisable

Ensures that data being read from the whole table cannot change for the duration of the transaction. Updates are blocked for the duration of the transaction guaranteeing repeatable reads. Inserts are blocked for the duration of the transaction preventing phantom reads.

Snapshot

Uses row version to read rows but does not acquire any locks on data. If data is being modified during a read, snapshot isolation returns the data prior to the modification, thus protecting transactions from unrepeatable and phantom reads