Обсуждение: The value returned by autoinc ?
Hi!
When I insert data into a table with a sequence associated to a column
(and the required trigger), how can I know the value the sequence has
just generated ? (think in parallel accesses)
Would this approach be the answer ?
begin work
insert...
select max...
commit
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
`````````````````````````````````````````````
Silvio Emanuel Nunes Barbosa de Macedo
mailto:smacedo@inescn.pt
INESC - Porto - Grupo CAV
Pc da Republica, 93 R/C Tel:351 2 209 42 21
4000 PORTO PORTUGAL Fax:351 2 208 41 72
At 2:57 +0200 on 15/3/99, Silvio Emanuel Barbosa de Macedo wrote: > When I insert data into a table with a sequence associated to a column > (and the required trigger), how can I know the value the sequence has > just generated ? (think in parallel accesses) > > Would this approach be the answer ? > begin work > insert... > select max... > commit No, this approach is a waste of precious time... The correct approach is: INSERT... SELECT currval( 'seq_name' ); currval gives you the last value the sequence has given to the current session. That is, it won't work if you use it before the insertion (because the sequence didn't give you a number yet). It will also give you the correct number even if between the INSERT and the SELECT, another process or another connection also made an insert. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma
Thank you for your answer.
Still, one doubt.
On Mon, 15 Mar 1999, Herouth Maoz wrote:
> INSERT...
> SELECT currval( 'seq_name' );
>...
> It will also give you the
>correct number even if between the INSERT and the SELECT, another process
>or another connection also made an insert.
>Herouth
If there is an insert between my INSERT and SELECT, won't the counter be
increased ? The only way I can understand this is the transaction locks
inserts... so, in fact there could not exist another insert...
Am I wrong (or better, how wrong am I :) ) ?
Thanks again!
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
`````````````````````````````````````````````
Silvio Emanuel Nunes Barbosa de Macedo
mailto:smacedo@inescn.pt
INESC - Porto - Grupo CAV
Pc da Republica, 93 R/C Tel:351 2 209 42 21
4000 PORTO PORTUGAL Fax:351 2 208 41 72
Silvio Emanuel Barbosa de Macedo wrote: > If there is an insert between my INSERT and SELECT, won't the counter be > increased ? The only way I can understand this is the transaction locks > inserts... so, in fact there could not exist another insert... It should be a counter attached to your session, so there wouldn't be any problems, since you can only have one thread in each session. Clark
At 11:59 +0200 on 15/3/99, Silvio Emanuel Barbosa de Macedo wrote: > If there is an insert between my INSERT and SELECT, won't the counter be > increased ? The only way I can understand this is the transaction locks > inserts... so, in fact there could not exist another insert... The counter is increased - but you get the last value *you* used, not the other. It's more or less like this: counter = 5; Process1 hidden variable = null; Process2 hidden var = null; -- Process1 inserts a tuple, calling nextval. counter = 6; Process1 hidden variable = 6; -- Second process inserts a tuple. counter = 7; Process1 hidden variable = 6. Process2 hidden var = 7; -- Process1 now wants to know which number it entered, calling currval. -- Currval takes the value in the hidden variable. It's 6. -- Now suppose process1 makes another insertion. Then: counter = 8; Process1 hidden variable = 8. Process2 hidden var = 8; Do you understand? Whenever you make a call to currval, your process gets the value thus retrieved and keeps it. The two operations are done atomically (uncrementing and checking what value was taken), so it doesn't actually matter when you make the call to currval - the correct value is already available to you. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma