RE: How to get the statment sql that caused the lock

“Applications should never become idle while holding locks. Period.”  

While I agree with this, the reality in the field is that a plethora of multiple statement logical units of work that are submitted remotely from the user tier or middle tier are in fact transmitted as if connections never break.

Routinely this means the latency of transmission of the code, parsing, and execution of the 2nd through nth statements of an n component unit of work are potentially increasing the time locks are held far above the time that would occur if all the statements resided on the host before the transaction commenced.  

When a transaction is interrupted by any sort of a network hiccup (most insidiously between a client machine and the middle tier with no loss of connectivity between a middle tier and the database apparent unless a timeout is configured) then the locks on the statements of the transaction thus far in play can be held for a very long time.  

I also agree this should be fixed and not left to cause confusion and delay.  

BUT, it may not be the developer who can control this behavior, and it may have to be escalated to the person or team who can cause a change in the topology of transactions in the infrastructure or architecture.  

There are ways to slice the onion better, either with service deamons or the entire smart database (previously called thick) as per Toons and Bryn, Bryn and Toons.

But I think some developers are caught within their scope of control to be unable to do more than explain the problem to the team who decided on the infrastructure topology.  

As for JL’s depressing recognition that transaction is different from statement so that the trivial single column value identifier won’t lead to the offender in the context of that enhancement request, I believe he is correct and that the problem is exacerbated by the remote submission of statements of a transaction one piece at a time. That would require holding all the sql_ids of a transaction and the statement body of each somewhere. Now that might also be trivial for transactions that are embodied as PL/SQL stored packages so they could at least tell you the chunk of code to look at.  

//soapbox warning:  

If you don’t have all the code and partial success alternatives in hand on the RDBMS server to generate an answer and complete a transaction, then you shouldn’t be starting the transaction.  

This has been clear since at least 1990, early in the implementation of client server enterprise class applications.

It seems it is too difficult a concept to master for flavor of the month UI tool builders. One toolkit that facilitates doing exactly that is Oracle’s PL/SQL. I continue to think they are the best game in town and I don’t miss an opportunity to remind Oracle that would be the best investment to gain market share in the cloud. Just remember the mythical man month and don’t overwhelm Bryn, et. al. as you add resources to the team.  

off soapbox//  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mladen Gogala Sent: Monday, October 29, 2018 9:27 AM
To: Eriovaldo Andrietta; neil_chandler_at_hotmail.com Cc: ORACLE-L
Subject: Re: How to get the statment sql that caused the lock  

But why are you looking for that? If there is an active session blocking your transaction, you'll have to wait until that session ends its transaction. If the blocking session is idle, make note of the application that started the session, terminate the session with prejudice and a strong Austrian accent, and tell the developer who wrote the the application to fix the darned thing. Applications should never become idle while holding locks. Period.

On 10/29/18 7:39 AM, Eriovaldo Andrietta wrote:

My purpose is to retrieve the sql_id related to the transaction line in the view v$transaction that is responsible for the execution of the : INSERT, UPDATE, DELETE ... in order to show it to the developer and validate the application if need to add commit in the code, if does not exists.

I received a message from a member of the group , like this :  

I asked Oracle (SR 3-12200129251 : Request for ID of SQL responsible for transaction to be added to v$transaction) and they created an enhancement request

Bug 24920354 : ADD SQL_ID COLUMN TO V$TRANSACTION OF THE SQL THAT STARTED THE TRANSACTION  

But two years have passed. They did nothing about it.  

This is what I am locking for.  

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217


--
http://www.freelists.org/webpage/oracle-l