O'Reilly logo

Oracle Internals: An Introduction by Steve Adams

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Buffer Locks

A form of enqueue locking is used to protect cached database blocks. For each buffer in the database buffer cache, there is a buffer header. The buffer headers constitute a fixed array in the permanent memory part of the shared pool. These buffer headers act as the resource structures for buffer locks. Sessions manipulate buffer headers, and thus buffers, via dynamically allocated structures known as buffer handles. The buffer handles act as the lock structures for buffer locks.

Buffer locks are taken only in shared and exclusive modes.[3] The buffer headers implement a two-way linked list of the buffer handles for sessions that are using the buffer, and another for the buffer handles of sessions waiting for the buffer. Sessions waiting for a buffer lock report either buffer busy waits, or buffer busy due to global cache waits, or write complete waits. The parameters for buffer busy waits are shown in Table 4.6.

Table 4-6. Wait Parameters (buffer busy waits)

Parameter

Description

p1

The file number of the database block.

p2

The block number of the database block in its file.

p3

The reason for the wait. A or 1014 indicates that the buffer is locked exclusively by a session that is busy reading a block from disk into the buffer, and that the read has not yet completed. A reason of is used for consistent gets, whereas 1014 is used for current mode block gets. Any other number indicates that the buffer is locked exclusively for modification by another session.

The timeout for buffer busy waits backs off from 1 to 3 seconds. If a buffer lock for a block that is in cache cannot be acquired within a certain number of timeouts, and if the session is holding buffer locks on one or more other buffers, then a buffer lock deadlock is assumed. The number of timeouts to wait before a buffer lock deadlock is assumed is dependent on the operation being attempted, and whether it is part of a discrete transaction. Because discrete transactions do not hold transaction locks, and thus row-level locks, they must acquire all the buffer locks they need before any modifications can be made, and hold them all until the transaction is ready to make its changes and commit. This means that discrete transactions hold more buffer locks than normal transactions, and hold them for much longer.

If a buffer lock deadlock is suspected, the session that timed out trying to acquire a buffer lock releases the buffer locks that it is holding on other buffers, and immediately enqueues them again, thereby falling to the end of the queue of waiting sessions. It also posts the first process that was waiting for a lock on each of the buffers concerned, and then yields the CPU. Although yielding the CPU does not really constitute a wait, a buffer deadlock wait is recorded and the exchange deadlocks statistic is incremented. Assumed buffer lock deadlocks signal event 370, which can be caught to investigate such problems.

In parallel server databases, buffers can be locked for global cache operations such as writes in response to ping requests, and consistent reads for direct memory transfers by the block server process. If a request for a buffer lock cannot proceed because the buffer is locked for a global cache operation, then a buffer busy due to global cache wait is recorded.

Similarly, when buffer lock requests cannot proceed because the buffers are locked by DBWn as part of a batch of blocks to be written, then write complete waits are recorded . The timeout for these waits is 1 second, and the parameters are as shown in Table 4.7.

Table 4-7. Wait Parameters (write complete waits)

Parameter

Description

p1

The file number of the database block.

p2

The block number of the database block in its file.

p3

The reason for the wait. The normal reason code is 1029; however, other values are seen at times.



[3] This is a simplification, but adequate for our purpose here.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required