Stumblings

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.

Update: Applies only to 9.2.0.4 or earlier. See this discussion for details.

There comes a day in every bloggers life when a search on google (usually involving convoluted and totally made up combinations of words) lands them on top. Today is such a day for us. Stumblings ranks first on google when searching for oracle10g XE hang on XP.

This is of course due to my earlier post about Oracle 10g XE installation on Windows XP. I wish the post was a better organized list of issues I encountered, rather than the rant it was intended to be. So I shall be revising it soon. Hopefully “soon” means before Oracle fixes these issues on the next release.

Another one of those problems at work today. The kind that seem really really strange. Till hindsight comes along.

We have a screen to create/edit users in our client server application. As far as I am concerned, users are merely rows in the USER_PROFILES table. So all this screen has to do is update the row if it already exists and insert it if it does not. Perfect place to use that merge statement that you studied for the OCP certification, but always seemed too awkward to use.

Another feature, seemingly unrelated, is the list of application sessions maintained in table APP_SESSIONS. Each session belongs to a user so we have a foreign key from APP_SESSIONS to USER_PROFILES. Every transaction carried out by a session first updates a timestamp in APP_SESSIONS.LAST_UPDATED. Then executes other DML and a commit at the end (or rollback when there is an error).

Today there is a problem. Bob reports the create user screen hangs when he tries to create a new user. It takes a while to uncover the series of events, but here they are:

1. Alice starts a long running transaction. Her row in APP_SESSIONS is updated. This creates two locks:
   • A row lock on her APP_SESSIONS row
   • A ROW EXCLUSIVE (RX) table lock on APP_SESSIONS
2. She changes her mind after waiting a while and end tasks the application. For an unknown reason her Oracle session still sticks around, with her lock on APP_SESSIONS intact (DBAs are looking at why).
3. Other users can work normally since they have their own rows in APP_SESSIONS and lock these.
4. As soon as Bob tries to create a new user, the MERGE statement is executed. This attempts to get a ROW SHARE (S) Table locks on all child tables of USER_PROFILES. This includes the APP_SESSIONS table. This fails since Alice still has the row table locked in ROW EXCLUSIVE mode.
5. The screen hangs.

Turns out the merge statement behaves like an update and a delete (complete with both the update and delete triggers firing). So locks on child tables in step 4 are only obtained if the foreign key is un-indexed. The kicker here is:

• The Merge behaves like an Update even if the Update part of it is never executed.
• The Merge statement is treated as an update of ALL columns of the table (even columns that are not in the update part). So this behaves (atleast as far as locking goes) as if you are updating primary keys in the parent table.

We request indexes on these foreign keys and issue resolved. Bob can create users without being held up by Alice.

Meanwhile I need to better understand the implications of what each kind of lock means (and what kind of locks can be obtained when that type is in place). This article looks promising. If that is not enough, there is always Tom Kyte's excellent book to fall back on. Hopefully I will understand enough to milk it for another post.

The events:

1. I downloaded Oracle 10g XE Beta to give it a spin. But an issue with the Windows XP installer caused this to hang. Of course I did not check the forum first and assumed it was due to me messing up my 10g EE uninstall. Meaning I was dumb enough to assume I was dumb enough to mess it up.
2. Tom Kyte’s blog post about the 10g XE Production release reminded me of this unfinished business. So I downloaded the new version and started installing it. I occassionally forget that Murphy’s laws apply to me. The new version would not install because it detected an older version. I could not uninstall the older version since it hadn’t completed and there was nothing to remove in ‘Add Remove Programs’.
3. So I first had to install the older version successfully so that it could be uninstalled. I tried various things (except booting up in safe mode and getting rid of the registry entries and folders – that did work for me later). Somehow I got it to the point where it did start appearing in ‘Add Remove Programs’ and uninstalled it.
4. Started the installation of the new version. This should be a cinch now … right? Of course not. The installer has found a new place to hang. This time I went to the forum with no prompting. Cleaned up the install in safe mode. Tried the installation by disabling the firewall and anti-virus. My luck finally turned. Finally a clean install. Thanks to the guys at the forum and Danny K for not only reminding me of the forum, but pointing me to the exact thread as well.

In case you actually care about why these few events should prompt a title as pompous as the above, here are the reasons:

1. I have been waiting to use this title since I saw it on Mark Rittman’s blog.
2. The events took place over a month or so, during which I had no Oracle database to play with (except the Production instance at work I mean).