pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedis an explicit lock necessary?
4+ messages / 4 participants
[nested] [flat]
* is an explicit lock necessary?
@ 2006-05-04 18:10 Ash Grove <ash_grv7@yahoo.com>
2006-05-04 18:47 ` Re: is an explicit lock necessary? Andrew Sullivan <ajs@crankycanuck.ca>
2006-05-04 18:50 ` Re: is an explicit lock necessary? Stephan Szabo <sszabo@megazone.bigpanda.com>
2006-05-04 18:59 ` Re: is an explicit lock necessary? Ragnar <gnari@hive.is>
0 siblings, 3 replies; 4+ messages in thread
From: Ash Grove @ 2006-05-04 18:10 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: is an explicit lock necessary?
2006-05-04 18:10 is an explicit lock necessary? Ash Grove <ash_grv7@yahoo.com>
@ 2006-05-04 18:47 ` Andrew Sullivan <ajs@crankycanuck.ca>
2 siblings, 0 replies; 4+ messages in thread
From: Andrew Sullivan @ 2006-05-04 18:47 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: is an explicit lock necessary?
2006-05-04 18:10 is an explicit lock necessary? Ash Grove <ash_grv7@yahoo.com>
@ 2006-05-04 18:50 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2 siblings, 0 replies; 4+ messages in thread
From: Stephan Szabo @ 2006-05-04 18:50 UTC (permalink / raw)
To: Ash Grove <ash_grv7@yahoo.com>; +Cc: pgsql-sql
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).
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: is an explicit lock necessary?
2006-05-04 18:10 is an explicit lock necessary? Ash Grove <ash_grv7@yahoo.com>
@ 2006-05-04 18:59 ` Ragnar <gnari@hive.is>
2 siblings, 0 replies; 4+ messages in thread
From: Ragnar @ 2006-05-04 18:59 UTC (permalink / raw)
To: Ash Grove <ash_grv7@yahoo.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-05-04 18:59 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-05-04 18:10 is an explicit lock necessary? Ash Grove <ash_grv7@yahoo.com>
2006-05-04 18:47 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-05-04 18:50 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2006-05-04 18:59 ` Ragnar <gnari@hive.is>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox