“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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Mladen Gogala
Sent: Monday, October 29, 2018 9:27 AM
To: Eriovaldo Andrietta; neil_chandler@xxxxxxxxxxx
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
