Are Deadlocks inevitable ? - Microsoft SQL Server

John Rivers

There are many references to deadlock handlers that retry the
transaction automatically.

But IMO a deadlock is the result of a design flaw that should be
fixed.

My applications treat deadlocks like any other runtime error - they
are logged and fixed.

There seems to be a an opinion that deadlocks are inevitable !

This is not true - any scenario resulting in a deadlock can be cured
by adjusting the application.

I have talked with database consultants working for banks that think
deadlocks are an acceptable every day occurrence.

Now that is scary ...

Jul 10 '08 #1

Subscribe Reply

4215

Serge Rielau

John Rivers wrote:

There are many references to deadlock handlers that retry the
transaction automatically.

But IMO a deadlock is the result of a design flaw that should be
fixed.

My applications treat deadlocks like any other runtime error - they
are logged and fixed.

There seems to be a an opinion that deadlocks are inevitable !

This is not true - any scenario resulting in a deadlock can be cured
by adjusting the application.

I wouldn't go as far as claiming "any".

I have talked with database consultants working for banks that think
deadlocks are an acceptable every day occurrence.

If you have a complex transaction with many concurrent users I think I
agree that deadlocks are inevitable.
You cannot realistically sort data access/change in a way to always
guarantee lock waits over deadlocks at a reasonable expense.
So fault toleration is the more sensible way.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Jul 10 '08 #2

Serge Rielau

Serge Rielau wrote:

John Rivers wrote:
>There are many references to deadlock handlers that retry the
transaction automatically.

But IMO a deadlock is the result of a design flaw that should be
fixed.

My applications treat deadlocks like any other runtime error - they
are logged and fixed.

There seems to be a an opinion that deadlocks are inevitable !

This is not true - any scenario resulting in a deadlock can be cured
by adjusting the application.
I wouldn't go as far as claiming "any".

>I have talked with database consultants working for banks that think
deadlocks are an acceptable every day occurrence.

If you have a complex transaction with many concurrent users I think I

I meant complex _application_ ...

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Jul 10 '08 #3

John Rivers

An interesting idea:

to swap a deadlock-proof design for a higher performing design that
suffers deadlocks

but by accepting deadlocks you risk one client being selected as the
deadlock victim repeatedly for a long period of time ... potentially
forever!

Jul 10 '08 #4

Erland Sommarskog

John Rivers (fi*****@btinte rnet.com) writes:

but by accepting deadlocks you risk one client being selected as the
deadlock victim repeatedly for a long period of time ... potentially
forever!

If you have that high deadlock frequency, you better do something about it.

It is possible that every deadlock that occurs in an application could be
address. But what would the cost be? And what would the risk be that you
introduce bugs or performance bottlenecks.

If you have a bunch of background processes that deadlocks with each
other, or at least willing to become the deadlock victim, then some 20-30
deadlocks per day is not a big issue. It becomes an issue, if users get
this message slapped in the face 20-30 times a day, or if you have deadlocks
every minute. Then again, if you have deadlocks every minute, the deadlocks
are likely to be a symptom of a general performance problem.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Jul 10 '08 #5

Similar topics

19121

Deadlocks and use of nolock

by: AKS | last post by:

I am getting lot of deadlocks in my application. As it is very complex ti avoid deadlocks at this stage of application we have done few steps to lessen the impact. We have added retries after deadlock is capturted. We have added select * from TABLE with (nolock) wherever possible. But interestingly second step is not working. I have few simple select statements where i am using nolock criteria still I am getting deadlock victim error....

Microsoft SQL Server

8296

deadlocks in sql server

by: Hasan | last post by:

Hi I'm having a problem with deadlocks in a table in SQL server when trying to update it through Biztalk 2004. There is no problem when I use the same Biztalk solution to update a similar dummy table, but when I try updating the original table in the production database, some transactions are updated successfully whereas others become the victim of the deadlock (Transaction (Process ID 185) was deadlocked on lock resources with another...

Microsoft SQL Server

2221

Lost deadlocks

by: Matt White | last post by:

We've found deadlocks in the trace file that were not captured by our Powerbuilder application. Some deadlocks are trapped or, at least, reported to the user as a db error, and others are completely silent. We've also seen evidence of strange data that would be explained by unprocessed deadlocks, although we've not yet proven that the unreported deadlocks are killing updates to the db. Putting a raiserror into various parts of the same...

Microsoft SQL Server

5641

deadlocks involving parallelism

by: T Dubya | last post by:

We're experiencing a large number of deadlocks since we began running SQL Server 2000 Enterprise Edition SP3 on a Dell 6650 with hyper threading intel processors. We don't have the same problem on Dell 6650's w/o the hyper threading. If I turn off the parallel query processing option the deadlocks stop. I've tried all of the suggestions from the Microsoft Knowledge Base under the following link - ...

Microsoft SQL Server

2206

deadlocks

by: Marcus | last post by:

Hello all, I am trying to figure out when it is appropriate to use shared and exclusive locks with InnoDB. Using shared locks (lock in share mode), I can easily create a scenario with 2 clients that deadlocks - start 2 transactions, open 2 shared locks, and both try to insert a new row before either commits. Using exclusive locks (for update) I cannot come up with a scenario that results in a deadlock, since the very nature of the...

PHP

2424

Deadlocks

by: Mike Carr | last post by:

I am running into an issue. Recently I installed IBuySpy Portal and then converted the data source to odp.net. When debugging the app my machine would freeze or become really slow. I can reproduce it by: Setting a breakpoint somewhere in the code, hitting F5, once the application has completely loaded hit the stop button, make a quick change and then select F5 again. It appears that the debugger attaches to the previous aspnet_wp.exe...

ASP.NET

2492

Deadlocks caused by referential integrity checks

by: Greg Stark | last post by:

There's another poster complaining about referential integrity checks causing deadlocks. Unfortunately I've deleted the message so this response (and the archives aren't responding) isn't going to show up on the right thread. The reason the deadlock is happening is because of a known deficiency in Postgres that postgres has to take an exclusive lock on the records to ensure they aren't deleted before your insert/update commits....

PostgreSQL Database

1789

Update Deadlocks on Primary Key index

by: PeterC | last post by:

We're getting numerous deadlocks in a multi-user system where users are coming in and updating their own data. In our troubleshooting/traces, the deadlocks seem to be coming from lock contention on a Primary Key Clustered Index, not on the table itself. Also, from what we can see, the application is requesting multiple locks on the Primary Key Index as it proceeds to perform the update on the primary table. Can anybody shed some light...

Microsoft SQL Server

3899

Ongoing purging of active records causes deadlocks

by: bobdurie | last post by:

Hi, We have a Java application that runs against a variety of backends including Oracle and MSSql 2000 and 2005. Our application has a handful of active tables that are constantly being inserted into, and some of which are being selected from on a regular basis. We have a purge job to remove unneeded records that every hour "DELETE FROM <tableWHERE <datafield< <sometimestamp>". This is how we are purging because we need 100% up...

Microsoft SQL Server

8866

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

8781

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

8550

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

8638

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

5662

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

4365

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

2769

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

2006

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

1769

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General