1 2 3 Previous Next 35 Replies Latest reply: Mar 6, 2014 2:20 AM by Nicolas.Gasparotto RSS

    Transaction Locking dilemma

      Hi all,

       

      11.2.0.3

      Aix 6.1

       

      We have been bothered by this transaction locking which has occurred for almost 5 months now. And our interim solution is to restart the app which caused delays to the clients.

      The locking occurs when a batch program deletes yesterday transactions in TAB1, while the app is updating the table. This app  is a 3rd party compiled so we can not check what are the series of sql statements inside it that causes the lock. The app vendor will not accept that their app is causing the lock since it only process the current day transactions. But my check log program showed that it is this app that is causing the lock. But the sql statement being caputured  is just a select statement because the updates maybe be done earlier without commit or maybe some select for update statement.

       

      My boss is already losing its patience why we can not trace the sql that is causing the lock so that we can show the vendor the proof that they need to revise their code. The vendor is hesitant because it might trigger another big error if the revised the program. I even tried to ask them to put commit on all the statements, but seems they do not believe me

       

      In view of the above, can you help me how to present the proof or sql statement the really causing the lock? We have tried it on TEST server but we can not replicate the issue. Maybe becauase the exaction wierd condition is not met? You advise will be highly appreciated.

       

      Please help...

       

      Thanks,

      pK

        • 1. Re: Transaction Locking dilemma
          sb92075

          ALTER SESSION SET SQL_TRACE=TRUE;

           

          above will capture all SQL executed by the batch job!

          • 2. Re: Transaction Locking dilemma

            The batch job is not the problem as we can see the command in clear text because it is a shell script. Its the app that is the problem, becuase it it compiled.

            How will I know which specific sql statement caused the lock.

             

            Thanks

            • 3. Re: Transaction Locking dilemma
              Suntrupth

              You can find out the blocking session and instance:

              select blocking_session , blocking_instance B_Inst from v$session where sid = <SID of your session>;

               

               

              Obtain row lock information with:

              select row_wait_obj#,row_wait_file#,row_wait_block#,row_wait_row# from v$session where sid = <SID of your session>;

               

               

              You can also check the data access waits with :

              select SID, state, event, p1, p2 from v$session where username =<username of the user logged in>

               

               

              If you are aware of the SQL ID , you can also use :

               

               

              alter system set events 'sql_trace [sql: sql_id=<sqlid> ] level 4';

               

              Regards,

              Suntrupth

              • 4. Re: Transaction Locking dilemma
                ABOracle

                pK,

                 

                You may switch on trace for other session using  :

                 

                trace switch on

                exec dbms_system.set_sql_trace_in_session(sid, serial#, TRUE);

                 

                trace switch off

                 

                 

                exec dbms_system.set_sql_trace_in_session(sid, serail#, FALSE);

                 

                sid and serail# are available in v$session view. trace will provide all sql.

                You might upload the awr report during problem time ash and awr report. ash for the session which has locking issue.

                 

                thanks,

                • 5. Re: Transaction Locking dilemma
                  rp0428
                  The locking occurs when a batch program deletes yesterday transactions in TAB1, while the app is updating the table.

                  Well if it 'hurts' when you do that the solution is: DON'T DO THAT!

                   

                  Delete yesterday transactions in TAB1 before you run the app or after you run the app. Then the locking won't occur.

                  • 6. Re: Transaction Locking dilemma

                    I have done all what you have said. If fact I saw that locking in the awr,ash,addm report. But the problem is, the last statement captured is select only. How can a select statement lock a table? That is why the app vendor is not convinced that their app is causing the lock.

                     

                    The app is running 24x7 so we can not do that delete scheduling thing, the delete batch job is being run every midnight. The fact that it happens only once in a while seems a rare bug exemption handling  and tracing  which is  difficult to do. .I just want to show to my boss that we can replicate the locking scenario in our test envi.


                    Thanks

                     

                    • 7. Re: Transaction Locking dilemma
                      sb92075

                      >But my check log program showed that it is this app that is causing the lock.


                      You claim that a problem exists, but have posted NOTHING specific showing any details about this purported problem.

                       

                      post SQL & results that show specifics about the purported "lock".

                       

                      what actually happens when the problem manifests itself?

                      • 8. Re: Transaction Locking dilemma
                        rp0428

                        Well the first thing I would look at would be that batch program:

                        The locking occurs when a batch program deletes yesterday transactions in TAB1, while the app is updating the table.

                        My hypothesis would be that you have the same flaw in your logic that many other such batch applications have. That is, a flaw in the way you determine 'yesterday transactions'.

                         

                        If you are using something like a CREATED_DATE or MODIFIED_DATE to identify those rows then you probably have that same flaw.

                         

                        See my detailed reply dated Aug 1, 2013 2:17 PM in this thread:

                        https://community.oracle.com/thread/2565781?start=0&tstart=0

                        The idea that you can use columns such as CREATED_DATE and MODIFIED_DATE, update them to SYSDATE using a trigger and then reliably extract chronological sets of data based on those columns is a MYTH that has been busted many, many times in this forum and in others. 

                         

                        Except in very limited circumstances that approach can FAIL to extract some data altogether. See my detailed explanation in my reply dated Apr 17, 2013 3:01 PM in this thread from a few months ago.

                        https://forums.oracle.com/message/10970661?tstart=0

                        My later reply in that thread at Aug 5, 2013 7:03 AM has an example that shows the problem.

                         

                        Check that thread and see if your code is doing something similar.

                         

                        • 9. Re: Transaction Locking dilemma

                          The batch is just simple , as in DELETE FROM TABLE1 WHERE TRANDATE = trunc(SYSDATE-1)

                          I will just delete yesterdays trans. I am only concern with the app, on how can I replicate the locking scenario.

                          Or how can I list all statements coming from the app itself. If I run sqltrace it will list all which I do not know if it comes from the app itself.

                           

                          Thanks

                          • 10. Re: Transaction Locking dilemma
                            Alvaro

                            What is the event that the hanging sessions experience while waiting for the "lock"?

                             

                            How did you determine that the blocking session is indeed the batch job?

                             

                            It's simple enough. When the blocking session manifest itself, take note of it's sid and serial#, and check out it's history of SQL_ID's executed. You can do that using ASH (V$ACTIVE_SESSION_HISTORY) reports.

                             

                            You can prove to your boss and also the app vendor that indeed the problem is the batch job by using views such as v$session_blockers, and v$lock.

                             

                            Or, take note of the job name, or module/action of the batch job, and set a trace for it's session as others have suggested as soon as the job starts.

                             

                            And yes, SELECT can lock a row if it has the keyword FOR UPDATE at the end, did you check that ?

                            • 11. Re: Transaction Locking dilemma

                              Thanks AL,

                               

                              I forgot what date did that last happened. Since awr retain 1 week of data, and I am sure it had occured at least once a week. How do I query the awr hist view to select the  locking statement snapshot?

                              • 12. Re: Transaction Locking dilemma
                                rp0428

                                 

                                The batch is just simple , as in DELETE FROM TABLE1 WHERE TRANDATE = SYSDATE-1

                                Baloney! That query isn't going to delete much of anything. The datatype of SYSDATE is DATE and dates include a time component down to the second. You can't delete a full days worth of data by matching a stored date values that have hours, minutes and seconds.

                                 

                                The likely way you are trying to delete the data is the same way those links I referred you to are doing it; with a query like one of these two:

                                DELETE FROM TABLE1 WHERE TRUNC(TRANDATE = TRUNC(SYSDATE - 1) -- won't use a standard index

                                DELETE FROM TABLE1 WHERE TRANDATE BETWEEN TRUNC(SYSDATE - 1) AND (TRUNC(SYSDATE) - 86400)

                                The key question is: how does TRANDATE get populated? If you use SYSDATE in an INSERT statement or trigger then you can have the EXACT issue I was talking about in those threads I gave you the links for.

                                 

                                That SYSDATE represents when you ATTEMPT to do the insert - it does NOT reflect when that insert was actually committed.

                                 

                                So you might have the very problem I tried to warn you about.

                                 

                                If you do your batch process could be trying to delete data that the app is working with. That could mean your batch process is causing the problem.

                                 

                                You need to use FACTS when you troubleshoot and the facts you have posted so far point to your batch process as the cause. Until you rule that out you don't have any reason to put the onus of resolving the issue on that third-party app team.

                                • 13. Re: Transaction Locking dilemma

                                  Thanks Rp, I will analyze it further

                                  I mean The batch is just simple , as in DELETE FROM TABLE1 WHERE TRANDATE = trunc(SYSDATE-1)

                                  Our trandate has no seconds,minute,hour ...it is rounded to day.

                                  • 14. Re: Transaction Locking dilemma
                                    sb92075

                                    f55237a7-2c38-4db3-a7a3-1d77256f0730 wrote:

                                     

                                    Thanks Rp, I will analyze it further

                                    I mean The batch is just simple , as in DELETE FROM TABLE1 WHERE TRANDATE = trunc(SYSDATE-1)

                                    Our trandate has no seconds,minuted,hour or rounded to day.

                                     

                                    If the root cause results from a boundary condition, it would be trivial to correct it by changing the batch job to be as below:

                                     

                                    DELETE FROM TABLE1 WHERE TRANDATE = trunc(SYSDATE-2)

                                    1 2 3 Previous Next