Oracle Database - Locks

1 - About

Lock Mechanism in Oracle

Oracle Database provides:

  • consistency. The data a session is viewing or changing must not be changed by other sessions until the user is finished.
  • and integrity. The data and structures must reflect all changes made to them in the correct sequence.

among transactions through a locking mechanisms.

The locks are:

  • performed automatically and requires no user action.
  • directly associated with a session.

Locks are mechanisms that prevent destructive interaction between transactions accessing the same resource. The resources can be either:

  • user objects, such as tables and rows,
  • or system objects not visible to users, such as shared data structures in memory and data dictionary rows.

Oracle Database automatically obtains and manages necessary locks when executing SQL statements, so you need not be concerned with such details. However, the database also lets you lock data manually.

Locks are not the only event where your session can wait (or freeze)

A lock is a mechanism that prevents destructive interactions, which are interactions that incorrectly update data or incorrectly alter underlying data structures, between transactions accessing shared data.

Oracle Database automatically obtains necessary locks when executing SQL statements. For example, before the database permits a session to modify data, the session must first lock the data. The lock gives the session exclusive control over the data so that no other transaction can modify the locked data until the lock is released.

Because the locking mechanisms of Oracle Database are tied closely to transaction control, application designers need only define transactions properly, and Oracle Database automatically manages locking. Users never need to lock any resource explicitly, although Oracle Database also enables users to lock data manually.

3 - Summary of Locking Behavior

The database maintains several different types of locks, depending on the operation that acquired the lock. In general, the database uses two types of locks:

  • exclusive locks (Only one exclusive lock can be obtained on a resource such as a row or a table)
  • and share locks (Many share locks can be obtained on a single resource)

Locks affect the interaction of readers and writers. A reader is a query of a resource, whereas a writer is a statement modifying a resource.

The following rules summarize the locking behaviour of Oracle Database for readers and writers:

  • A row is locked only when modified by a writer. When a statement updates one row, the transaction acquires a lock for this row only. By locking table data at the row level, the database minimizes contention for the same data. Under normal circumstances the database does not escalate a row lock to the block or table level.
  • A writer of a row blocks a concurrent writer of the same row. If one transaction is modifying a row, then a row lock prevents a different transaction from modifying the same row simultaneously.
  • A reader never blocks a writer. Because a reader of a row does not lock it, a writer can modify this row. The only exception is a SELECT ... FOR UPDATE statement, which is a special type of SELECT statement that does lock the row that it is reading.
  • A writer never blocks a reader. When a row is being changed by a writer, the database uses undo data data to provide readers with a consistent view of the row.

Readers of data may have to wait for writers of the same data blocks in very special cases of pending distributed transactions.

4 - Lock Modes

Oracle Database automatically uses the lowest applicable level of restrictiveness to provide the highest degree of data concurrency yet also provide fail-safe data integrity:

  • The less restrictive the level, the more available the data is for access by other users.
  • Conversely, the more restrictive the level, the more limited other transactions are in the types of locks that they can acquire.

Oracle Database uses two modes of locking in a multi-user database:

4.1 - Exclusive

The Exclusive lock mode prevents the associated resource from being shared. A transaction obtains an exclusive lock when it modifies data. The first transaction to lock a resource exclusively is the only transaction that can alter the resource until the exclusive lock is released.

4.2 - Share

The Share lock mode allows the associated resource to be shared, depending on the operations involved. Multiple users reading data can share the data, holding share locks to prevent concurrent access by a writer who needs an exclusive lock. Several transactions can acquire share locks on the same resource.

4.3 - Example

Assume that a transaction uses a SELECT ... FOR UPDATE statement to select a single table row. The transaction acquires:

  • an exclusive row lock. It allows other sessions to modify any rows other than the locked row
  • and a row share table lock. The table lock prevents sessions from altering the structure of the table

Thus, the database permits as many statements as possible to execute.

5 - Conversion and Escalation

5.1 - Conversion

Oracle Database performs lock conversion as necessary. In lock conversion, the database automatically converts a table lock of lower restrictiveness to one of higher restrictiveness.

For example, suppose a transaction issues a SELECT ... FOR UPDATE for an employee and later updates the locked row. In this case, the database automatically converts the row share table lock to a row exclusive table lock. A transaction holds exclusive row locks for all rows inserted, updated, or deleted within the transaction. Because row locks are acquired at the highest degree of restrictiveness, no lock conversion is required or performed.

5.2 - Escalation

Lock conversion is different from lock escalation, which occurs when numerous locks are held at one level of granularity (for example, rows) and a database raises the locks to a higher level of granularity (for example, table).

If a user locks many rows in a table, then some databases automatically escalate the row locks to a single table.

The number of locks decreases, but the restrictiveness of what is locked increases.

Oracle Database never escalates locks. Lock escalation greatly increases the likelihood of deadlocks. Assume that a system is trying to escalate locks on behalf of transaction 1 but cannot because of the locks held by transaction 2. A deadlock is created if transaction 2 also requires lock escalation of the same data before it can proceed.

