Oracle Locking and Concurrency

In oracle you will learn that:

Oracle uses a different locking method then most other databases, Oracle locking policy consists of the following:

Pessimistic Locking

When a user queries some data and picks a row to change the below statement is used:

Pessimistic Locking select empno, ename, sal
from emp
where empno = :empno
and ename = :ename
and sal = :sal
for update nowait

what the "for update nowait" statement does is to lock the row against updates by other sessions. This is why this approach is called pessimistic locking. We lock the row before we attempt to update it because we doubt that the row will remain unchanged otherwise. We'll get three outcomes from this statement:

Optimistic Locking

Optimistic locking, is to keep the old and new values in the application and apon updating the data us a update like below:

Optimistic Locking update table
set column1 = :new_column1, column2 = :new_column2, ....
where column1 = :old_column1
and column2 = :old_column2
...

We are optimistically hoping that the data has not changed, if we are lucky the row is updated, if not we update zero rows and now we have two options get the user to re-key the data back in or should be we try and merge the data (lots of code to do this)

So, the best method in Oracle would be to use pessimistic locking as the user can have confidence that the data they are modifying on the screen is currently owned by them - in other words the row is checked out and nobody could modify it. While you may be thinking what if the user walks away the row is locked, in this scenario its would be better to get the application to release the lock or use Resource Profiles in the database to time out idle sessions. Remember that even if a row is locked you can still read that row, it is never blocked for reading in Oracle.

Blocked Inserts

The only time an INSERT will block is when you have a table with a primary key or unique constraint placed on it and two sessions simultaneously attempt to insert a row with the same value, it is most avoided via the use of Oracle sequences in the generation of primary keys as they are highly concurrent method of generating unique keys in a multi-user environment.

Blocked Updates and Deletes

To avoid update and delete blocking use either one of the two locking methods Pessimistic or Optimistic.

Deadlocks

Deadlocks occur when two people hold a resource that the other wants. Oracle records all deadlocks in a trace file. The number one cause of deadlocks is un-indexed foreign keys

Lock Escalation

In other RDBMS when a users locks 100 rows (this may vary) the lock is escalated to a table lock, however Oracle will never escalates a lock, NEVER. Oracle does practice lock conversion/lock promotion they are synonymous.

If a user select a row using FOR UPDATE two locks are placed, one exclusive lock on the row and the other a ROW SHARE LOCK on the table itself. This will prevent other users placing a exclusive lock on the table, thus preventing them from altering the table structure.

Type of Locks

There a number of different types of locks as listed below:

DML Locks

There are two main types of DML locks TX (Transaction) and TM (DL Enqueue). A TX lock is acquired when a transaction initiates its first change and is held until the transaction performs a COMMIT or ROLLBACK. It is used as a queuing mechanism so that other sessions can wait for the transaction to complete. A TM lock is used to ensure that the structure of the table is not altered while you are modifying its contents.

The complete set of DML locks are

Row Share permits concurrent access but prohibits others from locking table for exclusive access
Row Exclusive

same as row share but also prohibits locking in share mode

Share

permits concurrent queries but prohibits updates to the table

Share Row Exclusive

prevent others from locking in share mode or updating the rows on the whole table

Exclusive permits queries but no DML against the table but select ok

The type of locked used when using DML are

RS (table) and RX (row) select  ... for update;
lock table ... in row share mode
RX (table) and RX (row) any insert, update or delete

Below are tables that can be used to identify locks, transaction ID, etc, the code can be used to obtain this information.

Useful SQL
Identify locks and Transaction ID's select username,
v$lock.sid,
trunc(id1/power(2,16)) rbs,
bitand(id1, to_number('ffff', 'xxxx'))+0 slot,
id2 seq,
lmode,
request
from v$lock, v$session
where v$lock.type = 'TX'
and v$lock.sid = v$session.sid
and v$session.username = USER;
Identify who is blocking whom select (select username from v$session where sid=a.sid) blocker,
a.sid,
' is blocking ',
(select username from v$session where sid=b.sid) blockee,
b.sid
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;
Using NOWAIT

select * from employee for update nowait;
select * from employee for update wait 10;

Note: the above commands will abort if the lock is not release in the specified time period.

Useful Views
V$TRANSACTION

lists the active transactions in the system

