devxlogo

Resolving Deadlocks in SQL Server 2000

Resolving Deadlocks in SQL Server 2000

Why Trust Us

eadlocking can be a difficult problem in a multi-user SQL Server application. Deadlocks are caused when transactions mutually block each other, and each is waiting for the other to finish. SQL Server will detect deadlocks involving locked database resources and cancel one of the queries, and roll back the transaction. In this article, you’ll learn how to use SQL Server Profiler and the trace flag 1204 to detect and analyze deadlocks. You’ll also learn some strategies to refactor the conditions leading to deadlocking.

Your application can detect a deadlock and resubmit its transaction, but a better approach is to resolve a deadlock by changing the conditions that lead to it in the first place. In this article, you’ll learn how SQL Server deadlocks arise, what types of deadlocks there are, and how you can resolve them.

Deadlocking is not a problem that is unique to SQL Server. Any database system that relies on locking to ensure that user transactions do not interfere with each other is subject to deadlock conditions. In order to understand and resolve SQL Server deadlocks, it’s important to understand the basic concepts underlying deadlocking in SQL Server.

Deadlocking Concepts
The key concept behind deadlocking is the transaction. To give your users a consistent view of the database, where either all changes in a transactional unit of work succeed or all fail, the database system must lock some resources while the work is being done. A SQL Server deadlock occurs when two or more processes have acquired locks on their respective resource, and they need to get an incompatible lock on the other’s resource in order to finish a transaction. The result is mutual blocking: each waits on each other to acquire some resource that the other process already has.

A SQL Server deadlock occurs when two or more processes have acquired locks on their respective resource, and they need to get an incompatible lock on the other’s resource in order to finish a transaction. The result is mutual blocking: each waits on the other to acquire some resource that the other process already has.

The result is a situation where neither process can finish. SQL Server’s lock manager will detect a deadlock cycle and end one of the transactions. Table 1 shows how, in general, a deadlock occurs.

Read the table from top to bottom, imagining time to progress from instance T1 through T7. By time T3, Transaction1 and Transaction2 have both been granted locks on some resource. At time T4, Transaction1 requests an incompatible lock on the resource already locked by Transaction2, and is blocked. At that point, Transaction1 goes into a wait state, waiting for the lock to be released.

At time T5, Transaction2 requests an incompatible lock on the resource that Transaction1 already has locked. At this point, Transaction2 also goes into a wait state, and each process is blocking the other. This is a deadlock cycle, and here is where SQL Server will detect the deadlock cycle and end one of the transactions.

Table 1. How a SQL Server resource deadlock occurs in general.

Time

Transaction1

Transaction2

T1

Begin Tran

Begin Tran

T2

GRANT

?

T3

?

GRANT

T4Blocked

Incompatible request

(WAIT on Tran2)

?

T5Blocked

?

Incompatible request

(WAIT on Tran1)

T6

Deadlock Victim

(blocking removed)

T7

?

Commit

Types of Waits
According to SQL Server Books Online, SQL Server threads can wait on

  • Locks
  • Parallel query resources
  • Other threads
  • Memory resource
  • Application events

Deadlocking can occur with locks, parallelism, threads, and application events. (Memory waits are resolved by query time-out.) The most frequent source of SQL Server deadlocking is resource locking where the resources are table or index objects.

Deadlocks Involving Locks
Lock-based deadlocks involve two or more threads, at least one transaction, and one or more resources. It’s useful to view deadlocks as occurring in two stages. The first is a grant stage, where each thread is granted a lock on its resource. They could be the same resource, but it’s much more common that they are different resources.

The second stage is a blocked request where each thread requests an incompatible lock on the other thread’s resource. Each thread waits on the other to release its locks before it can complete. SQL Server detects the deadlocked state and rolls back one of the transactions.

Deadlocking is more than blocking. Blocking occurs when one thread is waiting on another, and some brief blocking is normal.

Deadlocking is more than blocking. Blocking occurs when one thread is waiting on another, and some brief blocking is normal. Blocking is expected in any database system that uses locking to maintain transaction isolation. Only blocks with long durations should be considered a problem. In an active system, short periods of blocking may be happening quite often. Lock-based deadlocking is a special type of blocking where two or more threads mutually block each other, and that’s what you need to avoid.

How SQL Server handles a Deadlock
In SQL Server 2000, the Lock Monitor thread detects the deadlock. It uses a periodic detection system, inspecting processes about every 5 seconds to determine if there are any deadlock cycles. When it finds one, it automatically chooses one thread as the deadlock victim. It then rolls back the victim thread’s transaction, cancels its query, and returns error 1205 to its client.

