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.