Locks are mechanisms that prevent transactions from accessing the same resource concurrently with an intent of modification to the data.

Objects Affected By Locks

• Tables and rows (data in tables).
• data dictionary rows , shared memory structures which are not designated as physical objects.

When do Locks occur:

Any transaction resulting in modifying the data in a table acquires a lock. These are termed as DML locks. Other types of locks also occur when a ddl statement is used.

Oracle Lock duration

All the locks acquired by Statements with in a transaction are held until completion of transaction.

How Oracle Locks are released

When a transaction explicitly commits with a commit statement or the transaction is rolledback with a rollback statement, all the locks held by the transaction are released.

Oracle also releases locks acquired after a savepoint when rolling back to the savepoint.

Oracle Lock Modes

Exclusive Lock

The lock is obtained to modify the data in a table.Any transaction acquiring an exclusive lock would be given permission to modify the data. The first transaction that acquires an exlusive lock is the only eligible transaction to modify the resource until the exclusive lock is released.

Shared Lock

As the name itself indicates that the data can be shared among transactions depending upon the operations involved. Mutiple transactions can read the data by shared locks  preventing a concurrent access by an exclusive lock. More than one shared lock can exist on the same resource.

Oracle Lock types

• DML locks
• DDL locks (dictionary locks)
• Oracle Internal Locks/Latches
• Oracle Distributed Locks

DML LOCKS : DML LOCKS are used to prevent damage to the data in the tables from being modified by transactions acting concurrently.  DML operations can acquire locks at row level or table level.DML locks acquiring locks at row level are called row level locks. Locks acquired at the table level are called table level locks.

Oracle Row Level Locks :

1. For DML statements like insert, update, delete and Select For Update  Oracle automatically quired row Level Locks.

2. Row level locking Provides the best concurreny becausing of  its locking mechanism at row level.  A transaction updating only two rows in table of 10,000 rows only holds the lock for those two rows. Other 9998 rows are available for other transactions.

3. You can have any number of Row level locks held by a transaction. Practically there is no limit on the no of row level locks held by a transaction.

4. A modified row is always locked exclusively so that other users cannot modify the row until the transaction holding the lock is committed or rolled back.

5. If any transaction acquires a row lock, then the  transaction inherently acquires a table lock for the corresponding table. This behaviour is provided by the Oracle database to restrict other conflicting DDL operations like alter table, drop table, truncate table when a transaction has acquired a row lock.

See an Lock example for the above

Session 1:

create table test ( x number(10));

Table created.

Insert into test values(20);

1 row inserted.

But do not enter a commit statement in Session 1 and do not close the session 1. Start a new session

Session 2:

drop table test;

ORA-00054 resource busy and acquire with NO WAIT specified.

Oracle Table Level Locks :

1. Oracle Table level Locks can be acquired by DML statements like INSERT, UPDATE, DELETE , SELECT FOR UPDATE, LOCK TABLE statements.

2. DML operations acquire table level locks in order to Prevent DDL operations on behalf of the transaction.

3. Exclusive DDL locks are prevented if a Oracle table level locks exists on a table.

4. If there is any uncommited data in a table , that table cannot be altered or droped or truncated until all the Oracle table level locks are released on that table.

Oracle Table Level Lock Modes :

• row share (RS),
• row exclusive (RX),
• share (S),
• share row exclusive (SRX),
• exclusive (X).

Below is a table depicting locking mode and other Lock operations permitted when a lock is present.

Details about row share(RS), row Exclusive locks(RX) , share (S), share row exclusive(SRX), exclusive (X) continued here.