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 ? Continue reading