Oracle Lock

 

http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96524/c21cnsis.htm#2937

 

 

Types of Locks

Oracle automatically uses different types of locks to control concurrent access to data and to prevent destructive interaction between users. Oracle automatically locks a resource on behalf of a transaction to prevent other transactions from doing something also requiring exclusive access to the same resource. The lock is released automatically when some event occurs so that the transaction no longer requires the resource.

Throughout its operation, Oracle automatically acquires different types of locks at different levels of restrictiveness depending on the resource being locked and the operation being performed.

Oracle locks fall into one of three general categories.

Lock	Description
DML locks (data locks)	DML locks protect data. For example, table locks lock entire tables, row locks lock selected rows.
DDL locks (dictionary locks)	DDL locks protect the structure of schema objects--for example, the definitions of tables and views.
Internal locks and latches	Internal locks and latches protect internal database structures such as datafiles. Internal locks and latches are entirely automatic.

The following sections discuss DML locks, DDL locks, and internal locks.

 

DML Locks

The purpose of a DML (data) lock is to guarantee the integrity of data being accessed concurrently by multiple users. DML locks prevent destructive interference of simultaneous conflicting DML or DDL operations. For example, Oracle DML locks guarantee that a specific row in a table can be updated by only one transaction at a time and that a table cannot be dropped if an uncommitted transaction contains an insert into the table.

DML operations can acquire data locks at two different levels: for specific rows and for entire tables.

Row Locks (TX)

The only DML locks Oracle acquires automatically are row-level locks. There is no limit to the number of row locks held by a statement or transaction, and Oracle does not escalate locks from the row level to a coarser granularity. Row locking provides the finest grain locking possible and so provides the best possible concurrency and throughput.

The combination of multiversion concurrency control and row-level locking means that users contend for data only when accessing the same rows, specifically:

• Readers of data do not wait for writers of the same data rows.
• Writers of data do not wait for readers of the same data rows unless SELECT ... FOR UPDATE is used, which specifically requests a lock for the reader.
• Writers only wait for other writers if they attempt to update the same rows at the same time.

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

A transaction acquires an exclusive DML lock(TM Lock) for each individual row modified by one of the following statements: INSERT, UPDATE, DELETE, and SELECT with the FOR UPDATE clause.

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. However, if the transaction dies due to instance failure, block-level recovery makes a row available before the entire transaction is recovered. Row locks are always acquired automatically by Oracle as a result of the statements listed previously.

If a transaction obtains a row lock for a row, the transaction also acquires a table lock for the corresponding table. The table lock prevents conflicting DDL operations that would override data changes in a current transaction.

Table Locks (TM)

A transaction acquires a table lock when a table is modified in the following DML statements: INSERT, UPDATE, DELETE, SELECT with the FOR UPDATE clause, and LOCK TABLE. These DML operations require table locks for two purposes: to reserve DML access to the table on behalf of a transaction and to prevent DDL operations that would conflict with the transaction. Any table lock prevents the acquisition of an exclusive DDL lock on the same table and thereby prevents DDL operations that require such locks. For example, a table cannot be altered or dropped if an uncommitted transaction holds a table lock for it.

A table lock can be held in any of several modes: row share (RS), row exclusive (RX), share (S), share row exclusive (SRX), and exclusive (X). The restrictiveness of a table lock's mode determines the modes in which other table locks on the same table can be obtained and held.

 

Table 20-3 Summary of Table Locks  

SQL Statement	Mode of Table Lock	Lock Modes Permitted?
		RS	RX	S	SRX	X
SELECT...FROM table...	none	Y	Y	Y	Y	Y
INSERT INTO table ...	RX	Y	Y	N	N	N
UPDATE table ...	RX	Y*	Y*	N	N	N
DELETE FROM table ...	RX	Y*	Y*	N	N	N
SELECT ... FROM table    FOR UPDATE OF ...	RS	Y*	Y*	Y*	Y*	N
LOCK TABLE table IN    ROW SHARE MODE	RS	Y	Y	Y	Y	N
LOCK TABLE table IN    ROW EXCLUSIVE MODE	RX	Y	Y	N	N	N
LOCK TABLE table IN    SHARE MODE	S	Y	N	Y	N	N
LOCK TABLE table IN    SHARE ROW EXCLUSIVE    MODE	SRX	Y	N	N	N	N
LOCK TABLE table IN    EXCLUSIVE MODE	X	N	N	N	N	N
	RS: row share RX: row exclusive S: share SRX: share row exclusive X: exclusive		*Yes, if no conflicting row locks are held by another transaction. Otherwise, waits occur.

 

 

http://www.dba-oracle.com/t_v$lock.htm

 

 

TX: 

 

TYPE

'BL','Buffer hash table', 
  'CF','Control File Transaction', 
  'CI','Cross Instance Call', 
  'CS','Control File Schema', 
  'CU','Bind Enqueue', 
  'DF','Data File', 
  'DL','Direct-loader index-creation', 
  'DM','Mount/startup db primary/secondary instance', 
  'DR','Distributed Recovery Process', 
  'DX','Distributed Transaction Entry', 
  'FI','SGA Open-File Information', 
  'FS','File Set', 
  'IN','Instance Number', 
  'IR','Instance Recovery Serialization', 
  'IS','Instance State', 
  'IV','Library Cache InValidation', 
  'JQ','Job Queue', 
  'KK','Redo Log "Kick"', 
  'LS','Log Start/Log Switch', 
  'MB','Master Buffer hash table', 
  'MM','Mount Definition', 
  'MR','Media Recovery', 
  'PF','Password File', 
  'PI','Parallel Slaves', 
  'PR','Process Startup', 
  'PS','Parallel Slaves Synchronization', 
  'RE','USE_ROW_ENQUEUE Enforcement', 
  'RT','Redo Thread', 
  'RW','Row Wait', 
  'SC','System Commit Number', 
  'SH','System Commit Number HWM', 
  'SM','SMON', 
  'SQ','Sequence Number', 
  'SR','Synchronized Replication', 
  'SS','Sort Segment', 
  'ST','Space Transaction', 
  'SV','Sequence Number Value', 
  'TA','Transaction Recovery', 
  'TD','DDL enqueue', 
  'TE','Extend-segment enqueue', 
  'TM','DML enqueue', 
  'TS','Temporary Segment', 
  'TT','Temporary Table', 
  'TX','Transaction', 
  'UL','User-defined Lock', 
  'UN','User Name', 
  'US','Undo Segment Serialization', 
  'WL','Being-written redo log instance', 
  'WS','Write-atomic-log-switch global enqueue', 
  'XA','Instance Attribute', 
  'XI','Instance Registration',

LMODE

  0,'None(0)',
  1,'Null(1)',
  2,'Row Share(2)',
  3,'Row Exclu(3)',
  4,'Share(4)',
  5,'Share Row Ex(5)',
  6,'Exclusive(6)')

REQUEST

  0,'None(0)',
  1,'Null(1)',
  2,'Row Share(2)',
  3,'Row Exclu(3)',
  4,'Share(4)',
  5,'Share Row Ex(5)',
  6,'Exclusive(6)')