Getting deadlocks in sqlserver

Question

I am getting deadlocks occasionally in sql server. I created a function for locking non database operations (credit card processing) so duplicates cannot happen. My functions are as follows (sorry for the tcl, but the sql is clear enough). Can anyone see why a deadlock happens occasionally?????

proc ims_syn_lock_object { db object {timeout 30} {wait 1}}  {   
 if {[catch {
  while {true} {
   am_dbtransaction begin $db

   # read the object locks that aren't timed out       
   set result [am_db1cell $db "SELECT object from GranularLocks WITH (ROWLOCK,HOLDLOCK) where object = [ns_dbquotevalue $object] AND  timeActionMade > DATEADD(second,-timeout, GETDATE())"]

   # check to see if this object is locked and not timed out
   if { [string equal "" $result] } {
    break;
   } else {
    # another process has this object and it is not timed out. 
    # release the row lock
    am_dbtransaction rollback $db

    if { $wait } {
     # sleep for between 400 and 800 miliseconds
     sleep [expr [ns_rand 400] + 400]                    
    } else {
     # we aren't waiting on locked resources.
     return 0;
    }
   }            
  }

  # either the object lock has timed out, or the object isn't locked
  # create the object lock.          
  ns_db dml $db "DELETE FROM GranularLocks  WHERE object = [ns_dbquotevalue $object]"
  ns_db dml $db "INSERT INTO GranularLocks(object,timeout) VALUES ([ns_dbquotevalue $object],[ns_dbquotevalue $timeout int])"   

  # releases the row lock and commits the transaction
  am_dbtransaction commit $db    

 } errMsg]} {
  ns_log Notice "Could not lock $object. $errMsg"
  catch { 
   am_dbtransaction rollback $db 
  } errMsg
  return 0
 }
 return 1     

}


proc ims_syn_unlock_object {db object } {

 #simply remove the objects lock
 ns_db dml $db "DELETE FROM GranularLocks WHERE object = [ns_dbquotevalue $object]"

}

Answer 1

It would be usefull to have the deadlock graph.

SQL deadlocks happen not only because of the queries involves, but equaly important is the schema involved. For example you can get a Reader-Writer deadlocks with perfectly valid and 'correct' queries simply because the read and write choose different access paths to the data. I could see this happening in your case if an index on timeActionMade exists on GranularLocks that does not cover 'object' column. But again, the solution will depend on what the actual deadlock is on.

Answer 2

1. Try adding UPDLOCK to the 1st select to force an exclusive lock too

2. Try sp_getapplock which is provided for this kind of operation.

I'd prefer number 2, personally...