Oracle7 Server Concepts

Contents Index Home Previous Next

How Oracle Locks Data

The only data 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 only contend for data when accessing the same rows, specifically:

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

In all cases, Oracle automatically obtains necessary locks when executing SQL statements, so users need not be concerned with such details. Oracle automatically uses the lowest applicable level of restrictiveness to provide the highest degree of data concurrency yet also provide fail-safe data integrity. Oracle also allows the user to lock data manually.

For a complete description of the internal locks used by Oracle, see "Types of Locks" [*].

Transactions and Data Concurrency

Oracle can provide data concurrency and integrity between transactions using its locking mechanisms. Because the locking mechanisms of Oracle are tied closely to transaction control, application designers need only define transactions properly, and Oracle will automatically manage locking.

Duration of Locks

All locks acquired by statements within a transaction are held for the duration of the transaction, preventing destructive interference (including dirty reads, lost updates, and destructive DDL operations) from concurrent transactions. The changes made by the SQL statements of one transaction only become visible to other transactions that start after the first transaction is committed.

Oracle releases all locks acquired by the statements within a transaction when you either commit or roll back the transaction. Oracle also releases locks acquired after a savepoint when rolling back to the savepoint. However, only transactions not waiting on the previously locked resources can acquire locks on the now available resources. Waiting transactions will continue to wait until after the original transaction commits or rolls back completely.

Data Lock Conversion and Escalation

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.

Oracle automatically converts a table lock of lower restrictiveness to one of higher restrictiveness as appropriate. For example, assume that a transaction uses a SELECT statement with the FOR UPDATE clause to lock rows of a table. As a result, it acquires the exclusive row locks and a row share table lock for the table. If the transaction later updates one or more of the locked rows, the row share table lock is automatically converted to a row exclusive table lock. For more information about table locks, see "Table Locks" [*].

Oracle does not escalate any locks at any time from one level of granularity (for example, rows) to another (for example, table), reducing the chance of deadlocks.

Deadlock Detection

Oracle automatically detects deadlock situations and resolves them automatically by rolling back one of the statements involved in the deadlock, thereby releasing one set of the conflicting row locks. A corresponding message also is returned to the transaction that undergoes statement-level rollback. The statement rolled back is the one belonging to the transaction that detects the deadlock. Usually, the signalled transaction should be rolled back explicitly, but it can retry the rolled-back statement after waiting.

Note: In distributed transactions, local deadlocks are detected by analyzing a "waits for" graph, and global deadlocks are detected by a time-out. Once detected, non-distributed and distributed deadlocks are handled by the database and application in the same way.

Deadlocks most often occur when transactions explicitly override the default locking of Oracle. Because Oracle itself does no lock escalation and does not use read locks for queries, but does use row-level locking (rather than page-level locking), deadlocks occur infrequently in Oracle. See "Explicit (Manual) Data Locking" for more information about manually acquiring locks and for an example of a deadlock situation.

Avoiding Deadlocks

Multi-table deadlocks can usually be avoided if transactions accessing the same tables lock those tables in the same order as each other, either through implicit or explicit locks. For example, all application developers might follow the rule that when both a master and detail table are updated, the master table is locked first and then the detail table. If such rules are properly designed and then followed in all applications, deadlocks are very unlikely to occur.

When you know you will require a sequence of locks for one transaction, you should consider acquiring the most exclusive (least compatible) lock first.

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 certain events occur, and the transaction no longer requires the resource.

Note: While reading this section, keep in mind that Oracle locking is fully automatic and requires no user action. Implicit locking occurs for all SQL statements so that database users never need to explicitly lock any resource. Oracle's default locking mechanisms lock data at the lowest level of restrictiveness to guarantee data integrity while allowing the highest degree of data concurrency.

Later sections also describe situations where you might wish to acquire locks manually or to alter the default locking behavior of Oracle and explain how you can do so; see "Explicit (Manual) Data Locking" [*].

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 the following general categories:

