| Oracle® Da
tabase Concepts 10g Release 1 (10.1) Part Number B10743-01 |
|
|
View PDF |
| Oracle® Da
tabase Concepts 10g Release 1 (10.1) Part Number B10743-01 |
|
|
View PDF |
Thi s chapter explains how Oracle maintains consistent data in a multiuser database environment.
This chapter contains the followi ng topics:
Introduction to Data Concurrency and Consistency in a Multiuser Environment< /a>
In a single-user database, the user can modify data in the da tabase without concern for other users modifying the same data at the same time. However, in a multiuser database, the statements wit hin multiple simultaneous transactions can update the same data. Transactions executing at the same time need to produce meaningful a nd consistent results. Therefore, control of data concurrency and data consistency is vital in a multiuser database.
Data concurrency means that many users can access data at the same time.
Data consistency means that each user sees a consistent view of the data, in cluding visible changes made by the user's own transactions and transactions of other users.
To describe consistent transaction behavior when transactions run at the same time, database researchers have defined a transaction isolation model called < strong>serializability. The serializable mode of transaction behavior tries to ensure that transactions run in such a way th at they appear to be executed one at a time, or serially, rather than concurrently.
While this degree of isolation between tra nsactions is generally desirable, running many applications in this mode can seriously compromise application throughput. Complete is olation of concurrently running transactions could mean that one transaction cannot perform an insert into a table being queried by a nother transaction. In short, real-world considerations usually require a compromise between perfect transaction isolation and perfor mance.
Oracle offers two isolation levels, providing application developers with operational modes that preserve consistency a nd provide high performance.
|
See Also:< /strong> Chapter 21, " Data Integrity" for information about data integrity, which enfor ces business rules associated with a database |
The ANSI/ISO SQL standard (SQL92) defines four leve ls of transaction isolation with differing degrees of impact on transaction processing throughput. These isolation levels are defined in terms of three phenomena that must be prevented between concurrently executing transactions.
The three preventable phenome na are:
Dirty reads: < a id="sthref1926" name="sthref1926">A transaction reads data that has been written by another transaction that has not been commi tted yet.
Nonrepeatable (fuzzy) reads: A transaction rereads data it has previously read and finds that another committed transaction has modified or deleted the data.
Phantom reads (or phantoms): A transacti on re-runs a query returning a set of rows that satisfies a search condition and finds that another committed transaction has inserte d additional rows that satisfy the condition.
SQL92 defines four levels of isolation in terms of the phenomena a tra nsaction running at a particular isolation level is permitted to experience. They are shown in Table 13-1:
Table 13-1 Preventable Read Phenomena by Isolation Level
Oracle offers the read committed and serializable isolati on levels, as well as a read-only mode that is not part of SQL92. Read committed is the default.
|
See Also: "How Oracle Manages Data Con currency and Consistency" for a full discussion of read committed and serializable isolation levels |
In general, multiuser databases use some form of data locking to solve the problems associated with data concurrency, consistency , and integrity. Locks are mechanisms that prevent destructive interaction between transactions accessing the same r esource.
Resources include two general types of objects:
User objects, such as tables and rows (s tructures and data)
System objects not visible to users, such as shared data structures in the memory an d data dictionary rows
Oracle maintains data consistency in a multiuser environment by using a multiversion consistency model and various types of locks and transactions. The following topics are discussed in this se ction:
Oracle automaticall y provides read consistency to a query so that all the data that the query sees comes from a single point in time (statement- level read consistency). Oracle can also provide read consistency to all of the queries in a transaction (transactio n-level read consistency).
Oracle uses the information maintained in its rollback segments to provide these consisten t views. The rollback segments contain the old values of data that have been changed by uncommitted or recently committed transaction s. Figure 13-1 shows how Oracle provides statement-level read consistency using data in rollback segments.
< div class="figure">Figure 13-1 Transactions and Read Consistency
As a query enters the execution stage, the current system change number (SCN) is de termined. In Figure 13-1, this system change number is 10023. As data blocks are read on behalf of the query, on ly blocks written with the observed SCN are used. Blocks with changed data (more recent SCNs) are reconstructed from data in the roll back 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.
Orac le always enforces statement-level read consistency. This guarantees that a ll the data returned by a single query comes from a single point in time—the time that the query began. Therefore, a query nev er sees dirty data nor any of the changes made by transactions that commit during query execution. As query execution proceeds, only data committed before the query began is visible to the query. The query does not see changes committed after statement execution begins.
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<
/code> with a subquery, 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 can h
ave 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.
Queries used in INSERT, UPDATE, and DELETE statements are guaranteed a consistent set of resul
ts. However, they do not see the changes made by the DML statement itself. In other words, the query in these operations sees data as
it existed before the operation began to make changes.
Oracle also offers the option of enforcing transaction-level read consistency. When a transaction runs in serializable mode, all data ac cesses reflect the state of the database as of the time the transaction began. This means that the data seen by all queries within th e same transaction is consistent with respect to a single point in time, except that queries made by a serializable transaction do se e changes made by the transaction itself. Transaction-level read consistency produces repeatable reads and does not expose a query to phantoms.
Real Application Clusters (RAC) use a cache-to-cache block transfer mechanism known as Cache Fusion to transfer read-consistent images of blocks from one instance to another. RAC does this using high speed, low latency interconnects to satisfy remote requests for data blocks.
Applicatio n designers, application developers, and database administrators can choose appropriate isolation levels for different transactions, depending on the application and workload. You can set the isolation level of a transaction by using one of these statements at the beginning of a transaction:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET TRANSACTION READ ONLY;
To save the networking and processing cost of beginning each transaction with a SET TRANSACTION
statement, you can use the ALTER SESSION statement to set the tran
saction isolation level for all subsequent transactions:
ALTER SESSION SET ISOLATION_LEVEL SERIALIZABLE ; ALTER SESSION SET ISOLATION_LEVEL READ COMMITTED;
The default isolation level for Oracle is read committed. This degree of isolation is appropriate for environments where few transactions are likely to conflict. Oracle causes each query to run with respect to its o wn materialized view time, thereby permitting nonrepeatable reads and phantoms for multiple executions of a query, but providing high er potential throughput. Read committed isolation is the appropriate level of isolation for environments where few transactions are l ikely to conflict.
Serializable isolation is suitable for environments:
With large databases and short transactions that update only a few rows
Wh ere the chance that two concurrent transactions will modify the same rows is relatively low
Where relati vely long-running transactions are primarily read only
Serializable isolation permits concurrent transactions to mak e only those database changes they could have made if the transactions had been scheduled to run one after another. Specifically, Oracle permits a serializable transaction to modify a data row only if it can determine that pr ior changes to the row were made by transactions that had committed when the serializable transaction began.
To make this dete
rmination efficiently, Oracle uses control information stored in the data block that indicates which rows in the block contain commit
ted and uncommitted changes. In a sense, the block contains a recent history of transactions that affected each row in the block. The
amount of history that is retained is controlled by the INITRANS parameter of CREATE TABLE an
d ALTER TABLE.
Under some circumstances, Oracle can have insufficient history information to determi
ne whether a row has been updated by a "too recent" transaction. This can occur when many transactions concurrently modify the same d
ata block, or do so in a very short period. You can avoid this situation by setting higher v
alues of INITRANS for tables that will experience many transactions updating the same blocks. Doing so enables Orac
le to allocate sufficient storage in each block to record the history of recent transactions that accessed the block.
Oracle g enerates 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
When a serializable transaction fails with the "Cannot serialize access" error, the application can take any of several actions:< /p>
Commit the work executed to that point
Execute additional (but different) s tatements (perhaps after rolling back to a savepoint established earlier in the transaction)
Undo the en tire transaction
Figure 13-2 shows an example of an application that rolls back and retries the transaction after it fails with the "Cannot serialize access" error:
Oracle gives the application developer a choice of two transaction isolation levels with different cha racteristics. Both the read committed and serializable isolation levels provide a high degree of consistency and concurrency. Both le vels provide the contention-reducing benefits of Oracle's read consistency multiversion concurrency control model and exclusive row-l evel locking implementation and are designed for real-world application deployment.
A useful wa y to view the read committed and serializable isolation levels in Oracle is to consider the following scenario: Assume you have a col lection 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 tr ansaction set consistent if all its reads return data written by the same set of committed transactions. An operation is not transaction set consistent if some reads reflect the changes of one set of transactions and other reads reflect changes made by othe r transactions. An operation that is not transaction set consistent in effect sees the database in a state that reflects no single se t of committed transactions.
Oracle provides transactions executing in read committed mode with transaction set consistency fo r each statement. Serializable mode provides transaction set consistency for each transaction.
Table 13-2 summarizes key differences between read committed and serializable transactions in Oracle.
Table 13-2 Re ad Committed and Serializable Transactions
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 t ransaction to commit or undo and release its lock. If that other transaction rolls back, the waiting transaction, regardless of its i solation mode, can proceed to change the previously locked row as if the other transaction had not existed.
However, if the ot her blocking transaction commits and releases its locks, a read committed transaction proceeds with its intended update. A serializab le transaction, however, fails with the error "Cannot serialize access", because the other transaction has committed a change that wa s made since the serializable transaction began.
Because Oracle does not use read locks in either read-consistent or serializable transactions, data read by one tra nsaction can be overwritten by another. Transactions that perform database consistency checks at the application level cannot assume that the data they read will remain unchanged during the execution of the transaction even though such changes are not visible to the transaction. Database inconsistencies can result unless such application-level consistency checks are coded with this in mind, even when using serializable transactions.
|
S ee Also: Oracle Database Application Develop er's Guide - Fundamentals for more information about referential integrity and serializable transactions |
|
See Also: "Explicit (Manual) Data Locking " |
Multitable deadlocks can usually be avoided if transactions accessing the same tables lock those tables in the same order, either through implicit or explicit locks. For example, all application developers might follow the rule that when both a mas ter 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 f or one transaction, consider acquiring the most exclusive (least compatible) lock first.
Oracle automatically uses different types of locks to control concurrent access to data and to prevent destruct ive interaction between users. Oracle automatically locks a resource on behalf of a transaction to prevent other transactions from do ing 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 au tomatically acquires different types of locks at different levels of restrictiveness depending on the resource being locked and the o peration being performed.
Oracle locks fall into one of three general categories.
| Lock | Description | |||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| DML locks (data locks) | DML locks protect data. For exam ple, table locks lock entire tables, row locks lock selected rows. | |||||||||
| DDL locks (dictionary locks) | DDL locks protect the structu re of schema objects—for example, the definitions of tables and views. | |||||||||
| Internal locks and latches | Internal locks and l atches protect internal database structures such as datafiles. Internal locks and latches are entirely automatic. |
|
Note: The acronym in parentheses after each type of lock or lock mode is the abbreviation used in the Locks Monitor of E nterprise Manager. Enterprise Manager might display TM for any table lock, rather than indicate the mode of table lock (such as RS or SRX). |
< a id="sthref2062" name="sthref2062">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 possibl e concurrency and throughput.
The combination of multiversion concurrency control and row-level locking means that users conte nd 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 for each individual row modified by one of the following statements: INSER
T, 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 hold ing the lock is committed or rolled back. However, if the transaction dies due to instance failure, bl ock-level recovery makes a row available before the entire transaction is recovered. Row locks are always acquired automatically by O racle as a result of the statements listed previously.
If a transact ion obtains a row lock for a row, the transaction also acquires a table lock for the corresponding table. The table lock prevents con flicting DDL operations that would override data changes in a current transaction.
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 wit
h the transaction. Any table lock prevents the acquisition of an exclusive DDL lock on the same table and thereby prevents DDL operat
ions 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 sever al 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.
T able 13-3 shows the table lock modes that statements acquire and operations that those locks permit and prohibit.
Table 13-3 Summary of Table Locks
| SQL Statement | Mode of Table Lock | Lock Modes Permitted? | ||||
|---|---|---|---|---|---|---|
| RS | RX | 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 |
| RX | Y* | Y* | N | N | N | |
SELECT ... FROM table FOR UPDATE OF ... |
RS | Y* | Y* | Y* | N | |
LOCK TABLE table IN ROW SHARE MODE |
RS | Y | Y | Y | Y | N |
LOCK TABLE table IN ROW EXCLUSIVE MO
DE |
RX | Y< /td> | Y | N | N | N |
LOCK TABLE table IN SHARE MODE<
/code> |
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 e xclusive
X: exclusive
*Yes, 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. They also describe the actions t hat cause the transaction to acquire a table lock in that mode and which actions are permitted and prohibited in other transactions b y a lock in that mode.
A row share table lock (also sometimes called a subshare table lock, SS) indicates that the transaction holding the lock on the table has locked rows in the table and inten ds to update them. A row share table lock is automatically acquired for a table when one of the following SQL statements is run:
SELECT ... FROM table ... FOR UPDATE OF ... ; LOCK TABLE table IN ROW SHARE M ODE;
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, inser t, 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 lo ck 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;
A row exclusive table lock (also called a subexclusive table lock, SX) generally indicates that the transact
ion holding the lock has made one or more updates to rows in the table. A row exclusive table lock is acquired automatically for a
INSERT INTO table ... ; UPDATE < em>table ... ; DELETE FROM table ... ; LOCK TABLE table IN ROW EXCLUSIVE MODE;
A row exclusi ve table lock is slightly more restrictive than a row share table lock.
Permitted Operations: A row exclusive table l ock held by a transaction allows other transactions to query, insert, update, delete, or lock rows concurrently in the same table. Th erefore, 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 SHAR E EXCLUSIVE MODE; LOCK TABLE table IN EXCLUSIVE MODE;
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 loc
k specific rows with SELECT ... FOR UPDATE, or to run 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 update the table only 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 SHAR E ROW EXCLUSIVE MODE; LOCK TABLE table IN EXCLUSIVE MODE; LOCK TABLE table IN ROW EXCLUSIVE MODE;
A share row exclusive table lock (also sometimes called a share-su bexclusive 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;
Perm
itted Operations: Only one transaction at a time can acquire a share row exclusive table lock on a given table. A share row excl
usive table lock held by a transaction allows other transactions to query or lock specific rows using SELECT with the 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 lock s, 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 TA BLE table IN EXCLUSIVE MODE;
An exclusive table lock is the most restrictive mode of table lock, allowing the transaction that holds the lock excl usive write access to the table. An exclusive table lock is acquired for a table as follows:
L OCK 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.
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 Oracle automatically locks data on behalf of different DML operations.
Table 13-4 summarizes the information in the following sections.
Table 13-4 Locks Obtai ned By DML Statements
| DML Statement | R ow Locks? | Mode of Table Lock |
|---|---|---|
SELECT ... FROM table |
||
INSERT INTO table ... |
X | RX |
UPDATE table ... |
X | RX |
DELETE FROM table ... |
X | RX |
SELECT ... FROM table ... FOR UPDATE OF ... |
X | RS |
LOCK TABLE table IN ... |
||
ROW SHARE MODE |
RS | |
ROW EXCLUSIVE MODE |
RX | |
SHARE MODE |
S | |
SHARE EXCLUSIVE MODE |
SRX | |
| X |
X: exclus ive
RX: row exclusive
RS: row share
S: share
SRX: share row exclusive
Queries are the SQL statements least likely to interfere with other SQL statements becaus
e they only read data. INSERT, UPDATE, and DELETE statements can have implicit queries as part
of the statement. Queries include the following kinds of statements:
SELECT INSERT ... SELECT ... ; UPDATE ... ; DELETE ... ;
They do not include the following statement:
SELECT ... FOR UPDATE OF ... ;
The following characteristics are true of all queries that do not use the FOR UPDATE clause:
A query acquires no data locks. Therefore, other transactions can query
and update a table being queried, including the specific rows being queried. Because queries lacking FOR UPDATE clauses do not acquire any data locks to block other operations, such queries are often referred to in Oracle as nonbloc
king queries.
A query does not have to wait for any data locks to be released; it can always pr oceed. (Queries may have to wait for data locks in some very specific cases of pending distributed transactions.)
The locking characteristics of INSERT, UPDATE, DELETE, and SELECT ... UPDATE statements are as follows:
The transaction that contains a DML stateme nt acquires exclusive row locks on the rows modified by the statement. Other transactions cannot update or delete the locked rows unt il the locking transaction either commits or rolls back.
The transaction that contains a DML statement d
oes not need to acquire row locks on any rows selected by a subquery or an implicit query, such as a query in a WHERE cl
ause. A subquery or implicit query in a DML statement is guaranteed to be consistent as of the start of the query and does not see th
e effects of the DML statement it is part of.
A query in a transaction can see the changes made by previ ous DML statements in the same transaction, but cannot see the changes of other transactions begun after its own transaction.
In addition to the necessary exclusive row locks, a transaction that contains a DML statement acquires at least a row exclusive table lock on the table that contains the affected rows. If the containing transaction already holds a share, share row exclusive, or exclusive table lock for that table, the row exclusive table lock is not acquired. If the containing transaction al ready holds a row share table lock, Oracle automatically converts this lock to a row exclusive table lock.
A data dictionary lock (DDL) protects the definition of a schema object while that object is acted upon or re ferred to by an ongoing DDL operation. Recall that a DDL statement implicitly commits its transaction. For example, assume that a use r creates a procedure. On behalf of the user's single-statement transaction, Oracle automatically acquires DDL locks for all schema o bjects referenced in the procedure definition. The DDL locks prevent objects referenced in the procedure from being altered or droppe d before the procedure compilation is complete.
Oracle acquires a dictionary lock automatically on behalf of any DDL transacti on 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.
Most DDL operations, excep
t for those listed in the next section, "Share DDL Locks", require exclusive DDL locks for a resource to prevent destructive interfer
ence with other DDL operations that might modify or reference the same schema object. For example, a During the acquisition of an exclusive DDL lock, if another DDL lock is already held on the schema object by another operation,
the acquisition waits until the older DDL lock is released and then proceeds. DDL operations also acquire DML locks (data loc
ks) on the schema object to be modified.DROP TABLE operation is not allowed to drop a table while an ALTER TABLE operation is adding a column to it, and vice versa.
Some DDL operations require share DDL locks for a resourc
e to prevent destructive interference with conflicting DDL operations, but allow data concurrency for similar DDL operations. For exa
mple, when a CREATE PROCEDURE statement is run, 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 concur
rent 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 th
e referenced schema object will remain constant for the duration of the transaction.
A share DD
L lock is acquired on a schema object for DDL statements that include the following statements: AUDIT, NOAUDIT, COMMENT, CREATE [OR REPLACE] VIEW/ PROCEDURE/PACKAGE/PACK
AGE BODY/FUNCTION/ TRIGGER, CREATE SYNONYM, and CREATE
TABLE (when the CLUSTER parameter is not included).
A SQL statement (or PL/SQL program unit) in the shared pool h olds a parse lock for each schema object it references. Parse locks are acquired so that the associated shared SQL area can be invali dated if a referenced object is altered or dropped. A parse lock does not disallow any DDL operation and can be broken to allow confl icting DDL operations, hence the name breakable parse lock.
A parse lock is acquired during the parse phase o f SQL statement execution and held as long as the shared SQL area for that statement remains in the shared pool.
The duration of a DDL lock depends on it s 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.
A DDL operation on a cluster acquires exclusive DDL lock s on the cluster and on all tables and materialized views in the cluster. A DDL operation on a table or materialized view in a cluste r acquires a share lock on the cluster, in addition to a share or exclusive DDL lock on the table or materialized view. The share DDL lock on the cluster prevents another operation from dropping the cluster while the first operation proceeds.
Latches and internal locks protect internal database and memory struct
ures. Both are inaccessible to users, because users have no need to control over their occurrence or duration. The following section
helps to interpret the Enterprise Manager LOCKS and LATCHES monitors.
Latches are simple, low-level serialization mechanisms to protect shar ed data structures in the system global area (SGA). For example, latches protect the list of users currently accessing the database a nd 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, par ticularly in regard to whether and how long a process will wait for a latch.
In ternal locks are higher-level, more complex mechanisms than latches and serve a variety of purposes.
These loc ks are of very short duration and are held on entries in dictionary caches while the entries are being modified or used. They guarant ee 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.
These locks protect various 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 m ounts it in exclusive mode. Because file and log locks indicate the status of files, these locks are necessarily held for a long time .
Oracle always performs locking automatically to ensure data concurrency, data int egrity, 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:
Applications require transaction-level read consistency or repeatable reads. In other words, queries in them must produce consistent data for the duration of the transactio n, not reflecting changes by other transactions. You can achieve transaction-level read consistency by using explicit locking, read-o nly transactions, serializable transactions, or by overriding default locking.
Applications require that a transaction have exclusive access to a resource so that the transaction does not have to wait for other transactions to complete.< /p>
Oracle's automatic locking can be overridden at the transaction level or th e session level.
At the transaction level, transactions that include the following SQL statements override Oracle's default lo cking:
The LOCK TABLE statemen
t (which locks either a table or, when used with views, the underlying base tables)
The SELECT ... FOR UPDATE statement
Locks acquired by these statements are released after the tra nsaction commits or rolls back.
At the session level, a session can set the required transaction isolation level with the SESSION statement.
|
|
|
See Also: Oracle Datab ase SQL Reference for detailed descriptions of the SQL statementsLOCK TABLE and SELECT ... |
With Oracle Lock Management services, an application developer can include statements in PL/SQL blocks that:
Request a lock of a specific type
Give the lock a unique name recognizable in another procedure in the same or in another instance
Change the lock type
Release the lock
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.
|
See Also:
|
Oracle Flashback Query lets you view and repair historical data. You can perform queries on the database as of a certain wall clock time or user-specified system change number (SCN).
Flashback Query uses Oracle's multi version read-consistency capabilities to restore data by applying undo as needed. Administrators can configure undo retention by simp ly specifying how long undo should be kept in the database. Using Flashback Query, you can query the database as it existed this morn ing, yesterday, or last week. The speed of this operation depends only on the amount of data being queried and the number of changes to the data that need to be backed out.
You can query the history of a given row or a transaction. Using undo data stored in the database, you can view all versions of a row and revert to a previous version of that row . Flashback Transaction Query history lets you examine changes to the database at the transa ction level.
You can audit the rows of a table and get information about the transactions that changed the rows and the times when it was changed. With the transaction ID, you can do transaction mining through LogMiner to get complete information about the tr ansaction.
You set the date and time you want to view. Then, any S QL query you run operates on data as it existed at that time. If you are an authorized user, then you can correct errors and back out the restored data without needing the intervention of an administrator.
With the AS OF SQL clause,
you can choose different snapshots for each table in the query. Associating a snapshot with a table is known as table decoration<
/em>. If you do not decorate a table with a snapshot, then a default snapshot is used for it. All tables without a specified snapshot
get the same default snapshot.
For example, suppose you want to write a query to find all the new customer accounts created i
n the past hour. You could do set operations on two instances of the same table decorated with different AS OF clauses.
DML and DDL operations can use table decoration to choose snapshots within subqueries. Operations such as I
NSERT TABLE AS SELECT and CREATE TABLE AS
SELECT can be used with table decoration in the subqueries to repair tables from which rows have been mistakenly deleted. Tabl
e decoration can be any arbitrary expression: a bind variable, a constant, a string, date operations, and so on. You can open a curso
r and dynamically bind a snapshot value (a timestamp or an SCN) to decorate a table with.
|
See Also:
|
This section lists some of the benefits of using Flashback Query.
Application Transpare ncy
Packaged applications, like report generation tools that only do queries, can run in Flashback Query mode by using logon t riggers. Applications can run transparently without requiring changes to code. All the constraints that the application needs to be s atisfied are guaranteed to hold good, because there is a consistent version of the database as of the Flashback Query time.
Application Performance
If an application requires recovery actions, it can do so by saving SCNs and flash ing back to those SCNs. This is a lot easier and faster than saving data sets and restoring them later, which would be required if th e application were to do explicit versioning. Using Flashback Query, there are no costs for logging that would be incurred by explici t versioning.
Online Operation
Flashback Query is an online operation. Concurrent DMLs and querie s from other sessions are allowed while an object is queried inside Flashback Query.The speed of these operations is unaffected. More over, different sessions can flash back to different Flashback times or SCNs on the same object concurrently. The speed of the Flashb ack Query itself depends on the amount of undo that needs to be applied, which is proportional to how far back in time the query goes .
Easy Manageability
There is no additional management on the part of the user, except setting th e appropriate retention interval, having the right privileges, and so on. No additional logging has to be turned on, because past ver sions are constructed automatically, as needed.
|
Notes:
|
This sectio n lists some ways to use Flashback Query.
Self-Service Repair
Perhaps you accidentally deleted so me important rows from a table and wanted to recover the deleted rows. To do the repair, you can move backward in time and see the mi ssing rows and re-insert the deleted row into the current table.
Email or Voice Mail Applications
You might have deleted mail in the past. Using Flashback Query, you can restore the deleted mail by moving back in time and re-inser ting the deleted message into the current message box.
Account Balances
You can view account prio r account balances as of a certain day in the month.
Packaged Applications
Packaged applications (like report generation tools) can make use of Flashback Query without any changes to application logic. Any constraints that the app lication expects are guaranteed to be satisfied, because users see a consistent version of the Database as of the given time or SCN.< /p>
In addition, Flashback Query could be used after examination of audit information to see the before-image of the dat a. In DSS environments, it could be used for extraction of data as of a consistent point in time from OLTP systems.
|
See Also:
|
 Previous |
 N ext |
Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
