By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,773 Members | 1,724 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,773 IT Pros & Developers. It's quick & easy.

Select for update and locking problem

P: n/a
In one system I have a table of work to be done (for simplicity in
this explanation I'll use a table consisting of id and status).

A client-side app needs to fetch 10 available items from the table but
naturally we don't want two clients working on the same 10 items.

My first attempt at handling this was, in pseudocode:

begin transaction
select * from worktodo where status = 0 limit 10 for update
update worktodo set status = 1 where id in set selected above
commit

If I test this method by hand on two "simultaneous" transactions I
will get 10 records on the first transaction and the second will
block until the first one commits.

When the first commits the second proceeds but unfortunately returns
no records even though there are plenty available.

I can reach my goal by getting an access exclusive lock on the table
and then doing the transaction but there seems there must be a better
way.

Thoughts? Comments? Ideas? Is there a way to exclude locked rows from
a query?

Cheers,
Steve
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.