02 - Transaction Level

Hi,

Here we’ll cover below:

- Concepts of transactions and locking.

- Types of Locks & Lock compatibility

- Locking issues - Blocking (Long-term locking), and Deadlocking

TRANSACTION ISOLATION LEVEL

- Transaction Isolation level decides how is one process isolated from other process.

- Using transaction levels you can implement locking in SQL SERVER.

- There are four transaction levels in SQL SERVER.

READ COMMITTED (Default)	- The shared lock is held for the duration of the transaction, meaning that no other transactions can change the data at the same time. - Other transactions can insert and modify data in the same table, however, as long as it is not locked by the first transaction.
READ UNCOMMITTED	- No shared locks and no exclusive locks are honored. - This is the least restrictive isolation level resulting in the best concurrency but the least data integrity.
REPEATABLE READ	- This setting disallows dirty and non-repeatable reads. - However, even though the locks are held on read data, new rows can still be inserted in the table, and will subsequently be read by the transaction.
SERIALIZABLE	- This is the most restrictive setting holding shared locks on the range of data. - This setting does not allow the insertion of new rows in the range that is locked; therefore, no phantoms are allowed.

- You may only have one level set at a time.

- Syntax

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

- Example

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE GO BEGIN TRANSACTION Select Count(*) From Mytable INSERT INTO MYTABLE VALUES (……)

- This example begins a transaction that makes a Select. - Since the isolation level is set to ‘Serializazble’ and we did not commited the transaction, range locks will be held on the table's rows. - Now, If you attempt to insert rows within the range of the same table, you will wait indefinitely. - The little Query Analyzer globe will spin until the original transaction commits or rolls back

Thanks & Regards,

Arun Manglick || Senior Tech Lead