CODEWITHSUNDEEP
oracle-databaseoracle11gdatabase-performancedatabase-administration
oramas
oramas

Reputation: 911

What happens if multiple users want to modify the same data block in Oracle Database

How does Oracle database handle that situation, if multiple users try to modify the rows in the same block? I know that they cannot modify the same row. But how does it work for the different rows in the same block? I mean, how does Oracle handle row level lock, while it modifies the whole block when you modify one row?

Upvotes: 1

Views: 832

Answers (1)

sandman
sandman

Reputation: 2108

You should ask this question on dba.stackexchange.com in order to get a better response.

A row-level lock happens when DML (Insert, Update, Delete) or SELECT FOR UPDATE is done. This row-level lock is controlled by the transaction that contains the DML that created the lock. When the transaction commits or rolls back, the lock is released. If another transaction tries to update the same row, it has to wait until the initial transaction commits or rolls back.

The block header contains an ITL (Interested Transaction List) with slots allocated for each transaction that wants to modify the block. The INITRANS table setting is a number that allows for multiple transactions on the table blocks. The default is low and for high concurrency tables, this is set to anything from 10 to 50. If INITRANS is set to 1, then there will be a wait for the next transaction that tries to modify the same block.

So, the whole block is not modified; the row lock is handled within the ITL and the number allocated to the table.

Upvotes: 2

Related Questions