Deadlock Question

[Anita]

Hi All,

Can multiple updates on one table using single
query generate deadlock ?
For example, at the same time, there are 2 users
run 2 queries as follows :

User1 runs :
update tab1 set tab1.v = tab1.v + 1
from tab1 inner join tab2 on tab1.no = tab2.no

User2 runs :
update tab1 set tab1.v = tab1.v + 1
from tab1 inner join tab3 on tab1.no = tab3.no

Note :
The content of the column "no" on table tab2 :
('A','B','C',....,'X','Y','Z')
The content of the column "no" on table tab3
is like in table tab2, but in different order :
('Z','Y','X',.....,'C','B','A')

Thanks in advance

Anita Hery


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Anita (an*******@devdex.com) writes:

Can multiple updates on one table using single
query generate deadlock ?
For example, at the same time, there are 2 users
run 2 queries as follows :

User1 runs :
update tab1 set tab1.v = tab1.v + 1
from tab1 inner join tab2 on tab1.no = tab2.no

User2 runs :
update tab1 set tab1.v = tab1.v + 1
from tab1 inner join tab3 on tab1.no = tab3.no

Note :
The content of the column "no" on table tab2 :
('A','B','C',....,'X','Y','Z')
The content of the column "no" on table tab3
is like in table tab2, but in different order :
('Z','Y','X',.....,'C','B','A')

Tables in a relational database are sets, and data has no order.

But, of course, for the evaluation of a query the physical order may
affect such things as deadlock.

Anyway, I am not going to answer the question directly, because there
is a lot of unknown elements. Is tbl.v a primary key or at least
indexed? What about tbl2.no and tbl3.no? And what exactly is
different order?

CREATE TABLE statements for the tables and INSERT statemetns for the
data may help.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Anita]

Erland Sommarskog,

Thanks for your reply.

Deadlock can be found easily in several command steps.
But, how can I find it in multiple updates using
only one step of command ?

This question is posted because I do not know
exactly how SQL Server handles my sample query.
And I become worry after reading many deadlock
articles here. Especially deadlock that is caused
by table index.

Below is the description of the tables :

Table tb1 :
- no CHAR(10); no2 CHAR(10); v INT
- Index possibility : only one index, on no or on no2
- no is unique, no2 is unique
- v is not a key.

Table tb2 :
- x CHAR(10); no CHAR(10)
- Index : on x
- x is not unique, no is unique

Table tb3 :
- x CHAR(10); no CHAR(10)
- Index : on x
- x is not unique, no is unique

Regards,

Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Anita (an*******@devdex.com) writes:

Deadlock can be found easily in several command steps.
But, how can I find it in multiple updates using
only one step of command ?

Testing deadlocks that may occur from single statements are indeed not
trivial to construct at will.

One possibility is to write a small app - could even be a stored procedure
- that runs the supicious SQL statement all over again in an infinite
loop. If you get a deadlock, you now know that it can happen. If you
don't get a deadlock - well you still don't know, because may the test
was not good enough.

Another approach is to introduce a waitstate somewhere, so that you get
a chance to start a second query window with the competing query. This
is not trivial either. For a simple case, I used this function some
time ago:

create function nisse () returns int as
begin
exec master..xp_cmdshell 'osql -E -n -Q "WAITFOR DELAY ''00:00:20''"'
return 1
end

In your case, at least one your updates should read:

UPDATE tbl
SET col = dbo.nisse() -- Or some expression including dbo.nisse().
...

But of course, this constructs a situation which is not really the same
as the real-world scenario, and the observations may not be transferrable.
(But it seems to me that in this case, they could.)

Since you did not provide CREATE TABLE statements and INSERT statements
with sample data, I was too lazy to actually try this technique with
your example. :-)
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Anita]

When SQL Server receive query :
update tab1 set tab1.v = tab1.v + 1
from tab1 inner join tab2 on tab1.no = tab2.no
I expect it follows the procedure like this :
a. Find the rows that will be updated.
b. If they are not found then exit.
c. Try locking the rows found.
d. If locking is successfull then update the
rows and exit.
e. Wait for miliseconds.
f. If query timeout expires then exit.
g. goto c.

