Select For Update – Picking the lock

As a medical professional, my girlfriend is always giving me advice and tips ( apart from “that washing up won’t do itself, you know!”). You may be interested to know that Nurse Debbie’s top tips for curing insomnia are :

  1. A healthy helping of wine ( strictly medicinal, you understand)
  2. Lisen to me talk about programming

Now she’s snoring ( albeit in a ladylike fashion)…
Explicitly locking rows in application code has always been regarded as being a bit of a no-no. Let Oracle handle locking, the argument goes, or you’ll be up to your ankles in deadlocks…head-first.
Most of the time, this holds true. Sure, there’s the odd batch job daemon where you’ll lock a row in a table just to show it’s running and so shouldn’t run again before the previous iteration has completed, and maybe you’ve got a Forms block based on a Ref Cursor which requires you to lock the target table before doing any DML. For the most part however, this practice is something you want to avoid. And yet …

There are times when you just have to bite the bullet and lock that row. But when exactly does the row get locked and when does it get released ?
Let’s have a play.

CREATE TABLE keyhole (
    id NUMBER,
    message VARCHAR2(50) DEFAULT ('Hello Lloyd'))

    FOR i IN 1..1000 LOOP
        INSERT INTO keyhole(id) VALUES( i);

I never really watched Through The Keyhole so that probably wasn’t the best theme to pick for a running gag through this post. Anyway… Oracle documentation tells us that a FOR UPDATE cursor will lock the row when it is opened. So, you’d think it would release the lock when it was closed, right ? Hmmm…let’s do a little test :

    CURSOR c_keyhole( p_id IN IS
       SELECT *
       FROM keyhole
       WHERE id = p_id
   OPEN c_keyhole( 42);
   CLOSE c_keyhole;

OK, so it’s not doing much really. Should be no biggie right ? Now in a different session, let’s try to issue an update :

set timing on
update keyhole
set message = 'Sorry, no-one at home'
where id = 42

And we wait, and wait….and wait….in fact, we have to wait until we go back to the first session and terminate the transaction ( in this case we’ll just issue a ROLLBACK).
Now look at session 2 and we find the update has succeeded :

1 row updated.

Elapsed: 00:08:01.05

The point of all of this ? If you do need to lock a row explicitly in your application then

  • do it at the last possible moment
  • commit/rollback at the earliest opportunity
  • closing the cursor can wait till the transaction is completed

Disclaimer : Deb has just read this and wants to make it known that she doesn’t snore…which I’m happy to confirm as I really don’t fancy spending another night in the shed.


2 thoughts on “Select For Update – Picking the lock

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.