Oracle Locking & Concurrency

In oracle you will learn that:

Transactions are what databases are all about; they are good
You should defer committing as long as you have to. You should not do it quickly to avoid stressing the system, it does not stress the system to have long or large transactions. The rule is commit when you must, and not before. Things should only be small or large as your business logic dictates
You should hold locks on data as long as you need to. There are tools you to use, not things to be avoided. Locks are not a scarce resource.
There is no overhead involved with row-level locking in Oracle, none
You should never escalate a lock(for example, use a table lock instead of a row lock) because it would be 'better on the system'. In Oracle it won't be better for the system - it will save no resources
Concurrency and consistency can be achieved. You can get it fast and correct, every time.

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

Oracle locks data at the row level on modification only. There is no lock escalation to a block or table level, ever.
Oracle never locks data just to read it. There are no locks placed on rows of data by simple reads.
A writer of data does not block a reader of data.
A writer of data is blocked only when another writer of data has already locked the row it was going after.

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:

if the underlying data has not changed, we get our row back and this row will be locked from updates by others (but not reads).
if another user is in the process of modifying that row, we will get a ORA-00054 Resource Busy error. We are blocked and must wait for the other user to finish with it.
if, in the time between selecting the data and indicating our intention to update, someone has already changed the row then we will get zero rows back. The data will be stale. The application needs to re-query the data and lock it before allowing the end user to modify any of the data in order to avoid a lost update scenario.

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

if you update the parent table's primary key the child table will be locked in the absence of an index
if I delete a parent table, the entire child table will be locked, again in the absence of an index.

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 - DML (data manipulation language), in general SELECT, INSERT, UPDATE and DELETE. DML locks will be locks on a specific row of data, or a lock at the table level, which locks every row in the table.
DDL locks - DDL (data definition language), in general CREATE, ALTER and so on. DDL locks protect the definition of the structure of objects.
Internal locks and latches - These are locks that Oracle uses to protect its internal data structure.
Distributed Locks - These are used by OPS to ensure that different nodes are consistent with each other.
Deadlocks - Occurs when two sessions block each other while each waits for a resource that the other session is holding.
PCM - PCM (Parallel Cache Management) These are locks that protect one or more cached data blocks in the buffer cache across multiple instances, also used in OPS.

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

Exclusive DDL Locks - these prevent other sessions from gaining a DDL lock or TM lock themselves. You can query a table but not modify it. Exclusive locks will normally lock the object until the statement has finished. However in some instances you can use the ONLINE option which only uses a low-level lock this still locks DDL operations but allows DML to occur normally.

Share DDL Locks - This protect the structure of the referenced object against modification by other sessions, but allows modification to the data. Shared DDL locks allow you to modify the contents of a table but not their structure.

Breakable Parse Locks - This allows an object, such as a query plan cached in the shared pool to register its reliance on some objects. If you perform a DDL against that object, Oracle will review the list of objects that have registered their dependence, and invalidate them. Hence these locks are breakable, they do not prevent the DDL from occurring. Breakable parse locks are used when a session parses a statement, a parse lock is taken against every object referenced by that statement. These locks are taken in order to allow the parsed, cached statement to be invalidated (flushed) in the shared pool if a reference object is dropped or alter in some way. Use the below SQL to identify any parse locks on views, procedures,grants, etc

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.

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:

Read-consistent queries: Queries that produce consistent results with respect to a point in time by using rollback segments.

Non-Blocking queries: Queries are never blocked by writers of data, as they would be in other databases.

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.