Results 1 to 5 of 5

Thread: Dead lock issue

  1. #1
    Join Date
    Sep 2005
    Posts
    2

    Unanswered: Dead lock issue

    Sybase ASE Version: 15.0.3

    We have the row level locking on table 'mytable' and we are seeing the following deadlock very often. We have an index on col1 and iupdate will finish so fast.The following SQL is comming via application.


    Deadlock Id 7: detected. 1 deadlock chain(s) involved.

    Deadlock Id 7: Process (Familyid 0, Spid 69, Suid 4699) was executing a UPDATE command at line 1.
    Deadlock Id 7: Process 69 was involved in application '<none>'.
    Deadlock Id 7: Process 69 was involved on host name 'xxxxx'.
    Deadlock Id 7: Process 69 was involved in transaction '$chained_transaction'.
    SQL Text: update mytable set LastModDate=@p0, LastModEmpID=@p1 where col1=@p2
    Deadlock Id 7: Process (Familyid 0, Spid 62, Suid 4699) was executing a UPDATE command at line 1.
    Deadlock Id 7: Process 62 was involved in application '<none>'.
    Deadlock Id 7: Process 62 was involved on host name 'xxxxxx'.
    Deadlock Id 7: Process 62 was involved in transaction '$chained_transaction'.
    SQL Text: update mytable set LastModDate=@p0, LastModEmpID=@p1 where col1=@p2
    Deadlock Id 7: Process (Familyid 0, Spid 62) was waiting for a 'exclusive row' lock on row 0 page 289329 of the 'mytable' table in database 'mydatabase' but process (Familyid 0, Spid 69) already held a 'shared row' lock on it.
    Deadlock Id 7: Process (Familyid 0, Spid 69) was waiting for a 'exclusive row' lock on row 0 page 289329 of the 'mytable' table in database 'mydatabase' but process (Familyid 0, Spid 62) already held a 'shared row' lock on it.

    Deadlock Id 7: Process (Familyid 0, Spid 69) was chosen as the victim.
    Victim process host = `xxxxx', user = `user1' program name = `' host processes = `' .

    End of deadlock information.

    Do you have any idea how to avoide this deadlock ?


    Thanks
    Kris

  2. #2
    Join Date
    Jun 2009
    Location
    India
    Posts
    50
    Hi - I am not expert on this, but as a suggestion please try below if they would help you:

    (i) Please change the locking scheme for the tables that are causing blocking issue.Use DOL instead. (This doesn't guarantee elimination of deadlocks).

    (ii) Always access tables in the same order inside a transaction. i.e joins used in tables for a transaction should be in the same order for other transactions. This may help to some extent.

    (iii) Avoid long-running transactions.Use transactions as small as possible i.e don't use select queries within begin tran & commit tran in as SP.

    (iv) If above doesn't help, enable configuration parameter "print deadlock information" and study the deadlock information printed in error log file of that instance to understand which queries ran into a deadlock.

    (v) don't change the configuration parameter "deadlock checking period" to "0". Set this to default value.

    (vi) Avoid using keyword headlock in DDLs or SPs, as this increases the risk of deadlock. Use holdlock only when you require repeatable reads within a transaction
    Last edited by Neevarp; 02-18-10 at 09:55.

  3. #3
    Join Date
    Sep 2005
    Posts
    2
    Neevarp,

    Thank you for the info but I already have DOL on the table and also taking care about what you mentioned but still deadlock occuring.Its occuring on same page and same row(not always same page it depends on the row that updating )

    Thanks
    kris

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Find the places in your application where this occurs:
    Code:
    SQL Text: update mytable set LastModDate=@p0, LastModEmpID=@p1 where col1=@p2
    It could be that this update causes a table scan to locate the records in the table. If so, see if any indexes can help target the search better. Alternatively, there could be a declared transaction that has a select followed by an update statement. Locks are held during a declared transaction, so the shared lock would be held, until the whole transaction is finished.

  5. #5
    Join Date
    Jun 2009
    Location
    India
    Posts
    50
    Agreed with "MCrowley" comments.

    Shared locks are used by Select statements. Shared lock held on page 289329 was not released, so other process could not get exclusive lock on that page.

    Also, there could be chance of deadlock between two transactions if one is processing in the sequence A - B - C while the other runs C - B - A.

    Please provide us with the SQL statements (DDL) of the both transactions which was causing deadlock issue.
    Last edited by Neevarp; 02-19-10 at 00:58.

Posting Permissions

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