Posted by Erland Sommarskog on 04/26/07 21:31

Igor (jerosimic@gmail.com) writes:
> Can you give me an example for this "deadlocks can be avoided with
> proper indexes, and also proper access order." or if you have any websites
> that explain this if it's not to much trouble.

What I had in mind when I talked about indexing, is that if your indexing
is not the best one, you can get table scans, and this increases the risk
for deadlock, for at least two reasons: 1) more rows have to be locked (and
thus more likely that two processes want to lock the same resource at
the same time) and 2) transactions are longer (so locks are held for a
longer time).

Here is a very simple example where you get a deadlock because of access
order. First run:

CREATE TABLE tbl1 (a int NOT NULL)
CREATE TABLE tbl2 (a int NOT NULL)
go
INSERT tbl1 (a) VALUES(1)
INSERT tbl2 (a) VALUES(1)
go

The run in one window:

BEGIN TRANSACTION
UPDATE tbl1 SET a = 123
WAITFOR DELAY '00:00:05'
UPDATE tbl2 SET a = 123
COMMIT TRANSACTION

And in another:

BEGIN TRANSACTION
UPDATE tbl2 SET a = 123
WAITFOR DELAY '00:00:05'
UPDATE tbl1 SET a = 123
COMMIT TRANSACTION

If you want to read about troubleshooting deadlocks, there is a section in
Books Online. It's far from whole-covering, but I doubt that any text
on deadlocking is. Some deadlocks are very easy to avoid, whereas others
can only be remedied with a serious redesign of the application.

In any basic text, you will find the advice of always accessing tables
in the same order. But in a complex application, this is far from a
trivial matter.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]