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)
To give you an example of what it looks like when you run it, here is a recording:
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.