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