Thread: Re: Query inside transaction

OK, thanks Michal, I did not know this issue. I thought I should use LOCK table inside the transaction to pick up the
correctid value from SELECT MAX(id) FROM e_catalog. 


On 25/06/2004 12.38, Michal Táborský <michal@taborsky.cz> wrote:
>NMB Webmaster wrote:
>
>> But if someone else runs the same transaction in the same time
>what
> > value does "currval('sequence')" return? That one of the first
> > transaction or that one of the other transaction? Moreover, field
> > id is a unique primary key, it does not accept duplicates.
>
>That's the beauty of sequences. They are transaction-safe. Co
>"currval('sequence')" will always return the same value of the previous
>
>nextval call within that transaction, no matter how many other
>transactions picked the numbers in between.
>
>To demonstrate:
>
>Transaction 1                              Transaction 2
>BEGIN;                    --
>nextval('seq') = 1            BEGIN;
>do something..                nextval('seq') = 2
>do something else...            COMMIT;
>currval('seq') = 1
>COMMIT;
>
>--
>Michal Taborsky
>http://www.taborsky.cz
>
>
>

On Fri, Jun 25, 2004 at 12:48:43 +0200,
  Ago <ago@nmb.it> wrote:
> OK, thanks Michal, I did not know this issue. I thought I should use LOCK table inside the transaction to pick up the
correctid value from SELECT MAX(id) FROM e_catalog. 

It depends on what you want. Sequences should be used to produce unique
values. If you want to get consecutively numbered rows then they shouldn't
be used.

>
>
> On 25/06/2004 12.38, Michal Táborský <michal@taborsky.cz> wrote:
> >NMB Webmaster wrote:
> >
> >> But if someone else runs the same transaction in the same time
> >what
> > > value does "currval('sequence')" return? That one of the first
> > > transaction or that one of the other transaction? Moreover, field
> > > id is a unique primary key, it does not accept duplicates.
> >
> >That's the beauty of sequences. They are transaction-safe. Co
> >"currval('sequence')" will always return the same value of the previous
> >
> >nextval call within that transaction, no matter how many other
> >transactions picked the numbers in between.

It is actually a bit stronger promise than that. Currval returns that last
assigned value from the current session, which may span multiple transactions.