Deadlock on Sybase tables

rupal · #1 (**permalink**) 06-17-09, 08:13

Hi experts,

I'm trying to simulate a deadlock error on Sybase. On executing the following steps:

Step 1: Session 1
begin transaction
go
update pd1 set type='P' where number=0
go

Step 2: Session 2
begin transaction
go
update pd2 set type='P' where number=0
go

Step 3: Session 1
select * from pd2
go

Step 4: Session2
select * from pd1
go
Throws a deadlock error.

Step 5: Session3
select * from pd1
go
This query hangs until Session1 is issued a commit or rollback.

Does anybody know if the deadlock error can be generated repeatedly on the same table across different sessions? If yes, how?

pdreyer · #2 (**permalink**) 06-17-09, 11:27

Create test data

Code:

select *, filler1=convert(char(255),'a')
into tempdb..pd1
from master..spt_values a
where type='P'
go
create index ix1 on pd1(number)
go

Now from session1

Code:

1> use tempdb
2> go
1> begin tran
2> go
1> update pd1 set type='P' where number=0
2> go
(1 row affected)

Session2:

Code:

1> use tempdb
2> go
1> begin transaction
2> go
1> update pd1 set type='P' where number=1024
2> go
(1 row affected)
1> select number,type from pd1 where number=0
2> go

Session2 now waits for a lock to be released for number=0

Back to session1

Code:

1> select number,type from pd1 where number=1024
2> go
 number      type 
 ----------- ---- 
        1024 P    

(1 row affected)

session2 now get

Code:

Msg 1205, Level 13, State 1:
Server 'srv1', Line 1:
Your server command (family id #0, process id #280) encountered a deadlock situation. Please re-run your command.

session1 cleanup

Code:

1> rollback
2> go
1> drop table pd1
2> go

Lerac · #3 (**permalink**) 06-17-09, 11:47

Rupal,

Deadlocks will be consistently generated across multiple sessions if the conditions for deadlocks are met.

A deadlock only occurs if there is a collision in the locks from two separate sessions where the lock waits would never be resolved :
Process A aquires an update lock on Resource 1 and Process B aquires an update lock on Resource 2. Process A then attempts to aquire a lock on Resource 2, which in normal cases would wait until Process B releases the lock first. If Process B now attempts to aquire a locck on Resource 1, it means that both processes will wait indefinitely for each other to release their respective locks. This is a seen as a deadlock, and the server will have to select one of these 2 processes to be the victim. The victim's SQL batch gets terminated and all processing that it has done is rolled back.

In your example, Session 3 does not create a collision, it is simply waiting for Session 1 to release the lock(s) it has acquired on table pd1. This is a simple 'lock wait', and should be indicated as a blocking lock if you do an 'sp_who' from another session - status = 'lock sleep' and a non-zero blk_spid, indicating which spid is blocking it.
In my test scenario Session 1 kept on being the victim, and not Session 2, as you would logically expect. This is likely due to the number of records involved and CPU usage being higher for session 2. Deadlock victims are supposed to be selected based on the number of CPU cycles accumulated - the one with the least CPU usage gets selected as the victim. I just added a bit more processing for session 1 to get around this.

This would do the trick and indicate correct deadlock handling as expected :

Code:

--Step 1: Session 1
begin transaction
go
update pd1 set type='P' where number=0
-- Generate some extra cpu cycles to ensure that Session 1 is NOT the victim
select count(*) from pd1
select * from pd1
go


--Step 2: Session 2
begin transaction
go
update pd2 set type='P' where number=0
go

--Step 3: Session 3
begin transaction
go
update pd3 set type='P' where number=0
go

--Step 4: Session 1
select * from pd2
select * from pd3
go

--Step 5: Session2
select * from pd1
go
--Throws a deadlock error.

--Step 6: Session3
select * from pd1
go
--Also throws a deadlock error.

rupal · #4 (**permalink**) 06-25-09, 07:11

Thanks guys for your help.

Internet Services

Web Development

Digital Marketing

Consumer Tech