Nov 4, 2009

Concurrency:

Concurrency is an ability that allows multiple users to access the same data at the same time.

4 Concurrency Problems:

Lost updates

Dirty reads

Non-repeatable reads

Phantom reads

Isolation Levels:

SQL Server 2005 uses locks to control how the data can be accessed and changed by multiple users at the same time without conflicting with each other.

5 Isolation Levels:

READ UNCOMMITTED

READ COMMITTED

REPEATABLE READ

SNAPSHOT

SERIALIZABLE

Syntax for setting isolation level:

SET TRANSACTION ISOLATION LEVEL

{READ UNCOMMITTED|READ COMMITTED|

REPEATABLE READ|SNAPSHOT|SERAILIZABLE}

Which concurrency problem is prevented?

Isolation level

Dirty reads

Lost updates

Non-repeatable reads

Phantom reads

Read Uncommitted

No

No

No

No

Read Committed

Prevents

No

No

No

Repeatable Read

Prevents

Prevents

Prevents

No

Snapshot

Prevents

Prevents

Prevents

Prevents

Serializable

Prevents

Prevents

Prevents

Prevents

SERIALIZABLE isolation level:

• SERIALIZABLE locks every resource a process uses; other processes can’t work on the same data until the first transaction is completed.

• More server overhead to manage the locks.

• Deadlocks can happen.

SNAPSHOT isolation level:

First introduced in SQL Server 2005

Uses row versioning feature that stores the original version of a row in TempDB when a row is modified.

The process works with the version in TempDB, not with the original data.

Eliminates locking the data and significantly reduces the likelihood of deadlocks.

If a SNAPSHOT transaction tries to change the data that has been changed by another process since the SNAPSHOT transaction began, the process will raise an error and roll back the transaction.

Avoid it by using UPDLOCK for SELECT statements that its results will be used in subsequent update.

Deadlocking:

Happens at REPEATABLE READ and SERIALIZABLE

A deadlock occurs when two or more tasks are blocking each other from accessing the same resources.

Deadlocks are automatically caught by SQL Server Database Engine. Once detected, the Database Engine chooses one of the transactions as the deadlock victim and lets the other transaction complete its process.

Default search interval is 5 seconds.

Deadlock Error:

(1 row(s) affected)

Msg 1205, Level 13, State 51, Line 7

Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Deadlock Tools:

1) Trace Flag 1204 and Trace Flag 1222

The flags can be set in the Startup Parameters for the instance of SQL Server in SQL Server Services

2) Deadlock Graph Event

SQL Server Profiler

Deadlock Graph:

Minimizing Deadlocks:

Because the transaction of the deadlock victim is terminated by SQL Server, applications should trap error message 1205 and resubmit the query again automatically.