data locks (DML locks) Data locks protect data. For example, table locks lock entire tables, row locks lock selected rows.
dictionary locks (DDL locks) Dictionary locks protect the structure of objects. For example, dictionary locks protect 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.
distributed locks Distributed locks ensure that the data and other resources distributed among the various instances of an Oracle Parallel Server remain consistent. Distributed locks are held by instances rather than transactions. They communicate the current status of a resource among the instances of an Oracle Parallel Server.
parallel cache management (PCM) locks Parallel cache management locks are distributed locks that cover one or more data blocks (table or index blocks) in the buffer cache. PCM locks do not lock any rows on behalf of transactions.
The following sections discuss data locks, dictionary locks, and internal locks, respectively. For more information about distributed and PCM locks, see Oracle7 Parallel Server Concepts & Administration.

Data Locks

The purpose of a data lock (DML lock) is to guarantee the integrity of data being accessed concurrently by multiple users. Data locks prevent destructive interference of simultaneous conflicting DML and/or DDL operations. For example, Oracle data 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. The following sections explain row and table locks.

Row Locks (TX)

Note: The acronym in parentheses after each type of lock or lock mode in the following sections is the abbreviation used in the Locks Monitor of Enterprise Manager. Enterprise Manager might display TM for any table lock, rather than indicate the mode of table lock (such as RS or SRX).

A transaction acquires an exclusive data 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. Row locks are always acquired automatically by Oracle as a result of the statements listed above.

Rows Locks and Table Locks If a transaction obtains a row lock for a row, the transaction also acquires a table lock for the corresponding table. A table lock also must be obtained to prevent conflicting DDL operations that would override data changes in a current transaction. The following section explains table locks, and "DDL Locks (Dictionary Locks)" [*] explains the locks necessary for DDL operations.

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. (For more information about exclusive DDL locks, see "Exclusive DDL Locks" [*].)

