Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2009
    Posts
    2

    Unanswered: Deadlock on Sybase tables

    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?

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    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
    Last edited by pdreyer; 06-17-09 at 11:33.

  3. #3
    Join Date
    Jun 2009
    Location
    South Africa
    Posts
    33
    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.

  4. #4
    Join Date
    Jun 2009
    Posts
    2
    Thanks guys for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •