Lock Escalation

Lock Escalation

The creation of additional Interested Transaction Lists (ITL) slots is subject to free space in the datablock because each ITL takes approximately 24 bytes of free space in the variable header of that datablock. Initial space reserved by INITRANS cannot be reused for data insertion. But if a datablock is fully packed due to less PCTFREE or PCTFREE=0 and when two transactions are accessing the same block, one has to wait till the transaction commits (or rollbacks). Here row level locks are escalated in to block level locks.

The cost of dynamic creation of transaction slots is trivial and it is better to keep data density higher than compromising data density. The space acquired by dynamically created transaction slots can be reclaimed for future data inserts. Any change in INITRANS will reflect only for newly formatted blocks. So you have to rebuild the table if you want to have more INITRANS for that segment.

ITL slots are acquired for every DML that affects that datablock. An ITL contains the transaction id for that transaction which is the pointer to an entry in the transaction table* of a rollback segment. Another transaction can always read the data from the rollback segment. If new transactions want to update the data it has to wait till the current transaction commits or rollback.

The transaction table is the data structure within the rollback segment, which holds the transaction identifiers of the transactions using that rollback segment. The number of rows in the transaction table is equal to the number of transaction slots in that rollback segment and is visible via the internal view X$KTUXE (available only when logged in as SYS).

While the transaction commits oracle does a fast commit by updating the flag in the transaction table in the rollback segment but the block is not revisited. In this point of time the ITL in the datablock (called open ITLs) is still pointing to the transaction table of the corresponding to the rollback segments. If Oracle crashes before the transaction is committed (or rolled back) the transaction recovery is performed while opening the database next time by the data from the rollback segments

If at the later time another transaction visits the datablock, which has an open ITL, to get a consistent copy (CR) it looks up the transaction table to find it deemed committed. So the transaction revisits the datablock and clears the ITL. This action is called block cleanout. The block clean out is delayed by some discrete time interval because of the fast commit and this is called delayed block cleanout.

This block cleanout can be forced by a simple full table scan after the transaction or setting the parameter ‘delayed_block_cleanout’ to FALSE. Setting this parameter to true some times makes the DBWR overactive. How ever this is no more tunable parameter in current version of Oracle and it defaults to TRUE.

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/46907/viewspace-103775/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/46907/viewspace-103775/