Single DELETE statement deadlocking in Sybase ASE

Question

I've got a fairly straightforward process running on ASE 15.7, that creates rows in two tables and later deletes them. The tables are both configured to use datapages for the locking scheme, and the column I'm using to delete the rows is the first column in the clustered index. Chained transaction mode is on, but the stored procedure is not part of some external transaction.

For some reason, I occassionally see deadlocks on the first line of the stored procedure. What's weird is that it's deadlocking with another spid running that exact same line! If the rows they're both trying to delete share data or index pages, shouldn't one spid be blocked while the other one acquires the locks and then completes? I'm at a loss as to how to resolve or even explain this, and haven't found anything in Sybooks Online.

This is the procedure - I've anonymized the object names, but otherwise the code is copied directly from the source file.

create procedure spCleanup (
    @firstColumnInClusteredPrimaryKey int
) as
begin

delete from tableName where firstColumnInClusteredPrimaryKey = @firstColumnInClusteredPrimaryKey

delete from relatedTableName where firstColumnInClusteredPrimaryKey = @firstColumnInClusteredPrimaryKey

end
go

This is the deadlock information from Sybase's errorlog file. Again I've anonymized the object, server and user names, but the text is otherwise copy-pasted directly from the log.

00:0011:00000:00166:2013/10/08 00:41:56.16 server  Deadlock Id 100 detected
Deadlock Id 100 detected. 1 deadlock chain(s) involved.

Deadlock Id 100: Process (Familyid 0, Spid 134, Suid 13) was executing a DELETE command in the procedure 'spCleanup' at line 8 at nested level 1.
Deadlock Id 100: Process 134 was involved in application 'appName'.
Deadlock Id 100: Process 134 was involved on host name 'serverName'.
Deadlock Id 100: Process 134 was involved in transaction '$del'.
SQL Text: spCleanup  2916 
Executing procedure: spCleanup
Subordinate SQL Text: delete from tableName where firstColumnInClusteredPrimaryKey = @firstColumnInClusteredPrimaryKey


Deadlock Id 100: Process (Familyid 0, Spid 166, Suid 13) was executing a DELETE command in the procedure 'spCleanup' at line 8 at nested level 1.
Deadlock Id 100: Process 166 was involved in application 'appName'.
Deadlock Id 100: Process 166 was involved on host name 'serverName'.
Deadlock Id 100: Process 166 was involved in transaction '$del'.
SQL Text: spCleanup  2911 
Executing procedure: spCleanup
Subordinate SQL Text: delete from tableName where firstColumnInClusteredPrimaryKey = @firstColumnInClusteredPrimaryKey


Deadlock Id 100: Process (Familyid 0, Spid 166) was waiting for a 'exclusive page' lock on page 67858 of table 'tableName' in database 'dbName' but process (Familyid 0, Spid 134) already held a 'exclusive page' lock on it.
Deadlock Id 100: Process (Familyid 0, Spid 134) was waiting for a 'update page' lock on page 67941 of table 'tableName' in database 'dbName' but process (Familyid 0, Spid 166) already held a 'exclusive page' lock on it.

Deadlock Id 100: Process (Familyid 0, Spid 134) was chosen as the victim. 
Victim process host = `serverName', user = `userName' program name = `appName' host processes = `dbServerName' . 

End of deadlock information.

Answer 1

Original post describes a classic deadlock scenario with a twist, namely, a single DELETE ... WITH statement, which acquires 2 different kinds of locks.

Recommendation - change table from All Page Lock (APL) to Data Row Lock (DRL) scheme

• clustered index will be replaced by placement index separate from data page
• index access / updates will use latches rather than logical locks
• row-level deadlock conflict is less probably (than page-level's)

Both SPIDs are executing the same sproc with the 1st DELETE ... WHERE statement demanding 2 different kinds of locks:

• initially an UPDATE lock on a page when scanning for a row to delete (UPDATE lock is compatible with shared locks only but not with another UPDATE lock or exclusive lock);
• once a target row is found on a page, then the UPDATE lock must be upgraded to an exclusive lockbefore deleting that row.

The DEADLOCK events posted in your original question confirm the scenario :

• SPID 134 has held an exclusive lock on a page while waiting to acquire an UPDATE lock on another page i.e. it has deleted at least one row already and is continuing with searching for another (row to delete);
• SPID 166 blocks the first SPID's acquiring an UPDATE lock on a page while waiting to apply an exclusive lock on another page, which has an exclusive lock held by SPID 134 already (after deleting 1 row or more).