 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
Pierre le Riche
Guest
|
 Posted: Tue Sep 23, 2003 2:33 pm Post subject: MSSQL2K: Deadlocks |
 |
|
Hi,
Something which I am not quite sure about after reading the SQL Server Books
Online, is whether it is possible that discrete queries can cause a deadlock
on MSSQL2K. For example:
Run on Computer A:
update MyTable1
set
MyField1 = MyField1 + 1
where
MyTablePrimaryKey in (1, 2)
Run at exactly the same moment on Computer B:
update MyTable1
set
MyField1 = MyField1 + 1
where
MyTablePrimaryKey in (2, 1)
The queries are identical, but the order of the update is reversed. Is it
possible that A locks record 1 and B locks record 2 at the same instant and
then a deadlock ensues? Obviously this will happen if the update is split
into two separate update queries and the update order is reversed, but can
it also happen with discrete queries such as this?
Thanks,
Pierre
|
|
| Back to top |
|
 |
Walter Prins
Guest
|
| Posted: Mon Sep 29, 2003 10:48 pm Post subject: Re: MSSQL2K: Deadlocks |
|
|
"Pierre le Riche" <pleriche (AT) hotmail (DOT) com> wrote
| Quote: | Hi,
Something which I am not quite sure about after reading the SQL Server
Books
Online, is whether it is possible that discrete queries can cause a
deadlock
on MSSQL2K. For example:
Run on Computer A:
update MyTable1
set
MyField1 = MyField1 + 1
where
MyTablePrimaryKey in (1, 2)
Run at exactly the same moment on Computer B:
update MyTable1
set
MyField1 = MyField1 + 1
where
MyTablePrimaryKey in (2, 1)
The queries are identical, but the order of the update is reversed. Is it
possible that A locks record 1 and B locks record 2 at the same instant
and
then a deadlock ensues? Obviously this will happen if the update is split
into two separate update queries and the update order is reversed, but can
it also happen with discrete queries such as this?
|
I think so yes, if I understand your question correctly. Many potential
deadlock situations on SQL server actually arise in my experience due to SQL
server's lock management strategy, namely that of "lock escalation:" To
conserve resources, the engine will sometimes promote a granted rowlock to a
pagelock etc instead of granting another rowlock. If it happens that
another transaction already has rowlocks in the same page now to be locked
in its entirety by the first transaction, the first transaction will block,
until it can get the page lock. If the other transaction now tries to lock
another row (or it also tries to get a a lock promotion) it will also lock,
potentially resulting in deadlock... Thus, the engine can sort of trip
itself up. So, in theory, though I would deem it extremely unlikely in the
very simple example you cite, it should be possible for bot transactions
above to initially have row locks granted, and then have the engine decide
to upgrade both to page locks...
Even worse, lock escalation can cause completely *unrelated* queries to
deadlock each other, as you can imagine, due to them possibly hitting the
same pages or tables... You can sometimes fix some of these problems with
careful use lock hints "NOLOCK" and "ROWLOCK".
This article may prove useful where this is discussed a little better than I
can here: http://www.developerfusion.com/show/1688/1/
Groete weereens
Walter Prins
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system ([url]http://www.grisoft.com)[/url].
Version: 6.0.521 / Virus Database: 319 - Release Date: 23/09/2003
|
|
| Back to top |
|
|
Pierre le Riche
Guest
|
| Posted: Tue Sep 30, 2003 10:37 am Post subject: Re: MSSQL2K: Deadlocks |
|
|
Thanks Walter!
P.S. It's been a while. I actually saw some posts of yours a while back but
wasn't sure that it's the same Walter Prins  . Hope life is treating you
well. Regards to Mossie.
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
