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.