The Lock Monitor generally chooses the least expensive transaction to roll back. You can override this somewhat using SET DEADLOCK_PRIORITY to LOW for a session. But whenever both threads have the same DEADLOCK_PRIORITY setting, the Lock Monitor will have to choose one of them as the victim.

The message delivered by error 1205 is mysterious or entertaining, depending on your point of view:

   Server: Msg 1205, Level 13, State 50, Line 1   Transaction (Process ID 54) was deadlocked    on lock resources with another process and    has been chosen as the deadlock victim.    Rerun the transaction.

Error 1205 is not fatal to the connection; your connection is not terminated. SQL Server aborts the batch, rolls back the transaction, and cancels the query. As a result, error 1205 cannot be detected from within Transact-SQL by @@ERROR within the batch or from calling a stored procedure, because the entire calling batch is cancelled. It has a severity level of 13, correctable by the user, so the client can resubmit the query.

Unfortunately, error 1205 does not tell you the identity of the other participating spid or spids that your process deadlocked with. It also does not reveal statements at the grant or blocked request stage, so you are not sure what commands set up the deadlock conditions in the first place.

SQL Server Deadlocking Factors
You can view deadlocks as containing four factors:

  • Lock modes and incompatibility
  • Transaction timing
  • The order of lock requests
  • Isolation level of the transactions

Lock Modes and Incompatibility
SQL Server has a number of lock modes. The most common are listed in Table 2. The core set of locks on resources are the shared and exclusive locks. They can be granted on rows, pages, and tables. These are the types of locks you’ll see most often in deadlocks.

The next set, the intent locks, are higher-level locks granted when lower-level locks are also granted. They also can occur in deadlocks, especially the hard ones. The last set, the schema locks and bulk update, are rarely involved in deadlocks.

Table 2. The most common types of resource locks.

Lock Mode

Abbreviation

Description

Shared

S

Used for reading (read lock)

Update

U

Used to evaluate prior to writing (may become exclusive)

Exclusive

X

Used for writing (insert, update, delete)

Intent Shared

IS

Have or intend to request shared lock(s) at a finer level

Intent Update

IU

Have or intend to request update lock(s) at a finer level

Intent Exclusive

IX

Have or intend to request exclusive lock(s) at a finer level

Shared Intent Update

SIU

Have shared lock with intent to acquire update lock at a finer level

Shared Intent Exclusive

SIX

Have shared lock with intent to acquire exclusive lock at a finer level

Update Intent Exclusive

UIX

Have update lock with intent to acquire exclusive lock at a finer level

Schema-Stability

Sch-S

Used when compiling queries

Schema Modification

Sch-M

Used for DDL operations (ALTER or DROP) on a table schema

Bulk Update

BU

Used with bulk copy into a table with either TABLOCK hint or table lock option is set

What’s important is that not all of these types of locks are compatible; in fact the majority aren’t, as illustrated in Table 3. It can be difficult to find and test lock compatibility. Table 3 is taken from Books Online and enhanced, and I’ve tested all the combinations that appear there. However, there are other types of locks that may also be incompatible. You can see at a glance, though, that more combinations are incompatible than are compatible.

Table 3. Most resources lock combinations are incompatible.

Granted:

Requested

S

X

U

IS

IX

SIX

Sch-S

Sch-M

BU

S

Yes

No

Yes

Yes

No

No

Yes

No

No

X

No

No

No

No

No

No

Yes

No

No

U

Yes

No

No

Yes

No

No

Yes

No

No

IS

Yes

No

Yes

Yes

Yes

Yes

Yes

No

No

IX

No

No

No

Yes

Yes

No

Yes

No

No

SIX

No

No

No

Yes

No

No

Yes

No

No

Sch-S

Yes

Yes

Yes

Yes

Yes

Yes

Yes

No

Yes

Sch-M

No

No

No

No

No

No

No

No

No

BU

No

No

No

No

No

No

Yes

No

Yes

Transaction Timing
Lock incompatibility is not the only factor in producing deadlocks. Transactions must overlap in time for blocking and deadlocking to occur. Usually that means that the database must be active enough for short transactions to coincide closely in time, or that transactions have sufficiently long duration to make blocking and deadlocking likely. As a result, sometimes deadlocks require a large number of concurrent transactions to surface. When testing your application, you might find that a load of five or ten simultaneous users is not enough to reveal deadlocking, but that a load of twenty users is.

The Order of Lock Requests
The transactions must coincide in time, but they must also grant and request locks in a certain order. That is, the initial locks must be granted before incompatible locks are requested. Sometimes changing the order of lock requests may release locks early or remove incompatible locks.

