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