Thread: Selecting a non-locked row.
I'm in the process of writing an application, and I'm not sure how to properly solve it. I have a table with records in, and they either still need to be processed or not. Several people will be using the table at the same time. I was thinking about using a select .... for update limit 1, but the other persons will of course try to select the same record and wait until that one is released. The locks are held for a long period of time. Is there a way to select a row that is not locked yet? Kurt
Kurt, > I'm in the process of writing an application, and I'm not sure > how to properly solve it. > > I have a table with records in, and they either still need to be > processed or not. Several people will be using the table at the > same time. > > I was thinking about using a select .... for update limit 1, but > the other persons will of course try to select the same record > and wait until that one is released. The locks are held for a > long period of time. > > Is there a way to select a row that is not locked yet? I'm a bit confused by your question. Could you try explaining it another way, possibly with SQL code examples? -- -Josh BerkusAglio Database SolutionsSan Francisco
On Sun, Jan 19, 2003 at 01:26:10PM -0800, Josh Berkus wrote: > Kurt, > > > I have a table with records in, and they either still need to be > > processed or not. Several people will be using the table at the > > same time. > > > > I was thinking about using a select .... for update limit 1, but > > the other persons will of course try to select the same record > > and wait until that one is released. The locks are held for a > > long period of time. > > > > Is there a way to select a row that is not locked yet? > > I'm a bit confused by your question. Could you try explaining it another way, > possibly with SQL code examples? I have data in the table that should only be used once. Several people will be using the table at the same time to get a new record out of it. After they're done with it the record gets marked as done. I have a query that looks something like: begin; select id, data from table where used IS NULL order by id for update limit 1; And after some time: update table set used = 1 where id = id; commit; Of course a second person doing the same thing will just wait for my commit. What I want is that he just gets the next non-locked record. Kurt
Kurt, > Of course a second person doing the same thing will just wait for > my commit. > > What I want is that he just gets the next non-locked record. Well, there's two ways you can do this: 1) hack the system tables to find out which incomplete rows in the table are currently locked, and select the lowest ID from those that aren't. You can do this in 7.3 fairly easily throught the "pg_locks" table, but in 7.2.3 it involves a rather annoying hack of the "hidden" tuple fields (which I don't know very well, so don't ask). 2) (my preference) modify your done/not done field to accept 3 values: not done, in progress, done. Then add this step between select ... for update and the final update that updates the row as "in progress". Then you can more easily select the first "not done" row. (actually, you would have to set a tuple lock with something longer lasting than select for update, and arrange to drop it if the connection dies. but it's still my preferred solution) -- -Josh BerkusAglio Database SolutionsSan Francisco
On Sunday 19 January 2003 16:26, Josh Berkus wrote: > > I was thinking about using a select .... for update limit 1, but > > the other persons will of course try to select the same record > > and wait until that one is released. The locks are held for a > > long period of time. > > > > Is there a way to select a row that is not locked yet? > > I'm a bit confused by your question. Could you try explaining it another > way, possibly with SQL code examples? I suspect that he is looking for something like this: SELECT * FROM foo WHERE [yada yada yada] AND NOT LOCKED; I don't think we have anything like that. It's not a bad idea though. I have used this facility in Progress RDBMS and it can be useful. My application of it was to create my own sequences that were guaranteed to be contiguous. They could be out of order within reason but no numbers were allowed to be skipped. I wound up creating a special table for sequences that had multiple entries for each sequence and you would simply get the lowest unlocked number in your sequence and bump it by the count of numbers, 10 in this case. If someone else had a number locked in a transaction and after you took the next one they released it with an ABORT, the number simply became available again to the next process. I'm not sure how to do something like that without the ability to exclude locked records from the query or else with an atomic compare and set function. -- D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.