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'))
/

BEGIN
    FOR i IN 1..1000 LOOP
        INSERT INTO keyhole(id) VALUES( i);
    END LOOP;
END;
/
COMMIT;

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 :

DECLARE
    CURSOR c_keyhole( p_id IN keyhole.id%TYPE) IS
       SELECT *
       FROM keyhole
       WHERE id = p_id
   FOR UPDATE;
BEGIN
   OPEN c_keyhole( 42);
   CLOSE c_keyhole;
END;
/

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.