CODEWITHSUNDEEP
javasap-asedatabase-deadlocksoracle-coherence
user2654241
user2654241

Reputation: 167

Sybase deadlock executing stored procedure that inserts record if not present

I am using Oracle Coherence with sybase tables as backstore of data. My java program inserts data into coherence cache and the data gets written to tables by means of stored procedure.

The code calls store procedure is like below for a batch of records

 begin
 beging tran

 update tableA
 set 
      col1=val1,
      col2=val2
 where
      col3=val3

 if(@@rowcount=0)
 insert into tableA
 (
       col1,
       col2,
       col3
 )
 values
 (
       val1,
       val2,
       val3
 )

 if(@@error=0)
 begin
      rollback tran
 end

 commit tran
 end

Nearly 50 threads may call the procedure by calling execureBatch where a batch contains nearly 125 records, however all records are mutually different and the table got datarows locking.

But Iget deadlock error by means of a BatchUpdateException

 java.sql.BatchUpdateException: Your server command (family id #0, process id #464) encountered a deadlock situation. Please re-run your command.

What can be the reason of getting the deadlock here?

Upvotes: 0

Views: 1245

Answers (1)

RobV
RobV

Reputation: 2378

Most likely, tableA is using the default allpages locking, and there's a deadlock occurring between the index and data pages. But the only way to know for sure is to enable the Sybase ASE config setting 'print deadlock information', and see what gets logged into the ASE errorlog file.

BTW, the test 'if(@@error=0)' to perform the rollback, looks funny. This will roll back if @@error = 0. But that actually means the preceding statement went well and there was no error, so typically you would commit instead of rolling back. Another issue is that that this is not working as you will expect. After executing the first 'if' statement '(@@rowcount=0)', the @@error value will have been reset to 0, since the 'if' statement always succeeds. This means you'll never be able to catch an error status from the update. The right way to do this is to copy, in a single statement, @@rowcount and @@error into local variables directly after a DML statement, and then perform any tests as needed.

Lastly, using 'if' without 'begin-end' compound statements is dangerous as it can quickly lead to code that doesn't do what you expect. As a general principle, it's best to always use begin-end after 'if', 'else' and 'while'.

Upvotes: 1

Related Questions