sqlindian – On Locks and Deadlocks

Random thoughts on performance and concurrency

SQL Server deadlocks and Live Locks–Common Patterns

leave a comment »

I get to play with a lot of deadlocks these days, part by chance and part by choice. So I thought it is a good idea to start a blog series about handling different types deadlocks and live locks (yes live locks!)

I will try to establish some context about general deadlock properties and then introduce you to some common and some not so common deadlock patterns.

Introduction

Troubleshooting

Cyclic deadlock

Conversion deadlock

Multi-Victim Deadlocks

Deadlock due to different access Paths

Deadlock due to different access Order

Deadlock due to different lock granularity

Deadlock due to Foreign Key Constraints

Deadlock due to Parallelism

Deadlocks due to Partition Level Lock Escalation

DEADLOCK on SELECT due to UNORDERED PREFETCH

Deadlocks involving Select Into

Deadlock due to Savepoint Rollback behavior

Deadlocks due to Hash Collision

Deadlocks Involving Lock Partitions

Deadlocks due to Lock Partitioning

Deadlock due to Implicit Conversion

 

Note: The scripts mentioned in the articles are tested only in SQL server 2008 R2 unless mentioned otherwise.

About these ads

Written by sqlindian

July 6, 2012 at 1:24 AM

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: