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 :
- A healthy helping of wine ( strictly medicinal, you understand)
- 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.