Oracle 9i : Merge

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.


1 Comment »

  1. Zenmed Rosacea Reviews You can see the difference in each picture I now have no redness, no more spots and my skin is less greasy. See this stuff really works. So thank you again Zenmed you are my savior!

RSS feed for comments on this post · TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: