Oracle – for when it was like that when you got there

Main menu

Post navigation

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 :

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.

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 :

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.