Select for update and locking problem
От | Steve Crawford |
---|---|
Тема | Select for update and locking problem |
Дата | |
Msg-id | 200311051408.39418.scrawford@pinpointresearch.com обсуждение исходный текст |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: