473,226 Members | 1,286 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,226 software developers and data experts.

Deadlocks workaround?

Hi All,

I have read about deadlocks here on Google and I was surprised to read
that an update and a select on the same table could get into a
deadlock because of the table's index. The update and the select
access the index in opposite orders, thereby causing the deadlock.
This sounds to me as a bug in SQL Server!

My question is: Could you avoid this by reading the table with a
'select * from X(updlock)' before updating it? I mean: Would this
result in the update transaction setting a lock on the index rows
before accessing the data rows?

Merry Christmas!
/Fredrik Möller
Jul 20 '05 #1
3 3808
louis nguyen (lo************@hotmail.com) writes:
In the example you posted, I typically use a "set transaction" option.
My understanding is that this would prevent all shared locks. What
is your opinion (pros/cons) of this? Thanks, Louis.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT @id = coalesce(MAX(id), 0) + 1 FROM tbl
INSERT tbl (id, ...) VALUES (@id, ...)
COMMIT TRANSACTION


This is very likely to cause deadlocks. The isolation level does not
affect the ability to get shared locks. It only affects what you can
see if you issue the same statement later in the query.

Try this:

CREATE TABLE tbl (id int NOT NULL)
go
DECLARE @id int
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT @id = coalesce(MAX(id), 0) + 1 FROM tbl
WAITFOR DELAY '00:00:10'
INSERT tbl (id) VALUES (@id)
COMMIT TRANSACTION

First create the table, then run the batch from two windows. You will
get a deadlock. Add "WITH (UPDLOCK)" after the table, and both
batches will succeed.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
Hi Erland,

Wow. I learned my lesson. Thanks, Louis.
Jul 20 '05 #3
Hi Erland and Louis,
The example you provided was very enlightening in showing the
difference between UPDLOCK and HOLDLOCK/SERIALIZABLE. Thank you!
As to the locking of index and data rows I might come back later with
an example illustrating the problem.
Regards
Fredrik
Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
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...
3
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...
1
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...
4
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...
7
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...
9
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...
6
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...
0
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...
4
by: John Rivers | last post by:
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...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.