Since I do not have information about how SQL Server
handles the query, I usually insert tablock hint in the query :
update tab1 with (tablock) set tab1.v = tab1.v + 1
from tab1 inner join tab2 on tab1.no = tab2.no

Though by using tablock hint it will lock all rows
in the table (prevent other rows from being updated
by other users) but, I think I should take this way.
It is free from deadlock.

Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Anita (an*******@devdex.com) writes:

When SQL Server receive query :
update tab1 set tab1.v = tab1.v + 1
from tab1 inner join tab2 on tab1.no = tab2.no
I expect it follows the procedure like this :
a. Find the rows that will be updated.
b. If they are not found then exit.
c. Try locking the rows found.
d. If locking is successfull then update the
rows and exit.
e. Wait for miliseconds.
f. If query timeout expires then exit.
g. goto c.
I have to admit that I don't fully master the internal procedure, but
I would expect it to be somewhat different. I would expect that already
when SQL Server finds the matching rows that it applies at least
shared locks, possible also intent locks. Once a row is found to
qualify, I would suppose SQL Server puts an exclusive lock on a
row.

You mention "query timeout". I suppose you mean lock timeout, which you
control with SET LOCK_TIMEOUT. Query timeout is a client (mis)feature,
and does not affect locking.

Going back to your original post, you had these two statements:

User1 runs :
update tab1 set tab1.v = tab1.v + 1
from tab1 inner join tab2 on tab1.no = tab2.no

User2 runs :
update tab1 set tab1.v = tab1.v + 1
from tab1 inner join tab3 on tab1.no = tab3.no

Working from my assumptions above - which I like to stress are nothing
but assumptions, you could get a deadlock here, if the statistics on
the table are such that the optimizer chooses different query plans.
For instance, for the first query, the optimizer decides to scan tab1
and then perform a nested join with tab2. But for the second query,
the optimizer scans tab3, and performs a nested join with index seek
on tab1. If the two queries start at the same time, they will find
matching rows in tab1 in different order, and therefor they will
deadlock.
Since I do not have information about how SQL Server
handles the query, I usually insert tablock hint in the query :
update tab1 with (tablock) set tab1.v = tab1.v + 1
from tab1 inner join tab2 on tab1.no = tab2.no

Though by using tablock hint it will lock all rows
in the table (prevent other rows from being updated
by other users) but, I think I should take this way.
It is free from deadlock.

Yes, this should be deadlock free. But there are of course other issues
with tablock. If most updates are on single rows, tablock might be too
heavy-handed and lead to concurrency issues.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Anita]

Erland Sommarskog,

Thanks for your reply,

Yes, your assumption is somewhat different with what I expect. I expect
: if there are 10 matching rows and SQL Server can lock only 9 rows,
then : SQL Server unlock 9 rows, wait for a moment, and try locking 10
rows again.
If my expectation is true, then the query is deadlock free and I will
avoid using tablock hint.

My last question is where I can get information that tell us your
assumption or my expectation is true ?

Regards,
Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Anita (an*******@devdex.com) writes:

Yes, your assumption is somewhat different with what I expect. I expect
: if there are 10 matching rows and SQL Server can lock only 9 rows,
then : SQL Server unlock 9 rows, wait for a moment, and try locking 10
rows again.
If my expectation is true, then the query is deadlock free and I will
avoid using tablock hint.

My last question is where I can get information that tell us your
assumption or my expectation is true ?

So much I can tell with confidence, that SQL Server never releases locks
because it cannot get all locks it needs to carry out a task. While such
a strategy could reduce deadlock, it could have other nasty effects like
lock starvation. A process that needs to access many rows in a busy system
would never get all locks.

Also, I believe that the work order is something like: lock one row,
update that row, lock next row and so on. In this case, it is of course
even less possible to release rows.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Anita]

Erland Sommarskog,

Thanks for all your replies.

Regards,
Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!