A question came up on my blog entry http://arup.blogspot.com/2011/01/more-on-interested-transaction-lists.html. I think the question warrants a more comprehensive explanation instead of an answer of a few lines. So I decided to create another blog.

Here was the question:

Could you please explain on the scenario when multiple transactions try to update the same row as well. Will there be any ITL allocated? Yes, I am talking about the real locking scenario.

Paraphrased differently, the reader wants to know what would happen when this series of event happens:

1. update row 1 (locked by transaction 1, and occupying one ITL slot)
2. update row 2 (locked by transaction 2, occupying a different ITL slot)
3. Transaction 3 now wants to update either row 1 or row 2. It will hang of course. But will it trigger the creation of a new ITL slot?

I also decided to expand the questions to cover one more scenario. Transaction 4 wants to update row 1 and row 4 in the same statement. Row 4 is not locked; but row 1 is. So will transaction 4 be allowed to lock row 4, even though the statement itself will hang? Will it trigger the creation of another ITL?

Examination

Let's examine these question via a case study. To demonstrate, let me create a table with three rows:

SQL> create table itltest2 (col1 number, col2 number)
  2  /

Table created.

SQL> insert into itltest2 values (1,1);

1 row created.

SQL> c/1,1/1,2
  1* insert into itltest2 values (1,2)
SQL> /

1 row created.

SQL> c/1,2/2,2
  1* insert into itltest2 values (2,2)
SQL> /

1 row created.

SQL> commit;

Checking the rows:

SQL> select * from itltest2
  2  /

      COL1       COL2
---------- ----------
         1          1
         1          2
         2          2

javascript:void(0)

Now open three sessions and issue different statements

Session1> update itltest2 set col2 = col2 + 1 where col1 = 1;

2 rows updated.

It updated (and locked) 2 rows - row 1 and row 2.

If you check the transaction ID, you will see the transaction details:

SQL> select dbms_transaction.local_transaction_id from dual;

LOCAL_TRANSACTION_ID
--------------------------------------------------------------------------------
7.10.33260

From session2, try to lock row 2 and 3:

Session2> update itltest2 set col1 = col1 + 1 where col2 = 2;

This will hang. The reason is obvious. The transaction is trying to get a lock on rows 2 and 3. Since row 2 is already locked by transaction 1, it can't be locked. However, what about row 3? It should have been able to be locked. Was it locked? Let's make a simple check by updating only row 3 from another session, which was attempted to be locked by transaction 2.

Session3> update itltest2 set col2 = col2 + 1 where col1 = 2 and col2 = 2;

1 row updated.

Checking the transaction ID:

SQL> select dbms_transaction.local_transaction_id from dual;

LOCAL_TRANSACTION_ID
--------------------------------------------------------------------------------
10.4.33214

We know that there are three transactions and three lock requests. Or, are there? Let's check in V$TRANSACTION:

SQL> select XIDUSN, XIDSLOT, XIDSQN
  2  from v$transaction;

    XIDUSN    XIDSLOT     XIDSQN
---------- ---------- ----------
         7         10      33260
        10          4      33214

There are only two transactions that have placed locks. If you combine the XIDUSN, XIDSLOT and XIDSQN, separated by periods, you will get the transaction ID shown earlier. The transaction that is hanging has not placed a lock on the row it could have put a lock on. That is consistent with the concept of statements inside transactions - either all rows will be updated or none - not in piecemeal. If one of the rows can't be locked, none of the rows will be.

What about ITL slots. Let's see them by doing block dumps. First , we need to know the block number these rows are in:

SQL> select dbms_rowid.rowid_block_number(rowid), col1, col2
  2  from itltest2;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)       COL1       COL2
------------------------------------ ---------- ----------
                                4052          1          1
                                4052          1          2
                                4052          2          2

After performing a checkpoint, we will dump the block.

SQL> alter system dump datafile 7 block min 4052 block max 4052;

Looking in the tracefile and searching for "Itl", we see the following two lines:

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.004.000081be  0x00c004fe.1873.23  ----    1  fsc 0x0000.00000000
0x02   0x0007.00a.000081ec  0x00c00350.194c.18  ----    2  fsc 0x0000.00000000

There are just two ITL slots; not three. Remember the XID column is in hexadecimal. If you convert the XID columns in the v$transaction view:

SQL> select
  2     to_char(XIDUSN,'XXXXXX'),
  3     to_char(XIDSLOT,'XXXXXX'),
  4     to_char(XIDSQN,'XXXXXX')
  5  from v$transaction;

TO_CHAR TO_CHAR TO_CHAR
------- ------- -------
      7       A    81EC
      A       4    81BE

Note how the output matches the entry under the column marked "Xid" in the Itl output. you saw the same transaction IDs in the same Itl. There are just two ITL slots and each slot points to a transaction that has placed the lock. The transaction that has not placed the lock is not given an ITL slot; there is no no need for it.

Lock Change

Now suppose Transactios 1 and 3 ended by either commit or rollback. Transaction 2, which was hanging until now, will be free to put the locks. Let's see the ITL slots:

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0008.00f.0000a423  0x00c013ce.1e11.05  C---    0  scn 0x0000.0244bcb2
0x02   0x0006.005.0000a43f  0x00c008fe.1d22.12  ----    2  fsc 0x0000.00000000

If you examine the hexadecimal values of the XID values from V$TRANSACTION,

SQL> select
  2       to_char(XIDUSN,'XXXXXX'),
  3       to_char(XIDSLOT,'XXXXXX'),
  4       to_char(XIDSQN,'XXXXXX')
  5  from v$transaction;

TO_CHAR TO_CHAR TO_CHAR
------- ------- -------
      6       5    A43F

This matches the transaction Id we see in the "Xid" column of the ITL slot. The other ITL slot is now free from any other lock.