ITL Deadlocks (script)

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