|
|
Posted by Igor on 05/05/07 10:45
On Apr 26, 11:31 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Igor (jerosi...@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, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
I finally solved deadlock that was occurring in user login sp
i had something like this:
begin tran
if exists (select * from users_online where user_id=@id)
update users_online data=... where user_id=@id
else
insert into users_online (user_id, data) values (@id, ...)
commit
so i put a xlock on select statement and now i don't get any deadlocks
begin tran
if exists (select * from users_online with (xlock) where user_id=@id)
update users_online data=... where user_id=@id
else
insert into users_online (user_id, data) values (@id, ...)
commit
Navigation:
[Reply to this message]
|