SELECT WHERE 'NOT LOCKED'?
От | Rod Taylor |
---|---|
Тема | SELECT WHERE 'NOT LOCKED'? |
Дата | |
Msg-id | 01fe01c0d250$78db0490$2205010a@jester обсуждение исходный текст |
Список | pgsql-hackers |
In a very volatile table I have a list of actions which need to be completed by external systems -- somewhat like a queue. I'd like the action row to be locked until it's completed so that multiple processes chewing away at it won't try to complete the same external action (easy enough -- exclusive lock on the row). Now, ORDER BY random() requires a full sort on the table -- even with LIMIT 1 in place which makes this operation quite heavy. A plain select attempts to grab the same row each time as the row is always at the top. Doing an update (to flag it) shouldn't be necessary and isn't effective. Ideally in my case, I could do: SELECT * FROM junk WHERE 'ROW NOT LOCKED' LIMIT 1 Anyway to fake this type of thing? I've thought about SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED (does that exist?), and doing an update to a flag while it's locked. Of course, I remove the row after using it so that doesn't really affect anything. What I do want though is the action to become available again if something doesn't complete. -- Rod Taylor BarChord Entertainment Inc.
В списке pgsql-hackers по дате отправления: