How about:
insert into parent XXX
insert into child YYY
delete from queue_table
update from log_table
commit;
Why is my update to the log table being blocked by an insert into table parent ?
You've only considered the simplest possible case - which is likely to cause
more problems than it solves and increase the level of complaint to Oracle
Support.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Yong Huang [dmarc-noreply@xxxxxxxxxxxxx]
Sent: 09 September 2016 16:32
To: Oracle-l Digest Users
Subject: 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<http://jonathanlewis.wordpress.com/>
@jloracle
