A reader of this blog, VijayS, asked me to share the script I use to demo ITL deadlocks that I mentioned in this comment. Since other readers might be interested, here is the script.
SET TERMOUT ON FEEDBACK ON VERIFY OFF SCAN ON ECHO ON @connect SELECT * FROM v$version WHERE rownum = 1; REM REM cleanup REM DROP TABLE t PURGE; PAUSE REM REM create a test table with three completely full data blocks REM (default tablespace is expected to have 8KB blocks) REM CREATE TABLE t (n NUMBER, c VARCHAR2(4000)) PCTFREE 0; INSERT INTO t SELECT rownum, rpad('*',2679,'*') FROM dual CONNECT BY level <= 9; COMMIT; SELECT dbms_rowid.rowid_block_number(rowid), n FROM t ORDER BY n; PAUSE REM REM in this session modify two rows: one in the first block and one in the REM third block REM SET SQLPROMPT " 1 > " SELECT sid FROM v$session WHERE audsid = userenv('sessionid'); UPDATE t SET n=n*10 WHERE n IN (1,7); PAUSE REM REM in another session (called session #2) modify two rows: one in the first REM block and one in the second block REM REM @connect REM SET SQLPROMPT " 2 > " REM REM SELECT sid FROM v$session WHERE audsid = userenv('sessionid'); REM UPDATE t SET n=n*10 WHERE n IN (2,5); REM PAUSE REM REM in another session (called session #3) try to modify one row per block; REM since all ITL slots of the first block are taken, wait... REM REM @connect REM SET SQLPROMPT " 3 > " REM REM SELECT sid FROM v$session WHERE audsid = userenv('sessionid'); REM UPDATE t SET n=n*10 WHERE n = 9; REM UPDATE t SET n=n*10 WHERE n = 6; REM UPDATE t SET n=n*10 WHERE n = 3; REM PAUSE REM REM in this session try to modify one row in the second block and, in addition, REM in session #2, try to modify one row in the third block; since all ITL slots REM of the second and third block are taken, wait..., deadlock... REM REM run "UPDATE t SET n=n*10 WHERE n = 8;" in session #2 UPDATE t SET n=n*10 WHERE n = 4;
Notice that:
- the remarks contained in the script provide important information for understanding what the script does and, as a result, for being able to produce a ITL deadlock
- to run the script three sessions are required (when running the script I copy/paste the commands from the output of the terminal running the script to the other two terminals)
The deadlock graph generated by the run that I recorded is the following one. Notice that the rows waited on are set to “no row”.
---------Blocker(s)-------- ---------Waiter(s)--------- Resource Name process session holds waits process session holds waits TX-00090013-00001b44 25 140 X 23 147 S TX-0007001d-000019a9 23 147 X 25 140 S session 140: DID 0001-0019-00000D23 session 147: DID 0001-0017-000004BC session 147: DID 0001-0017-000004BC session 140: DID 0001-0019-00000D23 Rows waited on: Session 140: no row Session 147: no row
[…] Chris Antognini has shared a script that is used to demo ITL deadlocks. […]
Thanks Christian, I can finally reproduce the “enq: TX – allocate ITL entry” wait. I am facing this same issue at my client and now I can reproduce it for them and eventually solve the issue. Many thanks!
See you at the Hotsos Symposium.
Thanks,
Tino.
Hello Christian
Reading this article I wondered how it comes that your deadlock graph shows two sessions only, while the test case invoves three sessions. Also, as table blocks always have at least 2 ITL entries (even when INITRANS is set to 1), I cannot really think of an ITL-deadlock scenario where only 2 sessions would be involved.
I played a bit around with your test case and I can produce situations where three sessions appear in the trace file as well as situations where only two sessions appear in the trace file. The latter can happen when I omit the last step, i.e. “UPDATE t SET n=n*10 WHERE n = 4;” in ‘this’ session / session 1. In this case however, although Oracle RDBMS reports an ORA-00060, I believe there is no real deadlock situation in that session 1 (‘this’ session) is not blocked and once session 1 terminates the transaction, the other two sessions 2 and 3 would be unblocked as well. Is it possible that Oracle RDBMS reports a deadlock for a mere blocking scenario, i.e. without having a “real” deadlock and if so could this be considered a bug?
thank you
kind regards
Martin
Hi Martin
Yes, since the number of ITL is at least 2, I also do not see how you can get a deadlock with 2 sessions.
Whether 2 or 3 sessions are “involved” in the deadlock probably depends on the way the graph is traversed. In fact, each node has two edges… depending how the algorithm choose which one to follow, a different path might be discovered.
Also in case you don’t execute the last statement, session #1 and session #3 are waiting for each other. Isn’t that a deadlock?
Best,
Chris
Hi Christian,
If we omit the last statement it is sessions #2 and #3 waiting for each other?
By “it is not a real deadlock” I mean that both these two sessions could also use the ITL slot blocked by session #1 (which is itself not blocked). Thus sessions #2 and #3 might progress once session #1 terminates its transaction (and doesn’t block ITL slots any more) – they are not indefinitely blocked until one session is forced to step back by a ORA-00060.
This is reflected in the deadlock graph which is displayed in the trace file. When I execute your full test case (including the last step) I get a deadlock graph including three sessions:
————Blocker(s)———– ————Waiter(s)————
Resource Name process session holds waits serial process session holds waits serial
TX-0055000A-000085A8-00000000-00000000 89 911 X 9189 102 18 S 48727
TX-00630018-00007185-00000000-00000000 102 18 X 48727 124 742 S 65486
TX-004F001F-00007C50-00000000-00000000 124 742 X 65486 89 911 S 9189
while when I omit the third step I may get a deadlock graph including two sessions (I believe it depends on the timing):
————Blocker(s)———– ————Waiter(s)————
Resource Name process session holds waits serial process session holds waits serial
TX-0057000F-00003849-00000000-00000000 85 195 X 42254 102 14 S 17598
TX-00470021-0000463F-00000000-00000000 102 14 X 17598 85 195 S 42254
In the first scenario (full test case) I see what I would call a “real deadlock” in that the sessions won’t make any progress unless one of them is forced (by ORA-00060) to release the ressources it locks. In the second scenario however the blocking is not final in my opinion but would resolve once session 1 terminates its transaction.
kind regards
Martin
Hi Martin
> If we omit the last statement it is sessions #2 and #3 waiting for each other?
Two things…
1) there is a “typo”, it should be #1 and #3; or better, #3 is waiting, round-robin, for either #1 and #2, and #1 is waiting, round-robin, for either #2 and #3
2) I did not read/understand correctly what you omitted; when I read “the last step” I though you were referring to the last statement being executed, i.e. the update of the row with n=8
> I mean that both these two sessions could also use the ITL slot blocked by session #1 (which is itself not blocked)
I don’t get this point. All ITL are used:
– the two of the first block by the sessions #1 and #2
– the two of the second block by the sessions #2 and #3
– the two of the third block by the sessions #1 and #3
Best,
Chris
Hello Christian,
I did omit the following statement “UPDATE t SET n=n*10 WHERE n = 4” which is executed in “this session” (session #1). This is why I think that session #1 should not be blocked (but session #2 and #3 are).
I executed
first in session #2:
UPDATE t SET n=n*10 WHERE n = 8;
after that in session #1 (and I omitted this step in some executions):
UPDATE t SET n=n*10 WHERE n = 4;
– this is why I think that the way I execute it the session #1 is not blocked if I omit the last statement.
Reading your reply however (and looking again at the test case), I wonder if I maybe misunderstood the order of the SQL exection in your test case and if the intended order of execution isn’t:
first in session #1:
UPDATE t SET n=n*10 WHERE n = 4;
after that in session #2:
UPDATE t SET n=n*10 WHERE n = 8;
thank you
kind regards
Martin