Labels

Thursday, August 7, 2008

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

No comments:

Post a Comment