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
@jloracle
