Thread: 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