Oracle Lock详解

DML Locks

These are the locks that Oracle places on your behalf when you execute DML. For the purposes of this post (and my sanity), let’s divide DML into 2 categories: Regular DML (the garden variety of INSERT, UPDATE, DELETE, SELECT FOR UPDATE) and Exotic DML (LOCK TABLE …)

This post describes the most common types of locks i.e. locks that are automatically placed on your behalf when you execute Regular DML .

1. Row Locks

These are the simplest part of the Oracle locking scheme to understand. Every row is either locked or it is unlocked. That’s it (no lock types to consider). When you execute regular DML, row locks are obtained on the affected rows.

A salient feature of Oracle’s locking mechanism is the the way row locks are recorded. The fact that a row is locked by a transaction is NOT stored in a centralized list of locks. Rather, the transaction id is stored as part of the row data itself. When your transaction needs to lock a row, Oracle inspects if the transaction specified on the row is still active. If yes, the row is locked by that transaction and your transaction is blocked. If not, the row is locked by your transaction and your transaction id is now stored on the row.

2. Table Locks

These are the ones that come in various modes. When a DML Table lock is in place, another session cannot execute DDL on this table, thus preventing alteration of the table structure while you still hold a table lock. DML table locks are also referred to as TM locks.

The fine Oracle manual tells you that Table locks can be taken in 5 different modes:

RS: row share
RX: row exclusive
S: share
SRX: share row exclusive
X: exclusive

However if you look closely, you will notice that you have to explicitly lock a table (using LOCK TABLE …) to get a lock in any of the last 3 modes (S, SRX, X). The other two modes (RS and RX) are the only ones automatically taken when you execute regular DML. So for now, let’s just consider these two.

The RS (row share) table lock is taken when you execute a SELECT … FOR UPDATE. This is in addition to the row locks on whatever rows are selected as a result of this statement. When this lock is in place other transactions may obtain RS, RX, S and SRX locks on this table. No X locks can be obtained though.

The RX (row exclusive) table lock is taken when you execute an INSERT, UPDATE or DELETE. Again, this table lock is in addition to the row locks on affected rows. When this lock is in place other transactions my place RS and RX locks but not S, SRX and X.

As far as regular DML is concerned there is no difference between the RS and RX locks. The difference only comes into play if you execute LOCK TABLE. This should be pretty rare anyway.

Anything else? Err, yes. In addition to the RS and RX locks on regular DML, S (share) table locks are taken on your table if all of the following conditions are satisfied:

1. Your table has FKs to other tables (i.e. it has parent tables).
2. You are updating the PK/UK columns or deleting on a Parent table of this table
3. The foreign key from your table to this Parent table in unindexed

Usually you do NOT update the PK columns of a table. You usually index your FKs. So you shouldn’t hit the issue of this kind of lock when you execute regular DML, right? Well, almost. There is a little issue with the 9i Merge statement that might unexpectedly cause share table locks to appear.