lock/ deadlock questions

I got 4 deadlocks in 15 minutes yesterday. The first three happened in a 1
second time frame and the last 1 happened 15 minutes later. From looking in
the SQL error log it what appear that they all got hosed by the same spid.
Ive been reading up on Locking this morning and how it affects Deadlocks and
have some questions. Im pretty clueless about Locking from what Im finding
out so please be gentle. According to BOL-Update Locks:
--start
Update Locks
Update (U) locks prevent a common form of deadlock. A typical update pattern
consists of a transaction reading a record, acquiring a shared (S) lock on
the resource (page or row), and then modifying the row, which requires lock
conversion to an exclusive (X) lock. If two transactions acquire shared-mode
locks on a resource and then attempt to update data concurrently, one
transaction attempts the lock conversion to an exclusive (X) lock. The
shared-mode-to-exclusive lock conversion must wait because the exclusive
lock for one transaction is not compatible with the shared-mode lock of the
other transaction; a lock wait occurs. The second transaction attempts to
acquire an exclusive (X) lock for its update. Because both transactions are
converting to exclusive (X) locks, and they are each waiting for the other
transaction to release its shared-mode lock, a deadlock occurs.
To avoid this potential deadlock problem, update (U) locks are used. Only
one transaction can obtain an update (U) lock to a resource at a time. If a
transaction modifies a resource, the update (U) lock is converted to an
exclusive (X) lock. Otherwise, the lock is converted to a shared-mode lock.
--finish
But then in Deadlocks, Avoiding it also says (Gauranteed Deadlock picture):
Transaction 1 Transaction 2
Begin Trans Begin Trans
Update Supplier Update Part
Update Part Update Supplier
Commit Trans Commit Trans
So my questions are:
Do the Begin Trans above actually mean having BEGIN TRANS inside your Proc?
Also, shouldnt these type of Deadlocks not occur since becuase of Update
Locks saving the day?
--
SQL2K SP3
TIA, ChrisROn Sat, 18 Dec 2004 08:26:53 -0800, "ChrisR" <bla@.noemail.com> wrote:
>But then in Deadlocks, Avoiding it also says (Gauranteed Deadlock picture):
>Transaction 1 Transaction 2
>Begin Trans Begin Trans
>Update Supplier Update Part
>Update Part Update Supplier
>Commit Trans Commit Trans
>So my questions are:
>Do the Begin Trans above actually mean having BEGIN TRANS inside your Proc?
>Also, shouldnt these type of Deadlocks not occur since becuase of Update
>Locks saving the day?
Yes, actually use BEGIN TRANS, that gives you a multi-statement atomic
transaction. Of course, multi-statement transactions are exactly what
get tangled most often in deadlocks, but you do want atomic
transactions, right?
The #1 way to get deadlocks is as illustrated - get the same things in
a different order. If you get them in the SAME order, you avoid
deadlocks. You may occassionally get them anyway from obscure effects
of larger statements that maybe get internally deadlocked over index
pages or other things that are normally invisible, but the rule is:
define a fixed order for getting multiple resources, and you eliminate
about 98% of deadlock problems.
These internal lock types are things you shouldn't ever have to worry
about, IMHO.
J.|||Are deadlocks possible without specifying a transaction?
> >Connection 1 Connection 2
> >Update Supplier Update Part
> >Update Part Update Supplier
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:esq8s0hlo06qr3e3nr4e9rv6688jg8f8c7@.4ax.com...
> On Sat, 18 Dec 2004 08:26:53 -0800, "ChrisR" <bla@.noemail.com> wrote:
> >But then in Deadlocks, Avoiding it also says (Gauranteed Deadlock
picture):
> >
> >Transaction 1 Transaction 2
> >Begin Trans Begin Trans
> >Update Supplier Update Part
> >Update Part Update Supplier
> >Commit Trans Commit Trans
> >
> >So my questions are:
> >
> >Do the Begin Trans above actually mean having BEGIN TRANS inside your
Proc?
> >Also, shouldnt these type of Deadlocks not occur since becuase of Update
> >Locks saving the day?
> Yes, actually use BEGIN TRANS, that gives you a multi-statement atomic
> transaction. Of course, multi-statement transactions are exactly what
> get tangled most often in deadlocks, but you do want atomic
> transactions, right?
> The #1 way to get deadlocks is as illustrated - get the same things in
> a different order. If you get them in the SAME order, you avoid
> deadlocks. You may occassionally get them anyway from obscure effects
> of larger statements that maybe get internally deadlocked over index
> pages or other things that are normally invisible, but the rule is:
> define a fixed order for getting multiple resources, and you eliminate
> about 98% of deadlock problems.
> These internal lock types are things you shouldn't ever have to worry
> about, IMHO.
> J.
>|||Yes - transactions can also be declared above the stored proc, either in
TSQL that calls the stored proc or by a client transaction monitor such as
COM+.
Regards,
Greg Linwood
SQL Server MVP
"ChrisR" <bla@.noemail.com> wrote in message
news:ueoyI6S5EHA.2180@.TK2MSFTNGP12.phx.gbl...
> Are deadlocks possible without specifying a transaction?
>> >Connection 1 Connection 2
>> >Update Supplier Update Part
>> >Update Part Update Supplier
>
>
>
> "JXStern" <JXSternChangeX2R@.gte.net> wrote in message
> news:esq8s0hlo06qr3e3nr4e9rv6688jg8f8c7@.4ax.com...
>> On Sat, 18 Dec 2004 08:26:53 -0800, "ChrisR" <bla@.noemail.com> wrote:
>> >But then in Deadlocks, Avoiding it also says (Gauranteed Deadlock
> picture):
>> >
>> >Transaction 1 Transaction 2
>> >Begin Trans Begin Trans
>> >Update Supplier Update Part
>> >Update Part Update Supplier
>> >Commit Trans Commit Trans
>> >
>> >So my questions are:
>> >
>> >Do the Begin Trans above actually mean having BEGIN TRANS inside your
> Proc?
>> >Also, shouldnt these type of Deadlocks not occur since becuase of Update
>> >Locks saving the day?
>> Yes, actually use BEGIN TRANS, that gives you a multi-statement atomic
>> transaction. Of course, multi-statement transactions are exactly what
>> get tangled most often in deadlocks, but you do want atomic
>> transactions, right?
>> The #1 way to get deadlocks is as illustrated - get the same things in
>> a different order. If you get them in the SAME order, you avoid
>> deadlocks. You may occassionally get them anyway from obscure effects
>> of larger statements that maybe get internally deadlocked over index
>> pages or other things that are normally invisible, but the rule is:
>> define a fixed order for getting multiple resources, and you eliminate
>> about 98% of deadlock problems.
>> These internal lock types are things you shouldn't ever have to worry
>> about, IMHO.
>> J.
>|||I may have phrased my question wrong. Say these two guys aren't in a
transaction:
> >> >Connection 1 Connection 2
> >> >Update Supplier Update Part
> >> >Update Part Update Supplier
> >
Is it still possible to have a deadlock?
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:#gY2YAZ5EHA.4028@.TK2MSFTNGP15.phx.gbl...
> Yes - transactions can also be declared above the stored proc, either in
> TSQL that calls the stored proc or by a client transaction monitor such as
> COM+.
> Regards,
> Greg Linwood
> SQL Server MVP
> "ChrisR" <bla@.noemail.com> wrote in message
> news:ueoyI6S5EHA.2180@.TK2MSFTNGP12.phx.gbl...
> > Are deadlocks possible without specifying a transaction?
> >
> >> >Connection 1 Connection 2
> >> >Update Supplier Update Part
> >> >Update Part Update Supplier
> >
> >
> >
> >
> >
> >
> > "JXStern" <JXSternChangeX2R@.gte.net> wrote in message
> > news:esq8s0hlo06qr3e3nr4e9rv6688jg8f8c7@.4ax.com...
> >> On Sat, 18 Dec 2004 08:26:53 -0800, "ChrisR" <bla@.noemail.com> wrote:
> >> >But then in Deadlocks, Avoiding it also says (Gauranteed Deadlock
> > picture):
> >> >
> >> >Transaction 1 Transaction 2
> >> >Begin Trans Begin Trans
> >> >Update Supplier Update Part
> >> >Update Part Update Supplier
> >> >Commit Trans Commit Trans
> >> >
> >> >So my questions are:
> >> >
> >> >Do the Begin Trans above actually mean having BEGIN TRANS inside your
> > Proc?
> >> >Also, shouldnt these type of Deadlocks not occur since becuase of
Update
> >> >Locks saving the day?
> >>
> >> Yes, actually use BEGIN TRANS, that gives you a multi-statement atomic
> >> transaction. Of course, multi-statement transactions are exactly what
> >> get tangled most often in deadlocks, but you do want atomic
> >> transactions, right?
> >>
> >> The #1 way to get deadlocks is as illustrated - get the same things in
> >> a different order. If you get them in the SAME order, you avoid
> >> deadlocks. You may occassionally get them anyway from obscure effects
> >> of larger statements that maybe get internally deadlocked over index
> >> pages or other things that are normally invisible, but the rule is:
> >> define a fixed order for getting multiple resources, and you eliminate
> >> about 98% of deadlock problems.
> >>
> >> These internal lock types are things you shouldn't ever have to worry
> >> about, IMHO.
> >>
> >> J.
> >>
> >
> >
>|||On Sat, 18 Dec 2004 10:22:53 -0800, "ChrisR" <bla@.noemail.com> wrote:
>Are deadlocks possible without specifying a transaction?
>> >Connection 1 Connection 2
>> >Update Supplier Update Part
>> >Update Part Update Supplier
In theory, this should eliminate deadlock problems.
But note that instead, you have "race" problems, where first #1
updates the supplier, then #2 updates the part, then #1 tries to
update the part but doesn't know what #2 already updated it, yada
yada.
And on occassion there have been SQLServer bugs with parallelism that
caused deadlocks from even single statements, just to spice up your
life a little!
J.|||ChrisR wrote:
> I may have phrased my question wrong. Say these two guys aren't in a
> transaction:
>> Connection 1 Connection 2
>> Update Supplier Update Part
>> Update Part Update Supplier
> Is it still possible to have a deadlock?
>
See Autocommit Transactions in BOL. That should explain what you are
asking. From a tool like QA, the answer is no. You won't see a deadlock
since each statement is atomic. This assumes you have autocommit
transactions turned on (the default). If you have implicit transactions
on (see SET IMPLICIT_TRANSACTIONS in BOL), a transaction is
automatically started for you on the first statement. In that case, you
would have a deadlock possibility. If these were executed from an
application, it would depend on the properties of the connection and how
the library handles this case.
--
David Gugick
Imceda Software
www.imceda.com|||Also, don't confuse an UPDATE statement with an UPDATE LOCK. In the
example, the normal Share to Exclusive lock conversion will happen, if the
two statements were written thus, a deadlock could be avoided:
Connection 1 Connection 2
-- --
--
UPDATE Supplier WITH (UPDLOCK) UPDATE Part WITH (UPDLOCK)
UPDATE Part WITH(UPDLOCK) UPDATE Supplier WITH (UPDLOCK)
If you coded this update within a stored procedure, you could gain further
control by accessing the resources in the same order, thus, further reducing
the chances for deadlocking.
Sincerely,
Anthony Thomas
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:eLNZbff5EHA.3376@.TK2MSFTNGP12.phx.gbl...
ChrisR wrote:
> I may have phrased my question wrong. Say these two guys aren't in a
> transaction:
>> Connection 1 Connection 2
>> Update Supplier Update Part
>> Update Part Update Supplier
> Is it still possible to have a deadlock?
>
See Autocommit Transactions in BOL. That should explain what you are
asking. From a tool like QA, the answer is no. You won't see a deadlock
since each statement is atomic. This assumes you have autocommit
transactions turned on (the default). If you have implicit transactions
on (see SET IMPLICIT_TRANSACTIONS in BOL), a transaction is
automatically started for you on the first statement. In that case, you
would have a deadlock possibility. If these were executed from an
application, it would depend on the properties of the connection and how
the library handles this case.
--
David Gugick
Imceda Software
www.imceda.com|||Conceptually no, but if you really want to get to the bottom of your
deadlock source, I suggest you trace the deadlock graph to the error log
usiny DBCC TRACEON (1204, 2605, -1) so we can give you a concise answer to
what's actually going on in your db when a deadlock occurs.
JXStern's given you some good advice - there are the wel documented deadlock
scenarios such as cyclical deadlocking (what you're describing), conversion
deadlocking (update lock escalation conflicts) but also other types which
occur deeper within the DB such as parallelism deadlocks. Examples of the
single statement deadlocking he referred to includes Update Supplier, where
Supplier has multiple indexes. The same update statement might update the
various indexes on the table in conflicting orders and cause an internal
deadlock. This happens and is fairly common. If you log & post your deadlock
trace graph, you'll get the full picture about what's going on when you
experience your deadlocks.
Regards,
Greg Linwood
SQL Server MVP
"ChrisR" <bla@.noemail.com> wrote in message
news:ugSJ4Ue5EHA.3616@.TK2MSFTNGP11.phx.gbl...
>I may have phrased my question wrong. Say these two guys aren't in a
> transaction:
>> >> >Connection 1 Connection 2
>> >> >Update Supplier Update Part
>> >> >Update Part Update Supplier
>> >
> Is it still possible to have a deadlock?
>
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:#gY2YAZ5EHA.4028@.TK2MSFTNGP15.phx.gbl...
>> Yes - transactions can also be declared above the stored proc, either in
>> TSQL that calls the stored proc or by a client transaction monitor such
>> as
>> COM+.
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> "ChrisR" <bla@.noemail.com> wrote in message
>> news:ueoyI6S5EHA.2180@.TK2MSFTNGP12.phx.gbl...
>> > Are deadlocks possible without specifying a transaction?
>> >
>> >> >Connection 1 Connection 2
>> >> >Update Supplier Update Part
>> >> >Update Part Update Supplier
>> >
>> >
>> >
>> >
>> >
>> >
>> > "JXStern" <JXSternChangeX2R@.gte.net> wrote in message
>> > news:esq8s0hlo06qr3e3nr4e9rv6688jg8f8c7@.4ax.com...
>> >> On Sat, 18 Dec 2004 08:26:53 -0800, "ChrisR" <bla@.noemail.com> wrote:
>> >> >But then in Deadlocks, Avoiding it also says (Gauranteed Deadlock
>> > picture):
>> >> >
>> >> >Transaction 1 Transaction 2
>> >> >Begin Trans Begin Trans
>> >> >Update Supplier Update Part
>> >> >Update Part Update Supplier
>> >> >Commit Trans Commit Trans
>> >> >
>> >> >So my questions are:
>> >> >
>> >> >Do the Begin Trans above actually mean having BEGIN TRANS inside your
>> > Proc?
>> >> >Also, shouldnt these type of Deadlocks not occur since becuase of
> Update
>> >> >Locks saving the day?
>> >>
>> >> Yes, actually use BEGIN TRANS, that gives you a multi-statement atomic
>> >> transaction. Of course, multi-statement transactions are exactly what
>> >> get tangled most often in deadlocks, but you do want atomic
>> >> transactions, right?
>> >>
>> >> The #1 way to get deadlocks is as illustrated - get the same things in
>> >> a different order. If you get them in the SAME order, you avoid
>> >> deadlocks. You may occassionally get them anyway from obscure effects
>> >> of larger statements that maybe get internally deadlocked over index
>> >> pages or other things that are normally invisible, but the rule is:
>> >> define a fixed order for getting multiple resources, and you eliminate
>> >> about 98% of deadlock problems.
>> >>
>> >> These internal lock types are things you shouldn't ever have to worry
>> >> about, IMHO.
>> >>
>> >> J.
>> >>
>> >
>> >
>>
>|||The send to error log trace flag is 3605 not 2605.
Sincerely,
Anthony Thomas
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:uCZgY5o5EHA.2156@.TK2MSFTNGP10.phx.gbl...
Conceptually no, but if you really want to get to the bottom of your
deadlock source, I suggest you trace the deadlock graph to the error log
usiny DBCC TRACEON (1204, 2605, -1) so we can give you a concise answer to
what's actually going on in your db when a deadlock occurs.
JXStern's given you some good advice - there are the wel documented deadlock
scenarios such as cyclical deadlocking (what you're describing), conversion
deadlocking (update lock escalation conflicts) but also other types which
occur deeper within the DB such as parallelism deadlocks. Examples of the
single statement deadlocking he referred to includes Update Supplier, where
Supplier has multiple indexes. The same update statement might update the
various indexes on the table in conflicting orders and cause an internal
deadlock. This happens and is fairly common. If you log & post your deadlock
trace graph, you'll get the full picture about what's going on when you
experience your deadlocks.
Regards,
Greg Linwood
SQL Server MVP
"ChrisR" <bla@.noemail.com> wrote in message
news:ugSJ4Ue5EHA.3616@.TK2MSFTNGP11.phx.gbl...
>I may have phrased my question wrong. Say these two guys aren't in a
> transaction:
>> >> >Connection 1 Connection 2
>> >> >Update Supplier Update Part
>> >> >Update Part Update Supplier
>> >
> Is it still possible to have a deadlock?
>
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:#gY2YAZ5EHA.4028@.TK2MSFTNGP15.phx.gbl...
>> Yes - transactions can also be declared above the stored proc, either in
>> TSQL that calls the stored proc or by a client transaction monitor such
>> as
>> COM+.
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> "ChrisR" <bla@.noemail.com> wrote in message
>> news:ueoyI6S5EHA.2180@.TK2MSFTNGP12.phx.gbl...
>> > Are deadlocks possible without specifying a transaction?
>> >
>> >> >Connection 1 Connection 2
>> >> >Update Supplier Update Part
>> >> >Update Part Update Supplier
>> >
>> >
>> >
>> >
>> >
>> >
>> > "JXStern" <JXSternChangeX2R@.gte.net> wrote in message
>> > news:esq8s0hlo06qr3e3nr4e9rv6688jg8f8c7@.4ax.com...
>> >> On Sat, 18 Dec 2004 08:26:53 -0800, "ChrisR" <bla@.noemail.com> wrote:
>> >> >But then in Deadlocks, Avoiding it also says (Gauranteed Deadlock
>> > picture):
>> >> >
>> >> >Transaction 1 Transaction 2
>> >> >Begin Trans Begin Trans
>> >> >Update Supplier Update Part
>> >> >Update Part Update Supplier
>> >> >Commit Trans Commit Trans
>> >> >
>> >> >So my questions are:
>> >> >
>> >> >Do the Begin Trans above actually mean having BEGIN TRANS inside your
>> > Proc?
>> >> >Also, shouldnt these type of Deadlocks not occur since becuase of
> Update
>> >> >Locks saving the day?
>> >>
>> >> Yes, actually use BEGIN TRANS, that gives you a multi-statement atomic
>> >> transaction. Of course, multi-statement transactions are exactly what
>> >> get tangled most often in deadlocks, but you do want atomic
>> >> transactions, right?
>> >>
>> >> The #1 way to get deadlocks is as illustrated - get the same things in
>> >> a different order. If you get them in the SAME order, you avoid
>> >> deadlocks. You may occassionally get them anyway from obscure effects
>> >> of larger statements that maybe get internally deadlocked over index
>> >> pages or other things that are normally invisible, but the rule is:
>> >> define a fixed order for getting multiple resources, and you eliminate
>> >> about 98% of deadlock problems.
>> >>
>> >> These internal lock types are things you shouldn't ever have to worry
>> >> about, IMHO.
>> >>
>> >> J.
>> >>
>> >
>> >
>>
>|||Thanks for picking that typo up!
Cheers,
Greg Linwood
SQL Server MVP
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
news:ek9x4sp5EHA.2012@.TK2MSFTNGP15.phx.gbl...
> The send to error log trace flag is 3605 not 2605.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:uCZgY5o5EHA.2156@.TK2MSFTNGP10.phx.gbl...
> Conceptually no, but if you really want to get to the bottom of your
> deadlock source, I suggest you trace the deadlock graph to the error log
> usiny DBCC TRACEON (1204, 2605, -1) so we can give you a concise answer to
> what's actually going on in your db when a deadlock occurs.
> JXStern's given you some good advice - there are the wel documented
> deadlock
> scenarios such as cyclical deadlocking (what you're describing),
> conversion
> deadlocking (update lock escalation conflicts) but also other types which
> occur deeper within the DB such as parallelism deadlocks. Examples of the
> single statement deadlocking he referred to includes Update Supplier,
> where
> Supplier has multiple indexes. The same update statement might update the
> various indexes on the table in conflicting orders and cause an internal
> deadlock. This happens and is fairly common. If you log & post your
> deadlock
> trace graph, you'll get the full picture about what's going on when you
> experience your deadlocks.
> Regards,
> Greg Linwood
> SQL Server MVP
> "ChrisR" <bla@.noemail.com> wrote in message
> news:ugSJ4Ue5EHA.3616@.TK2MSFTNGP11.phx.gbl...
>>I may have phrased my question wrong. Say these two guys aren't in a
>> transaction:
>> >> >Connection 1 Connection 2
>> >> >Update Supplier Update Part
>> >> >Update Part Update Supplier
>> >
>> Is it still possible to have a deadlock?
>>
>> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
>> news:#gY2YAZ5EHA.4028@.TK2MSFTNGP15.phx.gbl...
>> Yes - transactions can also be declared above the stored proc, either in
>> TSQL that calls the stored proc or by a client transaction monitor such
>> as
>> COM+.
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> "ChrisR" <bla@.noemail.com> wrote in message
>> news:ueoyI6S5EHA.2180@.TK2MSFTNGP12.phx.gbl...
>> > Are deadlocks possible without specifying a transaction?
>> >
>> >> >Connection 1 Connection 2
>> >> >Update Supplier Update Part
>> >> >Update Part Update Supplier
>> >
>> >
>> >
>> >
>> >
>> >
>> > "JXStern" <JXSternChangeX2R@.gte.net> wrote in message
>> > news:esq8s0hlo06qr3e3nr4e9rv6688jg8f8c7@.4ax.com...
>> >> On Sat, 18 Dec 2004 08:26:53 -0800, "ChrisR" <bla@.noemail.com> wrote:
>> >> >But then in Deadlocks, Avoiding it also says (Gauranteed Deadlock
>> > picture):
>> >> >
>> >> >Transaction 1 Transaction 2
>> >> >Begin Trans Begin Trans
>> >> >Update Supplier Update Part
>> >> >Update Part Update Supplier
>> >> >Commit Trans Commit Trans
>> >> >
>> >> >So my questions are:
>> >> >
>> >> >Do the Begin Trans above actually mean having BEGIN TRANS inside
>> >> >your
>> > Proc?
>> >> >Also, shouldnt these type of Deadlocks not occur since becuase of
>> Update
>> >> >Locks saving the day?
>> >>
>> >> Yes, actually use BEGIN TRANS, that gives you a multi-statement
>> >> atomic
>> >> transaction. Of course, multi-statement transactions are exactly
>> >> what
>> >> get tangled most often in deadlocks, but you do want atomic
>> >> transactions, right?
>> >>
>> >> The #1 way to get deadlocks is as illustrated - get the same things
>> >> in
>> >> a different order. If you get them in the SAME order, you avoid
>> >> deadlocks. You may occassionally get them anyway from obscure
>> >> effects
>> >> of larger statements that maybe get internally deadlocked over index
>> >> pages or other things that are normally invisible, but the rule is:
>> >> define a fixed order for getting multiple resources, and you
>> >> eliminate
>> >> about 98% of deadlock problems.
>> >>
>> >> These internal lock types are things you shouldn't ever have to worry
>> >> about, IMHO.
>> >>
>> >> J.
>> >>
>> >
>> >
>>
>>
>