Solved

ORA-00060 deadlock error on transaction

Posted on 2006-11-06
7
749 Views
Last Modified: 2013-12-25
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
   
0
Comment
Question by:Jules211
  • 4
  • 3
7 Comments
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 17882577
The #1 cause of deadlocks in Oracle is due to unindexed foriegn keys.

Do u have any?


deadlocks do clear by themselves, so Im wondering if u encounter this problem, do something else, or sleep for a bit and try again, see what happens

0
 

Author Comment

by:Jules211
ID: 17882612
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
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17882671
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

0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:Jules211
ID: 17882853
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
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17898970
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
0
 

Author Comment

by:Jules211
ID: 17898996
I think I figured out a solution- I need to use a SELECT FOR UPDATE instead of the initial update.  Thanks for the help.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17899034
ok, cool
thanks for the grade/pts though :)
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now