SQL : Locks & Transaction Isolation Levels

SQL LOCKS

UPDLOCK

• associated with DML statements like Update
• Shared lock and needs to be converted to an Exclusive lock
• Occurs at row level.

HOLDLOCK

• Table level it prevents the table from being updated by any other DML transactions like an insert or an update
• HOLDLOCKS are preferable to use in situations where we need only COMMITTED READS

NOLOCK

• Opposite of HOLDLOCKS and when they are issued at a table level
• Allows the user to access/read the data from a table whilst some update/ insert operations are running at the back.
• May produce UNCOMMITTED READS and are preferred to use at the Production Environments so that the real time update is not hindered.

• How do you see the current locks in a Server? - use the Stored proc SP_LOCK
• How will you kill the locks? - KILL Session_ID

TRANSACTION ISOLATION LEVELS

READ UNCOMMITTED

• Specifies that statements can read rows that have been modified by other transactions but not yet committed

READ COMMITTED

• Specifies that statements cannot read data that has been modified but not committed by other transactions
• DEFAULT option on SQL Server

REPEATABLE READ

• Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.

SNAPSHOT

• Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction.

SERIALIZABLE

• Statements cannot read data that has been modified but not yet committed by other transactions.
• No other transactions can modify data that has been read by the current transaction until the current transaction completes
• Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.