Thread: table locking
Hi I'm having trouble getting my head round postgres's locking. I've read through all the docs but cant seem to find the right thingthat does what I want... Basically, there are a few tables in my system where a SELECT is done prior to an INSERT to avoid duplicating certain data and so forth. So, I want to lock the table for the transaction that does the stuff above so no more than one can run at a time (essentially making it SERIALIZABLE). But, I dont want to block other readers from SELECTing from the table at the same time... This is the classic multiple readers, single writer problem yes? I'm sure its doable, but I cant work out which locking / transaction isolation levels to use. Thanks -- Shane
Shane Wright wrote: > Hi > > I'm having trouble getting my head round postgres's locking. I've read > through all the docs but cant seem to find the right thingthat does what I > want... > > > Basically, there are a few tables in my system where a SELECT is done prior > to an INSERT to avoid duplicating certain data and so forth. > > So, I want to lock the table for the transaction that does the stuff above so > no more than one can run at a time (essentially making it SERIALIZABLE). > But, I dont want to block other readers from SELECTing from the table at the > same time... > > This is the classic multiple readers, single writer problem yes? I'm sure > its doable, but I cant work out which locking / transaction isolation levels > to use. See my chapter on transactions. I think you want SERIALIZABLE transaction mode. http://www.postgresql.org/docs/awbook.html -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Hi Bruce Thanks for the information (and the book link, straight in the bookmarks and remembered for an Amazon search...) Anyway, the chapter was helpful, but I'm still stuck. SERIALIZABLE looks like it would do the job, but I don't want any queries to fail/rollback - I'd rather they stalled on SET TRANSACTION or LOCK TABLE until the first was complete. Is this possible? I am also assuming that even with SERIALIZABLE, concurrent SELECTs can proceed unhindered? Thanks Shane On Monday 18 Feb 2002 3:16 pm, Bruce Momjian wrote: > Shane Wright wrote: > > Hi > > > > I'm having trouble getting my head round postgres's locking. I've read > > through all the docs but cant seem to find the right thingthat does what > > I want... > > > > > > Basically, there are a few tables in my system where a SELECT is done > > prior to an INSERT to avoid duplicating certain data and so forth. > > > > So, I want to lock the table for the transaction that does the stuff > > above so no more than one can run at a time (essentially making it > > SERIALIZABLE). But, I dont want to block other readers from SELECTing > > from the table at the same time... > > > > This is the classic multiple readers, single writer problem yes? I'm > > sure its doable, but I cant work out which locking / transaction > > isolation levels to use. > > See my chapter on transactions. I think you want SERIALIZABLE > transaction mode. > > http://www.postgresql.org/docs/awbook.html
Shane Wright wrote: > Hi Bruce > > Thanks for the information (and the book link, straight in the bookmarks and > remembered for an Amazon search...) > > Anyway, the chapter was helpful, but I'm still stuck. SERIALIZABLE looks > like it would do the job, but I don't want any queries to fail/rollback - I'd > rather they stalled on SET TRANSACTION or LOCK TABLE until the first was > complete. > > Is this possible? > > I am also assuming that even with SERIALIZABLE, concurrent SELECTs can > proceed unhindered? Actually, I think the default READ COMMITTED will work fine for you. Backends will block waiting for your write, then continue when you commit, and reads will not be affected. I am not totally sure what you are testing, so I suggest starting two psql sessions and trying it. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
I think you want just LOCK TABLE tab IN SHARE ROW EXCLUSIVE MODE
or LOCK TABLE tab IN EXCLUSIVE MODE. The latter would lock out
SELECT FOR UPDATE, the former wouldn't.
regards, tom lane
Hi Tom, Thanks for the input, but I specifically can't use SELECT.. FOR UPDATE as the SELECT won't match any rows (it isnt supposed to, there's an explanation as to in a post I've just sent). I had thought about ACCESS EXCLUSIVE MODE; but it looks like it'll block all other readers as well, which isn't necessary. Thanks -- Shane On Monday 18 Feb 2002 3:42 pm, Tom Lane wrote: > I think you want just LOCK TABLE tab IN SHARE ROW EXCLUSIVE MODE > or LOCK TABLE tab IN EXCLUSIVE MODE. The latter would lock out > SELECT FOR UPDATE, the former wouldn't. > > regards, tom lane
Hi Bruce, I've had a go with that, but its proving quite complicated because of the number of combinations. The main problem is that the initial SELECT doesn't match any rows (it isnt supposed to), so I can use SELECT .. FOR UPDATE or anything. The exact thing is this... 1 - generate an ID randomly 2 - SELECT in the table to see if a row already has that ID 3 - if it does, loop back and generate another 4- if not, insert the new row with that ID So, I need to block other processes doing that same thing or they can (and do) pick the same ID and it gets duplicated (or one fails because of a UNIQUE constraint). I know it's not the best method for generating IDs, but it works relatively well given that I cant use sequences (gives away too much info about whats in the system), and I dont want to use oids (not portable, and issues on the oids surviving across databases). -- Shane On Monday 18 Feb 2002 3:34 pm, Bruce Momjian wrote: > Shane Wright wrote: > > Hi Bruce > > > > Thanks for the information (and the book link, straight in the bookmarks > > and remembered for an Amazon search...) > > > > Anyway, the chapter was helpful, but I'm still stuck. SERIALIZABLE looks > > like it would do the job, but I don't want any queries to fail/rollback - > > I'd rather they stalled on SET TRANSACTION or LOCK TABLE until the first > > was complete. > > > > Is this possible? > > > > I am also assuming that even with SERIALIZABLE, concurrent SELECTs can > > proceed unhindered? > > Actually, I think the default READ COMMITTED will work fine for you. > Backends will block waiting for your write, then continue when you > commit, and reads will not be affected. I am not totally sure what you > are testing, so I suggest starting two psql sessions and trying it.
Hi Tom, Sorry I tihnk I misunderstood your post the first time round :( SHARE ROW EXCLUSIVE MODE works just fine :)) Thanks very much :) Regards Shane On Monday 18 Feb 2002 3:42 pm, Tom Lane wrote: > I think you want just LOCK TABLE tab IN SHARE ROW EXCLUSIVE MODE > or LOCK TABLE tab IN EXCLUSIVE MODE. The latter would lock out > SELECT FOR UPDATE, the former wouldn't. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly