asked on

ORA-00060 deadlock error on transaction

Hi,

I have a dll that is registered on multiple workstations and is attempting to execute a transaction against an Oracle database.

The workstations are getting the error: ORA-00060: deadlock detected while waiting for resource

Here is the code:

'Start transaction
g_cnnAdoVfconn.BeginTrans

p_strSQL = "Update " & g_strPrjId & "_BATCHPARCEL SET QP_QUEUED = NULL " & _
"WHERE (" & g_strPrjId & "_BATCHPARCEL.NODE_ID = " & p_intNodeId & ") AND " & _
"(" & g_strPrjId & "_BATCHPARCEL.PARCEL_USER_ID = '" & p_strComUser & "')"

g_cnnAdoVfconn.Execute p_strSQL

p_strSQL = "select " & g_strPrjId & "_BATCHPARCEL.PARCEL_ID " & _
"FROM " & g_strPrjId & "_BATCHPARCEL, " & _
"" & g_strPrjId & "_DOCUMENT " & _
"WHERE (" & g_strPrjId & "_BATCHPARCEL.NODE_ID = " & p_intNodeId & ") AND " & _
"(" & g_strPrjId & "_BATCHPARCEL.PARCEL_USER_ID = '" & p_strComUser & "') AND " & _
"(" & g_strPrjId & "_BATCHPARCEL.PARCEL_ID = " & _
g_strPrjId & "_DOCUMENT.PARCEL_ID) AND (BAT_PRIORITY = 'High') " & _
"ORDER BY " & g_strPrjId & "_BATCHPARCEL.PARCEL_SUBJECT, PARCEL_REC_DATE"
p_rstUser.Open p_strSQL, g_cnnAdoVfconn

If p_rstUser.EOF = True Then
If p_rstUser.State = adStateOpen Then p_rstUser.Close

p_strSQL = "select " & g_strPrjId & "_BATCHPARCEL.PARCEL_ID " & _
"FROM " & g_strPrjId & "_BATCHPARCEL, " & _
"" & g_strPrjId & "_DOCUMENT " & _
"WHERE (" & g_strPrjId & "_BATCHPARCEL.NODE_ID = " & p_intNodeId & ") AND " & _
"(" & g_strPrjId & "_BATCHPARCEL.PARCEL_USER_ID = '" & p_strComUser & "') AND " & _
"(" & g_strPrjId & "_BATCHPARCEL.PARCEL_ID = " & _
g_strPrjId & "_DOCUMENT.PARCEL_ID) AND (BAT_PRIORITY = 'Normal') " & _
"ORDER BY " & g_strPrjId & "_BATCHPARCEL.PARCEL_SUBJECT, PARCEL_REC_DATE"

p_rstUser.Open p_strSQL, g_cnnAdoVfconn

If p_rstUser.EOF = True Then
If p_rstUser.State = adStateOpen Then p_rstUser.Close

p_strSQL = "select " & g_strPrjId & "_BATCHPARCEL.PARCEL_ID " & _
"FROM " & g_strPrjId & "_BATCHPARCEL, " & _
"" & g_strPrjId & "_DOCUMENT " & _
"WHERE (" & g_strPrjId & "_BATCHPARCEL.NODE_ID = " & p_intNodeId & ") AND " & _
"(" & g_strPrjId & "_BATCHPARCEL.PARCEL_USER_ID = '" & p_strComUser & "') AND " & _
"(" & g_strPrjId & "_BATCHPARCEL.PARCEL_ID = " & _
g_strPrjId & "_DOCUMENT.PARCEL_ID) AND (BAT_PRIORITY = 'Low') " & _
"ORDER BY " & g_strPrjId & "_BATCHPARCEL.PARCEL_SUBJECT, PARCEL_REC_DATE"

p_rstUser.Open p_strSQL, g_cnnAdoVfconn

If p_rstUser.EOF = True Then

MsgBox "There are no more jobs available in queue " & p_strComUser, _
vbOKOnly + vbInformation + vbSystemModal, _
"Warning"

g_cnnAdoVfconn.RollbackTrans

GetFromUser = False 'return
Else
p_lngParcel = p_rstUser!PARCEL_ID

p_rstUser.Close

p_strSQL = "Update " & g_strPrjId & "_DOCUMENT " & _
"SET NODE_ID = " & p_intNodeId & ", " & _
"USER_ID = '" & p_strToUser & "' " & _
"Where (PARCEL_ID = " & p_lngParcel & ")"
g_cnnAdoVfconn.Execute p_strSQL

