Locks are mechanisms that prevent destructive interaction between transactions accessing the same resource--either user objects such as tables and rows or system objects not visible to users, such as shared data structures in memory and data dictionary rows. 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. Transactions and Data ConcurrencyOracle provides 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 automatically manages locking. 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 lock any resource explicitly. 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. Modes of LockingOracle uses two modes of locking in a multiuser database:
Lock DurationAll 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 become visible only 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 for 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 Versus Lock EscalationA 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 Lock escalation occurs when numerous locks are held at one level of granularity (for example, rows) and a database raises the locks to a higher level of granularity (for example, table). For example, if a single user locks many rows in a table, some databases automatically escalate the user's row locks to a single table. The number of locks is reduced, but the restrictiveness of what is being locked is increased. Oracle never escalates locks. Lock escalation greatly increases the likelihood of deadlocks. 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 of the same data before it can proceed. DeadlocksA deadlock can occur when two or more users are waiting for data locked by each other. Deadlocks prevent some transactions from continuing to work. Figure 20-3 illustrates two transactions in a deadlock. In Figure 20-3, 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 next 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. Figure 20-3 Two Transactions in a DeadlockText description of the illustration cncpt068.gif Deadlock DetectionOracle automatically detects deadlock situations and resolves them 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. 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.
Avoid DeadlocksMultitable 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 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, consider acquiring the most exclusive (least compatible) lock first. Types of LocksOracle automatically uses different types of locks to control concurrent access to data and to prevent destructive interaction between users. Oracle automatically locks a resource on behalf of a transaction to prevent other transactions from doing something also requiring exclusive access to the same resource. The lock is released automatically when some event occurs so that the transaction no longer requires the resource. Throughout its operation, Oracle automatically acquires different types of locks at different levels of restrictiveness depending on the resource being locked and the operation being performed. Oracle locks fall into one of three general categories. The following sections discuss DML locks, DDL locks, and internal locks. DML LocksThe purpose of a DML (data) lock is to guarantee the integrity of data being accessed concurrently by multiple users. DML locks prevent destructive interference of simultaneous conflicting DML or DDL operations. For example, Oracle DML locks guarantee that a specific row in a table can be updated by only one transaction at a time and that a table cannot be dropped if an uncommitted transaction contains an insert into the table. DML operations can acquire data locks at two different levels: for specific rows and for entire tables. Row Locks (TX)The only DML locks Oracle acquires automatically are row-level locks. There is no limit to the number of row locks held by a statement or transaction, and Oracle does not escalate locks from the row level to a coarser granularity. Row locking provides the finest grain locking possible and so provides the best possible concurrency and throughput. The combination of multiversion concurrency control and row-level locking means that users contend for data only when accessing the same rows, specifically:
A transaction acquires an exclusive DML lock for each individual row modified by one of the following statements: A modified row is always locked exclusively so that other users cannot modify the row until the transaction holding the lock is committed or rolled back. However, if the transaction dies due to instance failure, block-level recovery makes a row available before the entire transaction is recovered. Row locks are always acquired automatically by Oracle as a result of the statements listed previously. If a transaction obtains a row lock for a row, the transaction also acquires a table lock for the corresponding table. The table lock prevents conflicting DDL operations that would override data changes in a current transaction. Table Locks (TM)A transaction acquires a table lock when a table is modified in the following DML statements: A table lock can be held in any of several modes: row share (RS), row exclusive (RX), share (S), share row exclusive (SRX), and exclusive (X). The restrictiveness of a table lock's mode determines the modes in which other table locks on the same table can be obtained and held. Table 20-3 shows the table lock modes that statements acquire and operations that those locks permit and prohibit. Table 20-3 Summary of Table LocksThe following sections explain each mode of table lock, from least restrictive to most restrictive. They also describe 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. Row Share Table Locks (RS)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 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 called a subexclusive 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 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-subexclusive 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 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 Locks Automatically Acquired for DML StatementsThe 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 20-4 summarizes the information in the following sections. Table 20-4 Locks Obtained By DML StatementsDefault Locking for QueriesQueries are the SQL statements least likely to interfere with other SQL statements because they only read data. 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
Default Locking for INSERT, UPDATE, DELETE, and SELECT ... FOR UPDATEThe locking characteristics of
DDL LocksA data dictionary lock (DDL) protects the definition of a schema object 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 schema 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. Oracle acquires a dictionary lock automatically 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 LocksMost DDL operations, except for those listed in the next section, "Share DDL Locks", require exclusive DDL locks for a resource to prevent destructive interference 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 locks) on the schema object to be modified. Share DDL LocksSome 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 A share DDL lock is acquired on a schema object for DDL statements that include the following statements: Breakable Parse LocksA SQL statement (or PL/SQL program unit) in the shared pool holds a parse lock for each schema 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. 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 for that statement remains in the shared pool. Duration of DDL LocksThe duration of a DDL lock depends 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 ClustersA DDL operation on a cluster acquires exclusive DDL locks on the cluster and on all tables and materialized views in the cluster. A DDL operation on a table or materialized view in a cluster 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 LocksLatches and internal locks protect internal database and memory structures. 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 or SQL*Plus LatchesLatches 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 LocksInternal locks are higher-level, more complex mechanisms than latches and serve a variety of purposes. Dictionary Cache LocksThese 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 LocksThese 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 mounts it in exclusive mode. Because file and log locks indicate the status of files, these locks are necessarily held for a long time. Tablespace and Rollback Segment LocksThese 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 LockingOracle always performs locking automatically 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 the transaction level or the session level. At the transaction level, transactions that include the following SQL statements override Oracle's default locking:
Locks acquired by these statements are released after the transaction commits or rolls back. At the session level, a session can set the required transaction isolation level with the
Examples of Concurrency under Explicit LockingThe following illustration shows how Oracle maintains data concurrency, integrity, and consistency when
Oracle Lock Management ServicesWith 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 The Oracle Lock Management services are available through procedures in the
Flashback QueryFlashback 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 commit number (SCN). Flashback query uses Oracle's multiversion read-consistency capabilities to restore data by applying undo as needed. Administrators can configure undo retention by simply specifying how long undo should be kept in the database. Using flashback query, a user can query the database as it existed this morning, 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 set the date and time you want to view. Then, any SQL query you execute 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 For example, suppose you want to write a query to find all the new customer accounts created in the past hour. You could do set operations on two instances of the same table decorated with different DML and DDL operations can use table decoration to choose snapshots within subqueries. Operations such as
Flashback Query Benefits
Some Uses of Flashback QuerySelf-Service RepairPerhaps you accidentally deleted some 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 missing rows and re-insert the deleted row into the current table. E-Mail or Voice Mail ApplicationsYou might have deleted mail in the past. Using flashback query, you can restore the deleted mail by moving back in time and re-inserting the deleted message into the current message box. Account BalancesYou can view account prior account balances as of a certain day in the month. Packaged ApplicationsPackaged applications (like report generation tools) can make use of flashback query without any changes to application logic. Any constraints that the application expects are guaranteed to be satisfied, because users see a consistent version of the Database as of the given time or SCN. In addition, flashback query could be used after examination of audit information to see the before-image of the data. In DSS enviornments, it could be used for extraction of data as of a consistent point in time from OLTP systems.
|
|