Isolation Level of the Transactions
Another key component of deadlocks is the isolation level of the transactions. SQL Server has four isolation levels, and the default is READ COMMITTED. That means that your transaction will only read committed data, not uncommitted data. At this level, your transaction must get a shared lock in order to read a resource, and if it is already locked with an incompatible mode, your transaction must wait. If you lower the isolation level to READ UNCOMMITTED, so that you allow the transaction to read uncommitted data, the requirement for shared locks is lifted.

The core set of locks on resources are the shared and exclusive locks. They can be granted on rows, pages, and tables. These are the types of locks you’ll see most often in deadlocks.

In the READ COMMITTED and READ UNCOMMITTED isolation levels, shared locks are released as soon as a read query is completed. In the higher isolation levels of REPEATABLE READ and SERIALIZABLE, your transaction will hold the shared locks until the end of the transaction. As a result, the higher isolation levels make deadlocks more likely.

Table 4 summarizes how shared locks behave with various isolation levels. Isolation level is often a critical factor in deadlocking, but not always: deadlocks involving only exclusive locks are independent of the isolation levels, because the isolation levels affect only how shared locks are treated.

Table 4. Isolation level affects how shared locks are handled in a transaction.

Lock Mode

Read Un-committed

Read Committed

Repeatable Read

Serializable

Shared

Held until data read and processed

Held until data read and processed

Held until end of transaction

Held until end of transaction

Update

Held until end of transaction unless promoted to exclusive or released

Held until end of transaction unless promoted to exclusive or released

Held until end of transaction unless promoted to exclusive

Held until end of transaction unless promoted to exclusive

Exclusive

Held until end of transaction

Held until end of transaction

Held until end of transaction

Held until end of transaction

You might think that deadlocking involves only exclusive locks, but some deadlocks involving shared locks can occur with the READ COMMITTED isolation level, whereas others involving shared locks require isolation levels higher than READ COMMITTED.

Types of Lock Deadlocks
Deadlocks can involve more than two threads, often quite a number of threads. When many threads are involved in a deadlock, SQL Server will choose one of the threads as a victim, return to the deadlock, choose another thread as a victim, and then eventually narrow the entire deadlock down to a core of two threads.

You can view deadlocks as falling into two categories, depending on whether the transactions initially lock the same or different resources. When the transactions each lock a single resource, let’s call that a “single-resource” deadlock, and when there is more than one resource, we’ll call that a “multiple-resource” deadlock.

Single-Resource Deadlocks
The single-resource deadlock is sometimes called ‘conversion’ deadlock, because it occurs when each transaction simultaneously attempts to convert its compatible lock to an incompatible lock.

Mixed X-S deadlocks can be much more difficult to diagnose. They can take place with the default isolation level of READ COMMITTED or higher and involve SELECT statements.

The initial grant stage requires shared locks, because both threads must obtain a lock on a single resource. The single-resource deadlock also requires an isolation level higher than READ COMMITTED so that the share locks will continue to be held through the end of the transaction. The blocked request stage requires exclusive or update locks, locks that are incompatible with shared locks.

Table 5 shows how a single-resource deadlock can occur. The HOLDLOCK hint causes each SELECT to enter into the SERIALIZABLE isolation level, so that the shared locks are held for the duration of the transaction. This is a comparatively rare type of deadlock because the SERIALIZABLE isolation level and HOLDLOCK hint are rarely used in practice.

Table 5. A single-resource deadlock transaction history.

Time

Tran 1

Tran 2

T1

Begin Tran

Begin Tran

T2

GRANT

Select *

From Authors With (HOLDLOCK)

Where au_id = ‘172-32-1176’

?

T3

GRANT

?

Select *

From Authors With (HOLDLOCK)

Where au_id = ‘172-32-1176’

T4

Blocked

Update Authors

Set contract = 0

Where au_id = ‘172-32-1176’

?

T5

Blocked

?

Update Authors

Set contract = 1

Where au_id = ‘172-32-1176’

T6

Deadlock Victim

(blocking removed)

T7

?

Commit

Multiple-resource Deadlocks
The multiple-resource deadlock is sometimes called a ‘cycle’ deadlock, but that is a misnomer. All deadlocks involve a cycle of some kind. This type of deadlock occurs when each thread requests an incompatible lock on the other’s resource.

The grant stage normally requires exclusive locks, because shared locks will not be held to the end of the transaction under the default isolation level of READ COMMITTED. There are many variations of the multiple-resource deadlock, but they can generally be classified into two sets.

Some multiple-resource deadlocks involve exclusive locks only; let’s call them X-only. In this group, threads make just exclusive lock requests. Others involve mixed exclusive and shared (X-S). First, threads are granted exclusive locks. Then the threads request shared locks and then they are blocked. The mixed X-S deadlocks can be more subtle and challenging.

