Expert Oracle Database Architecture读书笔记之Locking and Latching

Pessimistic Locking 

  A row lock would be placed as soon as the user indicates his intention to perform an update on a specific row that he has selected and has visible on the screen (by clicking a button on the screen, say). That row lock would persist until the application applied the users' modifications to the row in the database and committed.

Optimistic Locking

  optimistic locking, defers all locking up to the point right before the

update is performed. 

  There are many methods of implementing optimistic concurrency control. We’ve discussed one whereby the application will store all of the before images of the row in the application itself. In the following sections, we’ll explore two others, namely:
•  Using a special column that is maintained by a database trigger or application code to tell us the “version” of the record
•  Using a checksum or hash that was computed using the original data

Optimistic Locking Using a Version Column

  This is a simple implementation that involves adding a single column to each database table you wish to protect from lost updates. This column is generally either a NUMBER or DATE/TIMESTAMP column. It is typically maintained via a row trigger on the table, which is responsible for incrementing the NUMBER column or updating the DATE/TIMESTAMP column every time a row is modified.

  I would personally prefer this column be maintained by the UPDATE statement itself, not via a trigger because triggers that are not absolutely necessary (as this one is) should be avoided. For background on why I avoid triggers, refer to my “Trouble With Triggers” article from Oracle Magazine, found on the Oracle Technology Network at http://www.oracle.com/technology/oramag/oracle/08-sep/o58asktom.html.

Optimistic Locking Using a Checksum 

  This is very similar to the previous version column method, but it uses the base data itself to compute a “virtual” version column. I’ll quote the Oracle 11g Release 2 PL/SQL Supplied Packages Guide (before showing how to use one of the supplied packages!) to help explain the goal and concepts behind a checksum or hash function:

  A one-way hash function takes a variable-length input string, the data, and converts it to a fixed-length (generally smaller) output string called a hash value. The hash value serves as a unique identifier (like a fingerprint) of the input data. You can use the hash value to verify whether data has been changed or not.

Optimistic or Pessimistic Locking? 

  Today, however, optimistic concurrency control is what I would recommend for most

applications. Having a connection for the entire duration of a transaction is just too high a price to pay. 

Of the methods available, which do I use? I tend to use the version column approach with a 

timestamp column. It gives me the extra update information in a long-term sense. Furthermore, it’s less 

computationally expensive than a hash or checksum, and it doesn’t run into the issues potentially 

encountered with a hash or checksum when processing LONG, LONG RAW, CLOB, BLOB, and other very
large columns.

Blocked Inserts 


  There are few times when an INSERT will block. The most common scenario is when you have a table with a primary key or unique constraint placed on it and two sessions attempt to insert a row with the same value. One of the sessions will block until the other session either commits (in which case the blocked session will receive an error about a duplicate value) or rolls back (in which case the blocked session succeeds). Another case involves tables linked together via referential integrity constraints. An INSERT into a child table may become blocked if the parent row it depends on is being created or deleted.

Deadlocks

The number one cause of deadlocks in the Oracle database, in my experience, is unindexed foreign keys. (The number two cause is bitmap indexes on tables subject to concurrent updates, which we’ll cover in Chapter 11 “Indexes”). Oracle will place a full table lock on a child table after modification of the parent table in three scenarios:
•  If you update the parent table’s primary key (a very rare occurrence if you follow the rule of relational databases stating that primary keys should be immutable), the child table will be locked in the absence of an index on the foreign key.
•  If you delete a parent table row, the entire child table will be locked (in the absence of an index on the foreign key) as well.
•  If you merge into the parent table, the entire child table will be locked (in the absence of an index on the foreign key) as well. Note this is only true in Oracle 9i and 10g and is no longer true in Oracle 11g Release 1 and above.


  An unindexed foreign key can also be problematic in the following cases:
  •  When you have an ON DELETE CASCADE and have not indexed the child table. For example, EMP is child of DEPT. DELETE DEPTNO = 10 should CASCADE to EMP. If DEPTNO in EMP is not indexed, you will get a full table scan of EMP for each row deleted from the DEPT table. This full scan is probably undesirable, and if you delete many rows from the parent table, the child table will be scanned once for each parent row deleted.
  •  When you query from the parent to the child. Consider the EMP/DEPT example again. It is very common to query the EMP table in the context of a DEPTNO. If you frequently run the following query (say, to generate a report), you’ll find that not having the index in place will slow down the queries:
   select * from dept, emp where emp.deptno = dept.deptno and dept.deptno = :X;

  When do you not need to index a foreign key? The answer is, in general, when the following
conditions are met:

•  You do not delete from the parent table.
•  You do not update the parent table’s unique/primary key value (watch for unintended updates to the primary key by tools!).
•  You do not join from the parent to the child (like DEPT to EMP).

Lock Escalation 

  If you select a row from a table with the 