6 - Automatic Locks

Oracle Database automatically locks a resource on behalf of a transaction to prevent other transactions from doing something that requires exclusive access to the same resource. The database automatically acquires:

  • different types of locks
  • at different levels of restrictiveness

depending on the resource and the operation being performed.

The database never locks rows when performing simple reads.

Oracle Database locks are divided into the following categories.

  • DML Locks. Protect data. For example, table locks lock entire tables, while row locks lock selected rows.
  • DDL Locks. Protect the structure of schema objects—for example, the dictionary definitions of tables and views.
  • System Locks. Protect internal database structures.

7 - Rules to avoid lock

  • run the change late at night when the database is idle.
  • Do all DDL during a maintenance window with all end-users locked-out.
  • Kill the sessions that are preventing the exclusive lock.

8 - Metadata

8.1 - Script

Located in the directory : oracle_home\db_1\RDBMS\ADMIN

  • catblock.sql, creates the lock views that utllockt.sql needs, so you must run it before running utllockt.sql.
  • utllockt.sql script displays, in a tree fashion, the sessions in the system that are waiting for locks and the locks that they are waiting for. The location of this script file is operating system dependent.

8.2 - View and table

The base table for all locks is the V$LOCK view.

Oracle has several views for showing lock status, some of which show the username:

  • DBA_BLOCKERS – Shows non-waiting sessions holding locks being waited-on
  • DBA_DDL_LOCKS – Shows all DDL locks held or being requested
  • DBA_DML_LOCKS - Shows all DML locks held or being requested
  • DBA_LOCK_INTERNAL – Displays 1 row for every lock or latch held or being requested with the username of who is holding the lock
  • DBA_LOCKS - Shows all locks or latches held or being requested
  • DBA_WAITERS - Shows all sessions waiting on, but not holding waited for locks

8.3 - Duration

Oracle Database automatically releases a lock when some event occurs so that the transaction no longer requires the resource.

In most cases, the database holds locks acquired by statements within a transaction for the duration of the transaction.

These locks prevent destructive interference such as:

  • dirty reads,
  • lost updates,
  • and destructive DDL

from concurrent transactions.

A table lock taken on a child table because of an unindexed foreign key is held for the duration of the statement, not the transaction. Also, the DBMS_LOCK package enables user-defined locks to be released and allocated at will and even held over transaction boundaries.

Oracle Database releases all locks acquired by the statements within a transaction when it commits or rolls back. Oracle Database also releases locks acquired after a savepoint when rolling back to the savepoint.

However, only transactions not waiting for the previously locked resources can acquire locks on the now available resources. Waiting transactions continue to wait until after the original transaction commits or rolls back completely

9 - Lock Type

There are dozens of lock types, but the vast majority are system types. System locks are normally only held for a very brief amount of time.

There are only three types of user locks:

  • TX. The TX lock is a row transaction lock; it's acquired once for every transaction that changes data, no matter how many objects you change in that transaction. The ID1 and ID2 columns of V$lock point to the rollback segment and transaction table entries for that transaction.
  • TM. The TM lock is a DML lock. It's acquired once for each object that's being changed. The ID1 column of V$lock identifies the object being modified.
  • and UL. UL is a user-defined lock – a lock defined with the DBMS_LOCK package.

10 - Select

The DBA_LOCK_INTERNAL view used to show locks for a specific user, and you can specify the query in the form:

   NVL(b.username,'SYS') username,
   sys.dba_lock_internal a,
   sys.v_$session b
   WHERE ...

You can also query v$access and v$locked_object to see specific locks:

SELECT s.sid, s.serial#, p.spid
   v$session s,
   v$process p
   s.paddr = p.addr
   s.sid IN (SELECT SESSION_ID FROM v$locked_object);


Show locked objects

SET lines 100 pages 999
col username 	format a20
col sess_id 	format a10
col object	format a25
col mode_held	format a10
SELECT	oracle_username || ' (' || s.osuser || ')' username
,	s.sid || ',' || s.serial# sess_id
,	owner || '.' ||	object_name object
,	object_type
,	DECODE(	l.block
	,	0, 'Not Blocking'
	,	1, 'Blocking'
	,	2, 'Global') status
,	DECODE(v.locked_mode
	,	0, 'None'
	,	1, 'Null'
	,	2, 'Row-S (SS)'
	,	3, 'Row-X (SX)'
	,	4, 'Share'
	,	5, 'S/Row-X (SSX)'
	,	6, 'Exclusive', TO_CHAR(lmode)) mode_held
FROM	v$locked_object v
,	dba_objects d
,	v$lock l
,	v$session s
WHERE 	v.object_id = d.object_id
AND 	v.object_id = l.id1
AND 	v.session_id = s.sid
ORDER BY oracle_username
,	session_id

11 - In Toad

In the menu Database / Monitor / Session Browser

12 - Session Set

10gR2 or above.

Session altered.

13 - Documentation / Reference

db/oracle/lock.txt · Last modified: 2017/10/27 15:54 by gerardnico