Feature: FOR UPDATE SKIP LOCKED
I'm been reading up on FOR UPDATE NOWAIT and it looks like it was added in
8.1.
How difficult is it to add FOR UPDATE SKIP LOCKED or something similar?
(basically skip locked rows / oracle syntax)
More background here:
http://forge.mysql.com/worklog/task.php?id=3597
It would be quite useful to implement a database queue. Although FOR UPDATE
NOWAIT and trying again can work as well as other techniques,
just skipping over the locks has its advantages (simplicity and zero wait)
"Jonathan Bond-Caron" <jbondc@gmail.com> writes:
It would be quite useful to implement a database queue. Although FOR UPDATE
NOWAIT and trying again can work as well as other techniques,
just skipping over the locks has its advantages (simplicity and zero wait)
And disadvantages, such as complete lack of predictability or failure
detection.
regards, tom lane
On Wed, 2008-07-09 at 00:48 -0400, Tom Lane wrote:
"Jonathan Bond-Caron" <jbondc@gmail.com> writes:
It would be quite useful to implement a database queue. Although FOR UPDATE
NOWAIT and trying again can work as well as other techniques,just skipping over the locks has its advantages (simplicity and zero wait)
And disadvantages, such as complete lack of predictability or failure
detection.
Well, it's not like SQL is completely predictable in general... think
about ordering of results. Such a feature would definitely help queue
like table processing, and the fact that it is predictably unpredictable
should not be a surprise for anybody using such a feature...
Cheers,
Csaba.
Csaba Nagy wrote:
On Wed, 2008-07-09 at 00:48 -0400, Tom Lane wrote:
"Jonathan Bond-Caron" <jbondc@gmail.com> writes:
It would be quite useful to implement a database queue. Although FOR UPDATE
NOWAIT and trying again can work as well as other techniques,
just skipping over the locks has its advantages (simplicity and zero wait)And disadvantages, such as complete lack of predictability or failure
detection.Well, it's not like SQL is completely predictable in general... think
about ordering of results. Such a feature would definitely help queue
like table processing, and the fact that it is predictably unpredictable
should not be a surprise for anybody using such a feature...
Especially if it returned an updated row count or supported the
RETURNING clause, so you could find out after the fact what was or
wasn't done.
--
Craig Ringer
On Wed, 2008-07-09 at 16:23 +0800, Craig Ringer wrote:
Especially if it returned an updated row count or supported the
RETURNING clause, so you could find out after the fact what was or
wasn't done.
Well, it is supposed to be used as "SELECT ... FOR UPDATE SKIP LOCKED",
so you can in fact put the locked row ids in the target list. With a
"LIMIT 1" appended would be the perfect way to check out the next queue
item to process...
Cheers,
Csaba.
Csaba Nagy wrote:
On Wed, 2008-07-09 at 16:23 +0800, Craig Ringer wrote:
Especially if it returned an updated row count or supported the
RETURNING clause, so you could find out after the fact what was or
wasn't done.Well, it is supposed to be used as "SELECT ... FOR UPDATE SKIP LOCKED",
so you can in fact put the locked row ids in the target list. With a
"LIMIT 1" appended would be the perfect way to check out the next queue
item to process...
That makes sense. I was thinking of UPDATE ... SKIP LOCKED RETURNING
instead, which could be handy in similar situations.
--
Craig Ringer