FOR UPDATE clause, two locks will be created. One lock is placed on the row(s) you selected (and this will 

be an exclusive lock; no one else can lock that specific row in exclusive mode). The other lock, a ROW 

SHARE TABLE lock, is placed on the table itself. This will prevent other sessions from placing an exclusive 

lock on the table and thus prevent them from altering the structure of the table.

Lock Types

The three general classes of locks in Oracle are as follows:
•  DML locks: DML stands for Data Manipulation Language. In general this means

SELECT, INSERT, UPDATE, MERGE, and DELETE statements. DML locks are the 

mechanism that allows for concurrent data modifications. DML locks will be, for 

example, locks on a specific row of data or a lock at the table level that locks every 

row in the table. 

•  DDL locks: DDL stands for Data Definition Language, (CREATE and ALTER 

statements, and so on). DDL locks protect the definition of the structure of 

objects. 

•  Internal locks and latches: Oracle uses these locks to protect its internal data 

structures. For example, when Oracle parses a query and generates an optimized 

query plan, it will latch the library cache to put that plan in there for other sessions 

to use. A latch is a lightweight, low-level serialization device employed by Oracle, 

similar in function to a lock. Do not confuse or be misled by the term lightweight; 

latches are a common cause of contention in the database, as you will see. They 

are lightweight in their implementation, but not their effect.

DML Locks 

  DML locks are used to ensure that only one person at a time modifies a row and that no one can drop a

table upon which you are working. Oracle will place these locks for you, more or less transparently, as 

you do work. 

TX (Transaction) Locks 

In a database with a traditional memory-based lock manager, the process of locking a row would
resemble the following:
1.  Find the address of the row you want to lock.
2.  Get in line at the lock manager (which must be serialized, as it is a common in-

memory structure). 

3.  Lock the list.

4.  Search through the list to see if anyone else has locked this row.
5.  Create a new entry in the list to establish the fact that you have locked the row.
6.  Unlock the list.
Now that you have the row locked, you can modify it. Later, as you commit your changes, you must continue the procedure as follows:
1.  Get in line again.
2.  Lock the list of locks.
3.  Search through the list and release all of your locks.
4.  Unlock the list


  As you can see, the more locks acquired, the more time spent on this operation, both before and after modifying the data. Oracle does not do it that way. Oracle’s process looks like this:
1.  Find the address of the row you want to lock.
2.  Go to the row.
3.  Lock the row right there, right then—at the location of the row, not in a big list somewhere (waiting for the transaction that has it locked to end if it is already locked, unless you are using the NOWAIT option).


In Chapter 10 “Database Tables”, we’ll get into the details of the block format, but suffice it to say that at the top of a database block is some leading overhead space in which to store a transaction table for that block. This transaction table contains an entry for each real transaction that has locked some data in that block. The size of this structure is controlled by two physical attribute parameters on the CREATE statement for an object:
•  INITRANS: The initial, preallocated size of this structure. This defaults to 2 for indexes and tables.
•  MAXTRANS: The maximum size to which this structure may grow. It defaults to  255 and has a minimum of 2 practically. In Oracle 10g and above, this setting has been deprecated, so it no longer applies. MAXTRANS is 255 regardless in that release and later.


Each block starts life with, by default, two transaction slots. The number of simultaneous active transactions that a block can ever have is constrained by the value of MAXTRANS and by the availability of space on the block. You may not be able to achieve 255 concurrent transactions on the block if there is
not sufficient space to grow this structure.

TM (DML Enqueue) Locks 

TM locks are used to ensure that the structure of a table is not altered while you are modifying its
contents. For example, if you have updated a table, you will acquire a TM lock on that table. This will

prevent another user from executing DROP or ALTER commands on that table. If another user attempts 

to perform DDL on the table while you have a TM lock on it, he’ll receive the following error message: 

drop table dept
           *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

DDL Locks 

  DDL locks are automatically placed against objects during a DDL operation to protect them from changes by other sessions. For example, if I perform the DDL operation ALTER TABLE T, the table T will in

general have an exclusive DDL lock placed against it, preventing other sessions from getting DDL locks 

and TM locks on this table.


There are three types of DDL locks:
•  Exclusive DDL locks: These prevent other sessions from gaining a DDL lock or TM (DML) lock themselves. This means that you may query a table during a DDL operation, but you may not modify it in any way.
•  Share DDL locks: These protect the structure of the referenced object against modification by other sessions, but allow modifications to the data.
•  Breakable parse locks: These allow an object, such as a query plan cached in the shared pool, to register its reliance on some other object. If you perform DDL against that object, Oracle will review the list of objects that have registered their dependence and invalidate them. Hence, these locks are breakable—they do not prevent the DDL from occurring.

Latches

  Latches are lightweight serialization devices used to coordinate multiuser access to shared data structures, objects, and files.