A table lock can be held in any of several modes: row share (RS), row exclusive (RX), share lock (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 10 - 1 shows the modes of table locks that statements acquire and operations that those locks permit and prohibit.

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 SHARE 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
Table 10 - 1. Summary of Table Locks

RS: row share SRX: share row exclusive
RX: row exclusive X: exclusive
S: share
* if no conflicting row locks are held by another transaction;
otherwise, waits occur

The following sections explain each mode of table lock, from least restrictive to most restrictive. Each section describes the mode of table lock, the actions that cause the transaction to acquire a table lock in that mode, and which actions are permitted and prohibited in other transactions by a lock in that mode. For more information about manual locking, see "Explicit (Manual) Data Locking" [*].

Row Share Table Locks (RS) A row share table lock (also sometimes internally called a sub-share table lock, SS) indicates that the transaction holding the lock on the table has locked rows in the table and intends to update them. A row share table lock is automatically acquired for a table when one of the following SQL statements is executed:

SELECT . . . FROM table . . . FOR UPDATE OF . . . ; 
LOCK TABLE table IN ROW SHARE MODE;

A row share table lock is the least restrictive mode of table lock, offering the highest degree of concurrency for a table.

Permitted Operations: A row share table lock held by a transaction allows other transactions to query, insert, update, delete, or lock rows concurrently in the same table. Therefore, other transactions can obtain simultaneous row share, row exclusive, share, and share row exclusive table locks for the same table.

Prohibited Operations: A row share table lock held by a transaction prevents other transactions from exclusive write access to the same table using only the following statement: 

LOCK TABLE table IN EXCLUSIVE MODE;

Row Exclusive Table Locks (RX) A row exclusive table lock (also internally called a sub-exclusive table lock, SX) generally indicates that the transaction holding the lock has made one or more updates to rows in the table. A row exclusive table lock is acquired automatically for a table modified by the following types of statements: 

INSERT INTO table . . . ; 
UPDATE table . . . ; 
DELETE FROM table . . . ; 
LOCK TABLE table IN ROW EXCLUSIVE MODE;

A row exclusive table lock is slightly more restrictive than a row share table lock.

Permitted Operations: A row exclusive table lock held by a transaction allows other transactions to query, insert, update, delete, or lock rows concurrently in the same table. Therefore, row exclusive table locks allow multiple transactions to obtain simultaneous row exclusive and row share table locks for the same table.

Prohibited Operations: A row exclusive table lock held by a transaction prevents other transactions from manually locking the table for exclusive reading or writing. Therefore, other transactions cannot concurrently lock the table using the following statements: 

LOCK TABLE table IN SHARE MODE; 
LOCK TABLE table IN SHARE EXCLUSIVE MODE; 
LOCK TABLE table IN EXCLUSIVE MODE;

Share Table Locks (S) A share table lock is acquired automatically for the table specified in the following statement: 

LOCK TABLE table IN SHARE MODE;

Permitted Operations: A share table lock held by a transaction allows other transactions only to query the table, to lock specific rows with SELECT . . . FOR UPDATE, or to execute LOCK TABLE . . . IN SHARE MODE statements successfully; no updates are allowed by other transactions. Multiple transactions can hold share table locks for the same table concurrently. In this case, no transaction can update the table (even if a transaction holds row locks as the result of a SELECT statement with the FOR UPDATE clause). Therefore, a transaction that has a share table lock can only update the table if no other transactions also have a share table lock on the same table.

Prohibited Operations: A share table lock held by a transaction prevents other transactions from modifying the same table and from executing the following statements: 

LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE; 
LOCK TABLE table IN EXCLUSIVE MODE; 
LOCK TABLE table IN ROW EXCLUSIVE MODE;

Share Row Exclusive Table Locks (SRX) A share row exclusive table lock (also sometimes called a share-sub-exclusive table lock, SSX) is more restrictive than a share table lock. A share row exclusive table lock is acquired for a table as follows: 

LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;

Permitted Operations: Only one transaction at a time can acquire a share row exclusive table lock on a given table. A share row exclusive table lock held by a transaction allows other transactions to query or lock specific rows using SELECT with the FOR UPDATE clause, but not to update the table.

Prohibited Operations: A share row exclusive table lock held by a transaction prevents other transactions from obtaining row exclusive table locks and modifying the same table. A share row exclusive table lock also prohibits other transactions from obtaining share, share row exclusive, and exclusive table locks, which prevents other transactions from executing the following statements: 

LOCK TABLE table IN SHARE MODE; 
LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE; 
LOCK TABLE table IN ROW EXCLUSIVE MODE; 
LOCK TABLE table IN EXCLUSIVE MODE;

Exclusive Table Locks (X) An exclusive table lock is the most restrictive mode of table lock, allowing the transaction that holds the lock exclusive write access to the table. An exclusive table lock is acquired for a table as follows: 

LOCK TABLE table IN EXCLUSIVE MODE;

Permitted Operations: Only one transaction can obtain an exclusive table lock for a table. An exclusive table lock permits other transactions only to query the table.

Prohibited Operations: An exclusive table lock held by a transaction prohibits other transactions from performing any type of DML statement or placing any type of lock on the table.

Data Locks Automatically Acquired for DML Statements

The previous sections explained the different types of data locks, the modes in which they can be held, when they can be obtained, when they are obtained, and what they prohibit. The following sections summarize how data is automatically locked on behalf of different DML operations. Table 10 - 2 summarizes the information in the following sections.

DML Statement Row Locks? Mode of Table Lock
SELECT ...FROM table
INSERT INTO table ... _/ RX
UPDATE table ... _/ RX
DELETE FROM table ... _/ RX
SELECT ... FROM table ... FOR UPDATE OF ... _/ RS
LOCK TABLE table IN ...
ROW SHARE MODE RS
ROW EXCLUSIVE MODE RX
SHARE MODE S
SHARE EXCLUSIVE MODE SRX
EXCLUSIVE MODE X
RS: row share SRX: share row exclusive
RX: row exclusive X: exclusive
S: share

Table 10 - 2. Locks Obtained By DML Statements

Default Locking for Queries Queries are included in the following kinds of statements: 

SELECT 
INSERT . . . SELECT . . . ; 
UPDATE . . . ; 
DELETE . . . ;

They do not include the following statements: 

SELECT . . . FOR UPDATE OF . . . ;

Note that INSERT, UPDATE, and DELETE statements can have implicit queries as part of the statement.

Queries are the SQL statements least likely to interfere with other SQL statements because they only read data. The following characteristics are true of all queries that do not use the FOR UPDATE clause:

Default Locking for INSERT, UPDATE, DELETE, and SELECT ... FOR UPDATE Statements The locking characteristics of INSERT, UPDATE, DELETE, and SELECT ... FOR UPDATE statements are as follows:

DDL Locks (Dictionary Locks)

A DDL lock protects the definition of a schema object (for example, a table) while that object is acted upon or referred to by an ongoing DDL operation (recall that a DDL statement implicitly commits its transaction). For example, assume that a user creates a procedure. On behalf of the user's single statement transaction, Oracle automatically acquires DDL locks for all objects referenced in the procedure definition. The DDL locks prevent objects referenced in the procedure from being altered or dropped before the procedure compilation is complete.

A dictionary lock is acquired automatically by Oracle on behalf of any DDL transaction requiring it. Users cannot explicitly request DDL locks. Only individual schema objects that are modified or referenced are locked during DDL operations; the whole data dictionary is never locked.

DDL locks fall into three categories: exclusive DDL locks, share DDL locks, and breakable parse locks.

Exclusive DDL Locks

Certain DDL operations require exclusive DDL locks for a resource to prevent destructive interference with other DDL operations that might modify or reference the same object. For example, a DROP TABLE operation is not allowed to drop a table while an ALTER TABLE operation is adding a column to it, and vice versa.

In addition to DDL locks, DDL operations also acquire DML locks (data locks) on the object to be modified.

Most DDL operations acquire exclusive DDL locks on the object to be modified (except for those listed in the next section, "Share DDL Locks").

During the acquisition of an exclusive DDL lock, if another DDL lock is already held on the object by another operation, the acquisition waits until the older DDL lock is released and then proceeds.

Share DDL Locks

Certain DDL operations require share DDL locks for a resource to prevent destructive interference with conflicting DDL operations, but allow data concurrency for similar DDL operations. For example, when a CREATE PROCEDURE statement is executed, the containing transaction acquires share DDL locks for all referenced tables. Other transactions can concurrently create procedures that reference the same tables and therefore acquire concurrent share DDL locks on the same tables, but no transaction can acquire an exclusive DDL lock on any referenced table. No transaction can alter or drop a referenced table. As a result, a transaction that holds a share DDL lock is guaranteed that the definition of the referenced object will remain constant for the duration of the transaction.

A share DDL lock is acquired on an object for DDL statements on the object that include the following commands: AUDIT, NOAUDIT, COMMENT, CREATE [OR REPLACE] VIEW/ PROCEDURE/PACKAGE/PACKAGE BODY/FUNCTION/ TRIGGER, CREATE SYNONYM, and CREATE TABLE (when the CLUSTER parameter is not included).

Breakable Parse Locks

A SQL statement (or PL/SQL program unit) in the shared pool holds a parse lock for each object it references. Parse locks are acquired so that the associated shared SQL area can be invalidated if a referenced object is altered or dropped. See Chapter 16, "Dependencies Among Schema Objects", for more information about dependency management. A parse lock does not disallow any DDL operation and can be broken to allow conflicting DDL operations, hence the name "breakable parse lock".

A parse lock is acquired during the parse phase of SQL statement execution and held as long as the shared SQL area remains in the shared pool.

Duration of DDL Locks

The duration of a DDL lock varies depending on its type. Exclusive and share DDL locks last for the duration of DDL statement execution and automatic commit. A parse lock persists as long as the associated SQL statement remains in the shared pool.

DDL Locks and Clusters

A DDL operation on a cluster acquires exclusive DDL locks on the cluster and on all tables and snapshots in the cluster. A DDL operation on a table or snapshot in a cluster acquires a share lock on the cluster, in addition to a share or exclusive DDL lock on the table or snapshot. The share DDL lock on the cluster prevents another operation from dropping the cluster while the first operation proceeds.

Internal Locks and Latches

Internal locks and latches protect internal database and memory structures. These structures are inaccessible to users, since users have no need for control over their occurrence or duration. The following information will help you interpret the Server Manager LOCKS and LATCHES monitors.

Latches

Latches are simple, low-level serialization mechanisms to protect shared data structures in the system global area (SGA). For example, latches protect the list of users currently accessing the database and protect the data structures describing the blocks in the buffer cache. A server or background process acquires a latch for a very short time while manipulating or looking at one of these structures. The implementation of latches is operating system dependent, particularly in regard to whether and how long a process will wait for a latch.

Internal locks are higher-level, more complex mechanisms than latches and serve a variety of purposes. Details on the three categories of internal locks follow.

Dictionary Cache Locks These locks are of very short duration and are held on entries in dictionary caches while the entries are being modified or used. They guarantee that statements being parsed do not see inconsistent object definitions.

Dictionary cache locks can be shared or exclusive. Shared locks are released when the parse is complete. Exclusive locks are released when the DDL operation is complete.

File and Log Management Locks These locks protect different files. For example, one lock protects the control file so that only one process at a time can change it. Another lock coordinates the use and archiving of the redo log files. Datafiles are locked to ensure that multiple instances mount a database in shared mode or that one instance mounts it in exclusive mode. Because file and log locks indicate the status of files, these locks are necessarily held for a long time.

File and log locks are of particular importance if you are using the Oracle Parallel Server. For more information. see Oracle7 Parallel Server Concepts & Administration.

Tablespace and Rollback Segment Locks These locks protect tablespaces and rollback segments. For example, all instances accessing a database must agree on whether a tablespace is online or offline. Rollback segments are locked so that only one instance can write to a segment.

Explicit (Manual) Data Locking

In all cases, Oracle automatically performs locking to ensure data concurrency, data integrity, and statement-level read consistency. However, you can override the Oracle default locking mechanisms. Overriding the default locking is useful in situations such as these:

Oracle's automatic locking can be overridden at three levels:

transaction level Transactions including the following SQL statements override Oracle's default locking: the LOCK TABLE command (which locks either a table or, when used with views, the underlying base tables) and the SELECT.. FOR UPDATE command. Locks acquired by these statements are released after the transaction commits or rolls back. For information about each command, see the Oracle7 Server SQL Reference .
session level A session can set the required transaction isolation level with the ALTER SESSION command.
system level An instance can be started with non-default locking by adjusting the initialization parameter ISOLATION_LEVEL.
Note: If Oracle's default locking is overridden at any level, the database administrator or application developer should be sure that the overriding locking procedures operate correctly. They must satisfy the following criteria: data integrity is guaranteed, data concurrency is acceptable, and deadlocks are not possible or are appropriately handled.

Examples of Concurrency under Explicit Locking

The following illustration shows how Oracle maintains data concurrency, integrity, and consistency when LOCK TABLE and SELECT with the FOR UPDATE clause statements are used:

Note: For brevity, the message text for ORA-00054 is not included, but reads "resource busy and acquire with NOWAIT specified." User-entered text is in bold.

Transaction 1 Time Point Transaction 2 

LOCK TABLE scott.dept
  IN ROW SHARE MODE;
Statement processed

1 

DROP TABLE scott.dept;
DROP TABLE scott.dept
              *
ORA-00054
(exclusive DDL lock not possible because of T1's table lock)

LOCK TABLE scott.dept
   IN EXCLUSIVE MODE NOWAIT;

ORA-00054

2
3
4 SELECT LOC FROM scott.dept WHERE deptno = 20 FOR UPDATE OF loc; LOC - - - - - - - DALLAS 1 row selected 
UPDATE scott.dept   
  SET loc = 'NEW YORK'        WHERE deptno = 20;  
(waits because T2 has locked same rows)

5

6 ROLLBACK; (releases row locks) 
1 row processed. 
ROLLBACK;

7 
LOCK TABLE scott.dept   
  IN ROW EXCLUSIVE MODE;  

Statement processed.

8
9 
LOCK TABLE scott.dept   
  IN EXCLUSIVE MODE NOWAIT;  
ORA-00054

10 
LOCK TABLE scott.dept
 IN SHARE ROW EXCLUSIVE 
 MODE NOWAIT;   

ORA-00054

11 LOCK TABLE scott.dept IN SHARE ROW EXCLUSIVE MODE NOWAIT; ORA-00054
12 
UPDATE scott.dept   
  SET loc = 'NEW YORK'         WHERE deptno = 20;   

1 row processed.

13 
ROLLBACK;

SELECT loc   
  FROM scott.dept   
  WHERE deptno = 20   
  FOR UPDATE OF loc;   

LOC  
- - - - - - 
DALLAS  1 row selected.,

14
15 
UPDATE scott.dept   
  SET loc = 'NEW YORK'         WHERE deptno = 20; 
(waits because T1 has locked same rows)

ROLLBACK; 16
17 1 row processed. (conflicting locks were released) ROLLBACK;
LOCK TABLE scott.dept IN ROW SHARE MODE Statement processed 18
19 
LOCK TABLE scott.dept   
  IN EXCLUSIVE MODE NOWAIT;   
ORA-00054

20 LOCK TABLE scott.dept IN SHARE ROW EXCLUSIVE MODE NOWAIT; ORA-00054
21 
LOCK TABLE scott.dept   
  IN SHARE MODE;   

Statement processed.

22 
SELECT loc   
  FROM scott.dept   
  WHERE deptno = 20;   

LOC  
- - - - - - 
DALLAS  
1 row selected.

23 
SELECT loc   
  FROM scott.dept   
  WHERE deptno = 20   
  FOR UPDATE OF loc;   

LOC  
- - - - - -
DALLAS  
1 row selected.

24 
UPDATE scott.dept   
  SET loc = 'NEW YORK'         WHERE deptno = 20;  
(waits because T1 holds conflicting table lock)

ROLLBACK;

25
26 
1 row processed.  (conflicting table lock released)  
ROLLBACK;

LOCK TABLE scott.dept
 IN SHARE ROW EXCLUSIVE       MODE;

Statement processed.

27
28 
LOCK TABLE scott.dept   
  IN EXCLUSIVE MODE 
  NOWAIT;   
ORA-00054

29 LOCK TABLE scott.dept IN SHARE ROW EXCLUSIVE MODE NOWAIT; ORA-00054
30 LOCK TABLE scott.dept IN SHARE MODE NOWAIT; ORA-00054
31 
LOCK TABLE scott.dept   
  IN ROW EXCLUSIVE MODE        NOWAIT;   

ORA-00054

32 LOCK TABLE scott.dept IN SHARE MODE NOWAIT; ORA-00054
33 SELECT loc FROM scott.dept WHERE deptno = 20; LOC - - - - - - DALLAS 1 row selected.
34 SELECT loc FROM scott.dept WHERE deptno = 20 FOR UPDATE OF loc; LOC - - - - - - DALLAS 1 row selected.
35 UPDATE scott.dept SET loc = 'NEW YORK' WHERE deptno = 20; (waits because T1 holds conflicting table lock)
UPDATE scott.dept SET loc = 'NEW YORK' WHERE deptno = 20; (waits because T2 has locked same rows) 36 (deadlock) 
Cancel operation  
ROLLBACK;

37
38 
1 row processed

LOCK TABLE scott.dept IN EXCLUSIVE MODE; 39
40 LOCK TABLE scott.dept IN EXCLUSIVE MODE; ORA-00054
41 LOCK TABLE scott.dept IN ROW EXCLUSIVE MODE NOWAIT; ORA-00054
42 LOCK TABLE scott.dept IN SHARE MODE; ORA-00054
43 LOCK TABLE scott.dept IN ROW EXCLUSIVE MODE NOWAIT; ORA-00054
44 LOCK TABLE scott.dept IN ROW SHARE MODE NOWAIT; ORA-00054
45 SELECT loc FROM scott.dept WHERE deptno = 20; LOC - - - - - - DALLAS 1 row selected.
46 
SELECT loc   
  FROM scott.dept   
  WHERE deptno = 20   
  FOR UPDATE OF loc;  
(waits because T1 has conflicting table lock)

UPDATE scott.dept   
 SET deptno = 30   
 WHERE deptno = 20;  

1 row processed.

47 
COMMIT;

48
49 
0 rows selected.  
(T1 released conflicting lock)

SET TRANSACTION READ ONLY;

50 
SELECT loc   
  FROM scott.dept   
  WHERE deptno = 10;   

LOC  
- - - - - - 
BOSTON

51
52 
UPDATE scott.dept   
  SET loc = 'NEW YORK'         WHERE deptno = 10;   

1 row processed.

SELECT loc   
  FROM scott.dept   
  WHERE deptno = 10;   

LOC  
- - - - - - 
(T1 does not see uncommitted data)

53
54 COMMIT;
SELECT loc FROM scott.dept WHERE deptno = 10; LOC - - - - - - (same results seen even after T2 commits) 55
COMMIT; 56
SELECT loc FROM scott.dept WHERE deptno = 10; LOC - - - - - - NEW YORK (committed data is seen) 57

Oracle Lock Management Services

With Oracle Lock Management services, an application developer can include statements in PL/SQL blocks that

Because a reserved user lock is the same as an Oracle lock, it has all the Oracle lock functionality including deadlock detection. User locks never conflict with Oracle locks, because they are identified with the prefix "UL".

The Oracle Lock Management services are available through procedures in the DBMS_LOCK package. For more information about Oracle Lock Management services, see the Oracle7 Server Application Developer's Guide.

Contents Index Home Previous Next