Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.
share|improve this question
    
What is the isolation level you are using ? –  Kin Oct 25 '13 at 18:41
    
The default, it should be Read Committed for this server. I can double-check it later, if you think that makes a difference. There's only the implicit transaction though, so I would have thought all transaction levels above Read Uncommitted would behave the same way. –  Rick Oct 26 '13 at 10:47
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.