Table 6 shows how an X-only deadlock can occur. Note that the X-only type of deadlock works at any isolation level, because exclusive locks are always held to the end of a transaction. This is a fairly easy deadlock to detect and prevent. All you have to do is enforce the rule that transactions in stored procedures or other code have to access common sets of tables in the same order.

Table 6. How a deadlock with exclusive-only locks can occur.

Time

Tran 1

Tran 2

T1

Begin Tran

Begin Tran

T2

GRANT

Update Authors

Set contract = 0

Where au_id = ‘172-32-1176’

?

T3

GRANT

?

Update Titles

Set ytd_sales = 0

Where title_id = ‘BU1032’

T4

Blocked

Update Titles

Set ytd_sales = 0

Where title_id = ‘BU1032’

?

T5

Blocked

?

Update Authors

Set contract = 0

Where au_id = ‘172-32-1176’

T6

Deadlock Victim

(blocking removed)

T7

?

Commit

The X-S, exclusive with shared, type of deadlock is a more complex and subtle type of deadlock. There are many variations: X-X:S-S, X-X-X:S-S-S, X-IS:IS-X, S-IX:IX-S, X-X:U-U, etc. All of these can occur with the READ COMMITTED isolation level, so they are actually the type that you are more likely to see in subtle and complex deadlocks.

Table 7 shows one example of an X-S type of deadlock. Notice in this case that each transaction attempts to read another row in the same table. The rows are the resources that are locked by the transactions.

Table 7. A simple X-S deadlock.

Time

Tran 1

Tran 2

T1

Begin Tran

Begin Tran

T2

GRANT

Update Authors

Set contract = 0

Where au_id = ‘172-32-1176’

?

T3

GRANT

?

Update Titles

Set ytd_sales = 0

Where title_id = ‘BU1032’

T4

Blocked

Select *

From Titles

Where title_id = ‘BU1032’

?

T5

Blocked

?

Select *

From Authors

Where au_id = ‘172-32-1176’

T6

Deadlock Victim

(blocking removed)

T7

?

Commit

It might be rather rare that a transaction would want to read another transaction’s row just after an update, so Table 8 shows a much more subtle type of X-S deadlock. In this example, each transaction inserts a row into the same table, and then reads the table for more information, perhaps to do another insert. This is a much more common pattern of deadlock, something that transactions in stored procedures are legitimately required to do.

Table 8. A more subtle X-S deadlock.

Time

Tran 1

Tran 2

T1

Begin Tran

Begin Tran

T2

GRANT

Insert Authors Values

(‘111-11-1111’, ‘test1’, ”, ”, ”, ”, ”, ‘11111’, 0)

?

T3

GRANT

?

Insert Authors Values

(‘111-11-1112’, ‘test2’, ”, ”, ”, ”, ”, ‘11111’, 0)

T4

Blocked

Select * From Authors

Where contract = 0

?

T5

Blocked

?

Select * From Authors

Where contract = 0

T6

Deadlock Victim

(blocking removed)

T7

?

Commit

Mixed X-S deadlocks can be much more difficult to diagnose. They can take place with the default isolation level of READ COMMITTED or higher and involve SELECT statements.

Gathering Deadlock Information
There are three basic strategies for gathering deadlock information. You can use the Perfmon Deadlocks/sec counter, inspect the output of the trace flag 1204, and use SQL Trace/Profiler’s deadlock events.

Perfmon’s Deadlocks/sec Counter
The Perfmon Deadlocks/sec counter is useful in measuring the frequency of deadlocking. It’s also useful in multi-user testing to determine the minimum number of users required to reproduce deadlocks. However, it does not show individual deadlocks, it only shows that some deadlocking is occurring.

The 1204 Trace Flag
The output of the trace flag 1204 can be invaluable in diagnosing a deadlock. You set it in combination with the 3605 flag:

   DBCC TRACEON(3605, 1204)

Or

   DBCC TRACEON(3605)   DBCC TRACEON(1204)

The output of the flag will be sent to the SQL Server error log whenever SQL Server encounters a deadlock. You may find it useful to make a SQL Agent job execute these statements at startup so that the SQL Server error log will always report any deadlocks.

Interpreting the output of the trace flag 1204 can be challenging. Figure 1 shows a sample output and what some of the entries mean.

devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist

Charlie Frank

Charlie has over a decade of experience in website administration and technology management. As the site admin, he oversees all technical aspects of running a high-traffic online platform, ensuring optimal performance, security, and user experience.
View Author
Show More
Linkedin Twitter

©2024 Copyright DevX - All Rights Reserved. Registration or use of this site constitutes acceptance of our Terms of Service and Privacy Policy.

Sitemap — Privacy Policy