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

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

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

Find the places in your application where this occurs:
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.

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.