Thursday, February 03, 2011

A question came up on my blog entry 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?


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


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;


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;


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

  2  from v$transaction;

---------- ---------- ----------
         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;

------------------------------------ ---------- ----------
                                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;

------- ------- -------
      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;

------- ------- -------
      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.


അരവിന്ദ് said...

Hi Arup,

Thanks for the detailed explanation. looking forward for more myth busters from you.


Recovery Software said...

I like what you guys are up too. Such clever work and reporting! Carry on the great works guys I have incorporated you guys to my blogroll.
data recovery atlanta

This comment has been removed by the author.

Thanks for such a wonderful post.
You are awesome..


Hi Arup,

Can you please explain the below question i have searched in google but couldnt get the answer.

1)Can you please explain how the commit maker which oracle write in redo stream when transaction committed help in roll forward recovery.

2)After db gets open Oracle now wants to roll back the uncommitted transaction that happen before db was abort but how oracle or smon determine
which block it need to rollback after db is open.

3)Suppose USER A running transaction for 7 mins and after 5 mins checkpoint happen in database so all the dirty buffer will be flush down to datafile
by dbwr and before the dbwr writes lgwr will write the redo buffer of this block to redo file after 7 mins USER A commit the transaction oracle will
write the commit marker in redo stream and update the undo header slot that transaction is committed. Now this undo header slot are free to use by other transaction
as previous transaction is committed. Now USER B had overwrite some of the undo entry of USER A transaction. Now in between USER C process want
to access all block modified by USER A so it will read the blocks from datafile and when it will check the block it will see some active ITL entry in it.
as when the block written to datafile transaction was active. Now from the ITL entry it will try to access the undo header slot to determine if transaction
committed or not but as the entry is overwritten by USER B what will happen to this block.

Please help me in understanding above question.