p_strSQL = "Update " & g_strPrjId & "_BATCHPARCEL " & _
"SET NODE_ID = " & p_intNodeId & ", " & _
"NODE_TITLE_FROM = 'INT', " & _
"USER_ID_FROM = '" & p_strComUser & "', " & _
"PARCEL_USER_ID = '" & p_strToUser & "' " & _
"Where (PARCEL_ID = " & p_lngParcel & ")"
g_cnnAdoVfconn.Execute p_strSQL

g_cnnAdoVfconn.CommitTrans

GetFromUser = True
End If
Else
p_lngParcel = p_rstUser!PARCEL_ID

p_rstUser.Close

'Start transaction
'g_cnnAdoVfconn.BeginTrans

'Update Manual Node & User in Document table
p_strSQL = "Update " & g_strPrjId & "_DOCUMENT " & _
"SET NODE_ID = " & p_intNodeId & ", " & _
"USER_ID = '" & p_strToUser & "' " & _
"Where (PARCEL_ID = " & p_lngParcel & ")"
g_cnnAdoVfconn.Execute p_strSQL

'Update Manual Node & User in BatchParcel table
p_strSQL = "Update " & g_strPrjId & "_BATCHPARCEL " & _
"SET NODE_ID = " & p_intNodeId & ", " & _
"NODE_TITLE_FROM = 'INT', " & _
"USER_ID_FROM = '" & p_strComUser & "', " & _
"PARCEL_USER_ID = '" & p_strToUser & "' " & _
"Where (PARCEL_ID = " & p_lngParcel & ")"
g_cnnAdoVfconn.Execute p_strSQL

g_cnnAdoVfconn.CommitTrans

GetFromUser = True 'return

End If

Else
p_lngParcel = p_rstUser!PARCEL_ID

p_rstUser.Close

p_strSQL = "Update " & g_strPrjId & "_DOCUMENT " & _
"SET NODE_ID = " & p_intNodeId & ", " & _
"USER_ID = '" & p_strToUser & "' " & _
"Where (PARCEL_ID = " & p_lngParcel & ")"
g_cnnAdoVfconn.Execute p_strSQL

p_strSQL = "Update " & g_strPrjId & "_BATCHPARCEL " & _
"SET NODE_ID = " & p_intNodeId & ", " & _
"NODE_TITLE_FROM = 'INT', " & _
"USER_ID_FROM = '" & p_strComUser & "', " & _
"PARCEL_USER_ID = '" & p_strToUser & "' " & _
"Where (PARCEL_ID = " & p_lngParcel & ")"
g_cnnAdoVfconn.Execute p_strSQL

'commit transaction
g_cnnAdoVfconn.CommitTrans

GetFromUser = True 'return
End If

ASKER CERTIFIED SOLUTION

rockiroads

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jules211

ASKER

I'm not aware of any unindexed foreign keys... the database doesn't actually use database primary keys so I'm not sure if that applies. ?

So where would I try to sleep? Just trap that error and if it occurs, restart the function? Would I have to rollback when I encounter that error or does Oracle handle that automatically?

Thanks,
Julie

rockiroads

Well u are using transactional processing (BeginTrans, CommitTrans)
so u could always issue a rollback

Add a error handler in here

This error handler checks the error returned (is Err.Number set ?)

what u can then do is,
if deadlock error, sleep, say for 2 secs or so (u may need to fine tune this sleep) and retry - resume 0 or something like that

or any other error, issue a rollback - does g_cnnAdoVfconn.RollBack do it?
then exit procedure

Perhaps u need to add a count on the number of times deadlock error occurs, if 3 times, for example, issue rollback and exit

Jules211

ASKER

Can you tell me anything about the nature of why this would happen in Oracle? I can try the sleep suggestion but I'd like to be able to understand the reason this is happening. From what I gather Oracle does not actually lock records during a transaction, but uses a "buffer" area so to speak for a copy of the records as they are being edited, and when a commit is issued the original records are updated. Meanwhile other users should be able to select and update the original rows, but the commit will replace any updates made during that time. So how does a deadlock come into play?

Thanks!
Julie

rockiroads

Howdo

Not 100% sure, Im a bit limited when it comes to Oracle stuff. I know PL/SQL, triggers etc, not so hot on these aspects.
Do u think its worth u posting a 20ptr question in the Oracle arena with a link to this question

Jules211

ASKER

I think I figured out a solution- I need to use a SELECT FOR UPDATE instead of the initial update. Thanks for the help.

rockiroads

ok, cool
thanks for the grade/pts though :)