RE: Holder query in lock

Hi Jonathan,
I was thinking of a convenient way to give the users a clue about the initial DML that started a transaction. If the transaction contains e.g.update table1...delete table2...giving the first SQL (update table1) to the users would be helpful in troubleshooting scenarios. A recursive SQL would not be the first and, since we're looking for a lock holder, not necessarily a blocking session, I think it's doable. I mean, even if there's no waiter (therefore no blocker) yet, leaving the SQL ID for the first DML in v$transaction will help the developers. Yong

• Original message --- But a single transaction could be made up of multiple DML statements, and  multiple statements from the same transaction might have modified the same  table, and multiple copies of the same statement might have modified the same  table, and recursive statements (e.g. from "on delete cascade") might be  responsible for the actual block rather than the statement that the client  program ran.

If Oracle were to implement a way to track which SQL_ID was the blocking  statement they would actually need to record FOR EVERY ROW MODIFIED by a  transaction which execution of which statement blocked that row, and which  statement(s) - if any -  was the parent of of the blocking statement.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

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