V$SESSION lists session information for each current session.
V$LOCK lists the locks currently held by the Oracle Database and outstanding requests for a lock or latch.
V$LOCKED_OBJECT lists all locks acquired by every transaction on the system. It shows which sessions are holding DML locks (that is, TM-type enqueues) on what objects and in what mode.
DBA_LOCK lists all locks or latches held in the database, and all outstanding requests for a lock or latch
DBA_BLOCKERS displays a session if it is not waiting for a locked object but is holding a lock on an object for which another session is waiting
DBA_DDL_LOCKS lists all DDL locks held in the database and all outstanding requests for a DDL lock
DBA_DML_LOCKS lists all DML locks held in the database and all outstanding requests for a DML lock.

DDL Locks

DDL locks are automatically placed against objects during a DDL operation to protect them from changes by other sessions.

There are three types of DDL locks

Identify Locks select * from dba_ddl_locks;

Latches and Internal Locks (Enqueues)

Latches are locks that are held for short period of time, for example the time it takes to modify an in-memory data structure. They are used to protect certain memory structures such as the database block buffer cache or the library cache in the shared pool. They are lightweight low-level serialization mechanism to protect the in-memory data structures of the SGA. They do not support queuing and do not protect database objects such as tables or data files.

Enqueues are another more sophisticated serialized device used when update rows in a database table. The requestor will queue up and wait for the resource to become available, hence these are not as fast as a latch.

It is possible to use manual locking using the FOR UPDATE statement or LOCK TABLE statement, or you can create your own locks by using the DBMS_LOCK package.

Deadlocks

Occurs when two sessions block each other while each waits for a resource that the other session is holding.

Multi-versioning

Oracle operates a multi-version read-consistent concurrency model. Oracle provides:

When Oracle reads a table its uses the rollback segment of any rows that data has changed since when it started the read. This allows a point in time read of a table. This also allows Oracle not to lock a table while reading large tables.

Transaction and Row Locks

I am now going to describe in detail how a lock works, you don't need this details but its good to understand what is going on under the covers. Row level locks protect selected rows in a data block during a transaction, a transaction acquires a enqueue and an exclusive lock for each individual row modified by one of the following

These locks are stored within the data block and each lock refers to the transaction enqueue and as they are stored in the blocck they have a database wide view. The lock is held until either a commit or a rollback is executed, SMON also acquires it in exclusive mode when recovering (undo-ing) a transaction. Transaction locks are used as a queuing mechanism for processes awaiting release of an object locked by a transaction process.

Every data block (except for temp and rollback segements) will have a number of predefined transaction slots. Undo segments have a different type of transaction slot called transaction tables, the transaction slots are otherwise know as interested transaction lists (ITLs) and are controlled by the INITRANS parameter (default is 2 for tables and 3 for indexes). A transaction slots uses 24 bytes of free space in the block, and the maximum number of transactions slots is controlled by the MAXTRANS parameter, hwoever you can only use upto 50% of the block for transactions slots.

ITL slots are required for every transaction, it contains the transaction ID (XID) which is a pointer to an entry in the transaction table of a rollback segment. You can still read the data but other processes wanting to change the data must wait until the lock is released (commit or rollback). The ITL entry contains a XID, undo byes address (UBA) information, flags indicating the transaction status (Flag) and lock count (Lck) showing the number of rows locked by this transaction within the block and SCN at which the transaction is updated. Basically the XID identifies the undo information about that transaction.

You can use the view X$KTUXE to obtain information on the number of rows that are affected, also the view V$TRANSACTION can be used to get more details on a transaction.

When the transaction completed Oracle performs the bare minimum to commit the transaction, it involves updating the flag in the transaction table and the block is not revisisted. This is know as a fast commit, during this time the ITL in the data block is still pointing to the transaction table of the corresponding rollback segment. If an transaction wants to use the block (persume the change has not been comiited) it see's that its point to a rollback segement makes a copy of the block in memory, gets the UBA from the ITL, reads the data from the undo and uses it to rollback the change defined by the undo. If the transaction is committed the rows are no longer locked but the lock byte is the row header is not cleared until the next time a DML action is performed on the block. The block cleanout is delayed by some discrete time interval because of the fast commit, this is called delay block cleanout. The cleanout operation closes the open ITLs and generates the redo information as a block cleanout many involve updating the block with a new SCN, this is why you see redo generation for some select statements.