| Oracle7 Server Concepts Manual |  Library |
 Product |
 Contents |
 Index |
| Oracle7 Server Concepts Manual | Library |
Product |
Contents |
Index |
Ralph Waldo Emerson
This chapter explains how Oracle maintains consistent data in a multi-user database environment. The chapter includes:
data concurrency
Many users can access data at the same time.
data consistency
To describe consistent transaction behavior when transactions execute at the same time, database researchers have defined a transaction isolation model called serializability. The serializable mode of transaction behavior tries to ensure that transactions execute in such a way that they appear to be executed one at a time, or serially, rather than concurrently.
While this degree of isolation between transactions is generally desirable, running many applications in this mode can seriously compromise the application throughput. Complete isolation of concurrently running transactions could mean that one transaction could not do an insert into a table that was being queried by another. In short, real world considerations usually make it necessary to choose a compromise between perfect transaction isolation and performance.
Oracle offers two isolation levels, providing application developers with operational modes that preserve consistency and provide high performance.
dirty reads
A transaction reads data that has been written by a transaction that has not been committed yet.
non-repeatable (fuzzy) reads
A transaction re-reads data it has previously read and finds that another committed transaction has modified or deleted the data.
phantom read
Resources include two general types of objects:
exclusive locks
An exclusive lock prevents the associated resource from being shared and are obtained to modify data. The first transaction to exclusively lock a resource is the only transaction that can alter the resource until the exclusive lock is released.
share locks
A share lock 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 holds an exclusive lock). Several transactions can acquire share locks on the same resource.
Figure 10 - 1. Two Transactions in a Deadlock
In Figure 10 - 1, no problem exists at time point A, as each transaction has a row lock on the row it attempts to update. Each transaction proceeds (without being terminated). However, each tries to update the row currently held by the other transaction. Therefore, a deadlock results at time point B, because neither transaction can obtain the resource it needs to proceed or terminate. It is a deadlock because no matter how long each transaction waits, the conflicting locks are held.
Lock escalation greatly increases the likelihood of deadlocks. For example, imagine the situation where the system is trying to escalate locks on behalf of transaction T1 but cannot because of the locks held by transaction T2. A deadlock is created if transaction T2 also requires lock escalation before it can proceed, since the escalator is devoted to T1.
Note: Oracle never escalates locks.
Oracle uses the information maintained in its rollback segments to provide these consistent views. The rollback segments contain the old values of data that have been changed by uncommitted or recently committed transactions.
Figure 10 - 2 shows how Oracle can provide statement-level read consistency using data in rollback segments.
Figure 10 - 2. Transactions and Read Consistency
As a query enters the execution stage, the current system change number (SCN) is determined; in Figure 10 - 2, this system change number is 10023. As data blocks are read on behalf of the query, only blocks written with the observed system change number are used. Blocks with changed data (more recent SCNs) are reconstructed using data in the rollback segments, and the reconstructed data is returned for the query. Therefore, each query returns all committed data with respect to the SCN recorded at the time that query execution began. Changes of other transactions that occur during a query's execution are not observed, guaranteeing that consistent data is returned for each query.
ORA-1555: snapshot too old (rollback segment too small)
You can avoid this error by creating more or larger rollback segments. Alternatively, long-running queries can be issued when there are few concurrent transactions, or you can obtain a shared lock on the table you are querying, thus prohibiting any other exclusive locks during the transaction.
A consistent result set is provided for every query, guaranteeing data consistency, with no action on the user's part. The SQL statements SELECT, INSERT with a query, UPDATE, and DELETE all query data, either explicitly or implicitly, and all return consistent data. Each of these statements uses a query to determine which data it will affect (SELECT, INSERT, UPDATE, or DELETE, respectively).
A SELECT statement is an explicit query and may have nested queries or a join operation. An INSERT statement can use nested queries. UPDATE and DELETE statements can use WHERE clauses or subqueries to affect only some rows in a table rather than all rows.
While queries used in INSERT, UPDATE, and DELETE statements are guaranteed a consistent set of results, they do not see the changes made by the DML statement itself. In other words, the data the query in these operations sees reflects the state of the data before the operation began to make changes.
read committed
Because Oracle does not prevent other transactions from modifying the data read by a query, that data may be changed by other transactions between two executions of the query. Thus, a transaction that executes a given query twice may experience both non-repeatable read and phantoms.
serializable transactions
read only
Read only transactions see only those changes that were committed at the time the transaction began and do not allow INSERT, UPDATE, and DELETE statements.
You can set the isolation level of a transaction by using one of these commands at the beginning of a transaction:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION ISOLATION LEVEL READ ONLY;
To save the networking and processing cost of beginning each transaction with a SET TRANSACTION command, you can use the ALTER SESSION command to set the transaction isolation level for all subsequent transactions:
ALTER SESSION SET ISOLATION_LEVEL SERIALIZABLE;
ALTER SESSION SET ISOLATION_LEVEL READ COMMITTED;
You can also change the default transaction isolation level for the system by using the ALTER SYSTEM command. For detailed information on any of these SQL commands, see chapter 4 of Oracle7 Server SQL Reference.
Read committed isolation is the appropriate level of isolation for environments where few transactions are likely to conflict.
A serializable transaction executes against the database as it existed at the beginning of the transaction. A serializable transaction cannot modify rows changed by other transactions that are "too recent," that is, that commit after the serializable transaction began.
Oracle generates an error when a serializable transaction tries to update or delete data modified by a transaction that commits after the serializable transaction began:
ORA-08177: Cannot serialize access for this transaction
Here is an example:
Figure 10 - 3. Serializable Transaction Failure
When a serializable transaction fails with the "Can't serialize access" error, the application can take any of several actions:
Both read committed and serializable transactions use row-level locking, and both will wait if they try to change a row updated by an uncommitted concurrent transaction. The second transaction that tries to update a given row waits for the other transaction to commit or rollback and release its lock. If that other transaction rolls back, the waiting transaction (regardless of its isolation mode) can proceed to change the previously locked row, as if the other transaction had not existed.
However, read committed and serializable transactions behave differently if the other (blocking) transaction commits. When the other transaction commits and releases its locks, a read committed transaction will proceed with its intended update. A serializable transaction, however, will fail with the error "Can't serialize access", since the other transaction has committed a change that was made since the serializable transaction began.
Higher values of INITRANS should be used for tables that will experience many transactions updating the same blocks. This parameter will cause Oracle to allocate sufficient storage in each block to record the history of recent transactions that accessed the block.
For more information about referential integrity and serializable transactions, see Oracle7 Server Application Developer's Guide.
Oracle supports distributed serializable transactions, where a given transaction updates data in multiple physical databases (protected by two-phase commit to ensure all nodes or none commit). In a distributed database environment, all servers (whether Oracle or non-Oracle) that participate in a serializable transaction are required to support that transaction isolation mode.
If a serializable transaction tries to update data in a database managed by a server that does not support serializable transactions, the transaction receives an error indicating this. In this way, the transaction can rollback and retry only when the remote server does support serializable transactions. In contrast, read committed transactions can perform distributed transactions with servers that do not support serializable transactions.
A useful way to describe the read committed and serializable isolation levels in Oracle is to consider the following: a collection of database tables (or any set of data), a particular sequence of reads of rows in those tables, and the set of transactions committed at any particular time. An operation (a query or a transaction) is "transaction set consistent" if all its reads return data written by the same set of committed transactions. In an operation that is not transaction set consistent, some reads reflect the changes of one set of transactions, and other reads reflect changes made by other transactions. An operation that is not transaction set consistent in effect sees the database in a state that reflects no single set of committed transactions.
Oracle provides transactions executing in read committed mode with transaction set consistency on a per-statement basis. Serializable mode provides transaction set consistency on a per-transaction basis.
For environments with many concurrent users rapidly submitting transactions, designers must assess transaction performance requirements in terms of the expected transaction arrival rate and response time demands. You should choose an isolation level that provides the required degree of consistency while satisfying performance expectations. Frequently, for high performance environments, the choice of isolation levels involves making a tradeoff between consistency and concurrency (transaction throughput).
Both Oracle isolation modes provide high levels of consistency and concurrency (and performance) through the combination of row-level locking and Oracle's multiversion concurrency control system. Because readers and writers don't block one another in Oracle, while queries still see consistent data, both read committed and serializable isolation provide a high level of concurrency for high performance, without the need for reading uncommitted ("dirty") data.
Read committed isolation can provide considerably more concurrency with a somewhat increased risk of inconsistent results (due to phantoms and non-repeatable reads) for some transactions. The serializable isolation level provides somewhat more consistency by protecting against phantoms and non-repeatable reads and may be important where a read/write transaction executes a query more than once. However, serializable mode requires applications to check for the "Cannot serialize access" error and can significantly reduce throughput in an environment with many concurrent transactions accessing the same data for update. Application logic that checks database consistency must take into account the fact reads don't block writes in either mode.
Often, high performance environments with high transaction arrival rates require more throughput and faster response times than can be achieved with serializable isolation. On the other hand, an environment that supports few users with a very low transaction arrival rate faces exceedingly low risk of incorrect results due to phantoms and non-repeatable reads. Both of these environments are suitable for read committed isolation.
Oracle read committed isolation provides transaction set consistency for every query (that is, every query sees data in a consistent state). Therefore, read committed isolation will suffice for many applications that might require a higher degree of isolation if run on other database management systems that do not use multiversion concurrency control.
Read committed isolation mode does not require application logic to trap the "Cannot serialize access" error and loop back to restart a transaction. In most applications, few transactions have a functional need to re-issue the same query twice, so for many applications protection against phantoms and non-repeatable reads is not important. Therefore many developers choose read committed to avoid the need to write such error checking and retry code in each transaction.
Unlike other implementations of serializable mode, which lock blocks for read as well as write, Oracle provides the benefit of non-blocking queries and the fine granularity of row-level locking. Oracle's row-level locking and non-blocking sequence generators also reduce write/write contention. For applications that experience mostly read/write contention, Oracle serializable mode can provide significantly more throughput than other systems. Therefore, some applications might be suitable for serializable mode on Oracle but not on other systems.
Because all queries in an Oracle serializable transaction see the database as of a single point in time, this mode is suitable where multiple consistent queries must be issued in a read-write transaction. A report-writing application that generates summary data and stores it in the database might use serializable mode because it provides the consistency that a READ ONLY transaction provides, but also allows INSERT, UPDATE and DELETE.
Coding serializable transactions requires extra work by the application developer (to check for the "Cannot serialize access" error and to rollback and retry the transaction). Similar extra coding is needed in other database management systems to manage deadlocks. For adherence to corporate standards or for applications that are run on multiple database management systems, it may be necessary to design transactions for serializable mode. Transactions that check for serializability failures and retry can be used with Oracle read committed mode (which does not generate serializability errors).
Serializable mode is probably not the best choice in an environment with relatively long transactions that must update the same rows accessed by a high volume of short update transactions. Because a longer running transaction is unlikely to be the first to modify a given row, it will repeatedly need to rollback, wasting work. Note that a conventional read-locking "pessimistic" implementation of serializable mode would not be suitable for this environment either, because long-running transactions (even read transactions) would block the progress of short update transactions and vice versa.
Application developers should take into account the cost of rolling back and retrying transactions when using serializable mode. As with read-locking systems where deadlocks frequently occur, use of serializable mode requires rolling back the work done by aborted transactions and retrying them. In a high contention environment, this activity can use significant resources.
In most environments, a transaction that restarts after receiving the "Cannot serialize access" error may be unlikely to encounter a second conflict with another transaction. For this reason it can help to execute those statements most likely to contend with other transactions as early as possible in a serializable transaction. However, there is no guarantee that the transaction will successfully complete, so the application should be coded to limit the number of retries.
Although Oracle serializable mode is compatible with SQL92 and offers many benefits as compared with read-locking implementations, it does not provide semantics identical to such systems. Application designers must take into account the fact that reads in Oracle do not block writes as they do in other systems. Transactions that check for database consistency at the application level may require coding techniques such as the use of SELECT FOR UPDATE. This issue should be considered when applications using serializable mode are ported to Oracle from other environments.
The combination of multiversion concurrency control and row-level locking means that users only contend for data when accessing the same rows, specifically:
For a complete description of the internal locks used by Oracle, see "Types of Locks" ![[*]](jump.gif)
.
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.
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.
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.
When you know you will require a sequence of locks for one transaction, you should consider acquiring the most exclusive (least compatible) lock first.
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 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.
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.
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.
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.
.)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.
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.
| 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 | |
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:
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.
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.
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).
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.
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.
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.
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 | |
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.
|
Prev Next |
 Copyright © 1996 Oracle Corporation. All Rights Reserved. |
Library |
Product |
Contents |
Index |