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