ITPub博客

A data dictionary lock (DDL) protects the definition of a schema object while thatobject is acted upon or referred to by an ongoing DDL operation. Recall that a DDLstatement implicitly commits its transaction. For example, assume that a user creates aprocedure. On behalf of the user’s single-statement transaction, Oracle automaticallyacquires DDL locks for all schema objects referenced in the procedure definition. TheDDL locks prevent objects referenced in the procedure from being altered or dr

The locking characteristics of INSERT, UPDATE, DELETE, and SELECT ... FOR UPDATEstatements are as follows:■ The transaction that contains a DML statement acquires exclusive row locks on therows modified by the statement. Other transactions cannot update or delete thelocked rows until the locking transaction either commits or rolls back.■ The transaction that contains a DML statement does not need to acquire row lockson any rows selected by a subquery or an implicit query, such as a query in aW

Queries are the SQL statements least likely to interfere withother SQL statements because they only read data. INSERT, UPDATE, and DELETEstatements can have implicit queries as part of the statement. Queries include thefollowing kinds of statements:SELECTINSERT ... 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 UPDATEclause:■ A query acquires no data locks

The previous sections explained the different types of data locks, the modes in whichthey can be held, when they can be obtained, when they are obtained, and what theyprohibit. The following sections summarize how Oracle automatically locks data onbehalf of different DML operations.执行DML语句时自动获得的DML锁, 获取的行锁和表锁见下表Locks Obtained By DML StatementsDML Statement Row Locks? Mode of Table LockSELECT ... FROM tableINSERT INTO table ...

An exclusive table lock is the most restrictive mode of tablelock, allowing the transaction that holds the lock exclusive write access to the table. Anexclusive 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 othertransacti

A share row exclusive table lock (also sometimescalled 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 exclusivetable lock on a given table. A share row exclusive table lock held by a transactionallows other transactions to query or lock specific rows using SELECT with the FO

A share table lock is acquired automatically for the table specifiedin the following statement:LOCK TABLE table IN SHARE MODE;Permitted Operations: A share table lock held by a transaction allows other transactionsonly to query the table, to lock specific rows with SELECT ... FOR UPDATE, or to runLOCK TABLE ... IN SHARE MODE statements successfully. No updates are allowed byother transactions. Multiple transactions can hold share table locks for the same tableconcurrently. In this case, no trans

A row exclusive table lock (also called a subexclusivetable lock, SX) generally indicates that the transaction holding the lock has made oneor more updates to rows in the table. A row exclusive table lock is acquiredautomatically 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 r

A row share table lock (also sometimes called a subsharetable lock, SS) indicates that the transaction holding the lock on the table has lockedrows in the table and intends to update them. A row share table lock is automaticallyacquired for a table when one of the following SQL statements is run: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 highestdegree of concurrency for a table.P

Table-level locks are primarily used to do concurrency control with concurrent DDLoperations, such as preventing a table from being dropped in the middle of a DMLoperation. When a DDL or DML statement is on a table, a table lock is acquired. Tablelocks do not affect concurrency of DML operations. For partitioned tables, table lockscan be acquired at both the table and the subpartition level.A transaction acquires a table lock when a table is modified in the following DMLstatements: INSERT, UPDAT

Row-level locks are primarily used to prevent two transactions from modifying thesame row. When a transaction needs to modify a row, a row lock is acquired.There is no limit to the number of row locks held by a statement or transaction, andOracle does not escalate locks from the row level to a coarser granularity. Row lockingprovides the finest grain locking possible and so provides the best possibleconcurrency and throughput.The combination of multiversion concurrency control and row-level lock

The purpose of a DML lock (data lock) is to guarantee the integrity of data beingaccessed concurrently by multiple users. DML locks prevent destructive interferenceof simultaneous conflicting DML or DDL operations. DML statements automaticallyacquire both table-level locks and row-level locks.DML锁1. DML锁的作用是保证多用户并发访问的数据一致性2. DML能自动的获得表级锁和行级锁[@more@]

Multitable deadlocks can usually be avoided if transactions accessing the same tableslock those tables in the same order, either through implicit or explicit locks. Forexample, all application developers might follow the rule that when both a master anddetail table are updated, the master table is locked first and then the detail table. Ifsuch rules are properly designed and then followed in all applications, deadlocks arevery unlikely to occur.When you know you will require a sequence of locks

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

Oracle automatically uses different types of locks to control concurrent access to dataand to prevent destructive interaction between users. Oracle automatically locks aresource on behalf of a transaction to prevent other transactions from doing somethingalso requiring exclusive access to the same resource. The lock is released automaticallywhen some event occurs so that the transaction no longer requires the resource.Throughout its operation, Oracle automatically acquires different types of loc

A deadlock can occur when two or more users are waiting for data locked by eachother. Deadlocks prevent some transactions from continuing to work. Figure 13–3 is ahypothetical illustration of two transactions in a deadlock.In Figure 13–3, no problem exists at time point A, as each transaction has a row lock onthe 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

无用的(象空气一样的)更新见http://www.it-c.dk/courses/INP/E2001/Materiale/Concurrency/www/p2.php[@more@]

A transaction holds exclusive row locks for all rows inserted, updated, or deletedwithin the transaction. Because row locks are acquired at the highest degree ofrestrictiveness, no lock conversion is required or performed.Oracle automatically converts a table lock of lower restrictiveness to one of higherrestrictiveness as appropriate. For example, assume that a transaction uses a SELECTstatement with the FOR UPDATE clause to lock rows of a table. As a result, it acquiresthe exclusive row locks

All locks acquired by statements within a transaction are held for the duration of thetransaction, preventing destructive interference including dirty reads, lost updates,and destructive DDL operations from concurrent transactions. The changes made bythe SQL statements of one transaction become visible only to other transactions thatstart after the first transaction is committed.Oracle releases all locks acquired by the statements within a transaction when youeither commit or undo the transactio

Oracle uses two modes of locking in a multiuser database:■ Exclusive lock mode prevents the associates resource from being shared. This lockmode is obtained to modify data. The first transaction to lock a resourceexclusively is the only transaction that can alter the resource until the exclusivelock is released.■ Share lock mode allows the associated resource to be shared, depending on theoperations involved. Multiple users reading data can share the data, holding sharelocks to prevent concurr