Обсуждение: is an explicit lock necessary?
Hi, Does beginning a transaction put locks on the tables queried within the transaction? In the example below, is #2 necessary? My thought was that I would need to use an explicit lock to make sure that the sequence value I'm selecting in #4 is the same one that is generated from #3. I'm worried about another instance of the application doing an insert on table1 between #3 and #4. 1) From my app, I turn off autocommit. 2) I lock table1 in access exclusive mode 3) I do an insert into table1 which generates a primary key via nextval on sequence1 4) I grab grab the primary key value via currval on sequence1 5) I do an insert on table2 which includes table1's primary key so I can join the records later. 6) I manually commit Thanks! Ash __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
On Thu, May 04, 2006 at 11:10:56AM -0700, Ash Grove wrote: > Hi, > > Does beginning a transaction put locks on the tables > queried within the transaction? You mean like a table lock? No. A transaction does entail some locks: for instance, an access exclusive lock will block behind your share lock while you're looking at the table (because the exclusive lock wants to be exclusive, of course). See the concurrency control section of the manual. > In the example below, is #2 necessary? My thought was No. currval() is local to your _session_ (not even your transaction). The docs explain how this works. A -- Andrew Sullivan | ajs@crankycanuck.ca A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton
On Thu, 4 May 2006, Ash Grove wrote: > Hi, > > Does beginning a transaction put locks on the tables > queried within the transaction? > > In the example below, is #2 necessary? My thought was > that I would need to use an explicit lock to make sure > that the sequence value I'm selecting in #4 is the > same one that is generated from #3. I'm worried about > another instance of the application doing an insert on > table1 between #3 and #4. If you have 1 session per instance and #3 and #4 are done after each other without any intervening commands, the behavior of nextval/currval should guarantee that (currval gives the value from this session's nextval, not any other).
On fim, 2006-05-04 at 11:10 -0700, Ash Grove wrote: > Hi, > > Does beginning a transaction put locks on the tables > queried within the transaction? > > In the example below, is #2 necessary? My thought was > that I would need to use an explicit lock to make sure > that the sequence value I'm selecting in #4 is the > same one that is generated from #3. I'm worried about > another instance of the application doing an insert on > table1 between #3 and #4. > > 1) From my app, I turn off autocommit. > 2) I lock table1 in access exclusive mode > 3) I do an insert into table1 which generates a > primary key via nextval on sequence1 > 4) I grab grab the primary key value via currval on > sequence1 > 5) I do an insert on table2 which includes table1's > primary key so I can join the records later. > 6) I manually commit No. The locking is not necessary. currval is defined to return the value most recently obtained by nextval for this sequence in the current session. see: http://www.postgresql.org/docs/8.1/interactive/functions-sequence.html gnari