Microsoft® SQL Server® 2012 Bible

1072

Part VIII: Performance Tuning and Optimization

Internally, SQL Server uses locks for isolation (locks are still used in snapshot-based isola-

tion levels; the difference is that they typically do not block other operations)), and the

transaction isolation levels determines the duration of the share lock or exclusive lock for

the transaction, as listed in Table 47-4.

TABLE 47- 4 Isolation Levels and Lock Duration

Isolation Level Share-Lock Duration Exclusive-Lock Duration

Read

Uncommitted

None Held only long enough to prevent physical

corruption; otherwise, exclusive locks are nei-

ther applied nor honored.

Read Committed Held while the transac-

tion is reading the data

Held until transaction commit.

Repeatable Read Held until the transac-

tion is committed

Held until transaction commit.

Serializable Held until

transaction commit

Held until transaction commit. The exclu-

sive lock also uses a keylock (also called a

range lock) to prevent inserts.

Setting the Transaction Isolation Level

The transaction isolation level can be set at the connection level using the SET command.

Setting the transaction isolation level affects all statements for the duration of the con-

nection, or until the transaction isolation level is changed again. (You can’t change the

isolation level once you are in a transaction.)

SET TRANSACTION ISOLATION LEVEL

READ COMMITTED;

To view the current connection transaction isolation level, use sys.dm_exec_sessions:

SELECT TIL.Description

FROM sys.dm_exec_sessions dmv

JOIN (VALUES(1, 'Read Uncommitted'),

(2, 'Read Committed'),

(3, 'Repeatable Read'),

(4, 'Serializable'))

AS TIL(ID, Description)

ON dmv.transaction_isolation_level = TIL.ID

WHERE session_id = @@spid;

Result:

READ COMMITTED

c47.indd 1072c47.indd 1072 7/31/2012 10:23:31 AM7/31/2012 10:23:31 AM

http://www.it-ebooks.info