Re: ISOLATION LEVEL SERIALIZABLE

I am using 7.2 and JDBC. I have a very simple situation where I
insert an item with a primary key which is a SERIAL. In the same
transaction I need to insert a reference in a separate table to this
item. Of course, race conditions could occur if I didn't use this
special isolation level. But is there no way in which I could use
row-level locking instead? Certainly, I am not updating the table I
just inserted to, so the FOR UPDATE would never unlock. Is the
isolation level my only option? I noticed that psql displays the oid
after an INSERT. That would be exactly what I need but JDBC doesn't
seem to offer this.

(Right now I set the isolation level and just get the SERIAL's current value.)

|---table1----------------| |---table2-----------------|
| id SERIAL PRIMARY KEY | | id SERIAL REFERNCES table1 |

Short version:
I just inserted into table1 and need these in the same transaction.
How can I get the id I just inserted into table2? Thanks folks.
--
><><><><><><><><><><><><
power2themacs(at)yahoo(dot)com

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

You should create a trigger after insert on table1 to insert in table2
that should do it.
Furthermore you should change table2.id to int4 or int 8 and use the serial
from table1

http://www.pgexplorer.com
GUI tool for Postgres

----- Original Message -----
From: "power2themacs" <power2themacs(at)yahoo(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Sent: Tuesday, March 26, 2002 8:47 PM
Subject: [GENERAL] ISOLATION LEVEL SERIALIZABLE

> I am using 7.2 and JDBC. I have a very simple situation where I
> insert an item with a primary key which is a SERIAL. In the same
> transaction I need to insert a reference in a separate table to this
> item. Of course, race conditions could occur if I didn't use this
> special isolation level. But is there no way in which I could use
> row-level locking instead? Certainly, I am not updating the table I
> just inserted to, so the FOR UPDATE would never unlock. Is the
> isolation level my only option? I noticed that psql displays the oid
> after an INSERT. That would be exactly what I need but JDBC doesn't
> seem to offer this.
>
> (Right now I set the isolation level and just get the SERIAL's current
value.)
>
> |---table1----------------| |---table2-----------------|
> | id SERIAL PRIMARY KEY | | id SERIAL REFERNCES table1 |
>
>
> Short version:
> I just inserted into table1 and need these in the same transaction.
> How can I get the id I just inserted into table2? Thanks folks.
> --
> ><><><><><><><><><><><><
> power2themacs(at)yahoo(dot)com
>
> _________________________________________________________
> Do You Yahoo!?
> Get your free @yahoo.com address at http://mail.yahoo.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org

In table two you would not create a serial instead you would create an
INTEGER because serial is a counter and the values in table 2 may not be
in table 1.

Use a transaction like as follows

BEGIN;
INSERT INTO TABLE1 VALUES (Whatever values);
var = SELECT CURRVAL('sequence_name');
INSERT INTO TABLE2 VALUES (var,whatever else);
COMMIT;

So you would start the transaction then insert into the first table.
You would then get the current value of the sequence that the first table
created.
You would then insert this value into table2 along with anything else

HTH
Darren Ferguson

On Tue, 26 Mar 2002, power2themacs wrote:

> I am using 7.2 and JDBC. I have a very simple situation where I
> insert an item with a primary key which is a SERIAL. In the same
> transaction I need to insert a reference in a separate table to this
> item. Of course, race conditions could occur if I didn't use this
> special isolation level. But is there no way in which I could use
> row-level locking instead? Certainly, I am not updating the table I
> just inserted to, so the FOR UPDATE would never unlock. Is the
> isolation level my only option? I noticed that psql displays the oid
> after an INSERT. That would be exactly what I need but JDBC doesn't
> seem to offer this.
>
> (Right now I set the isolation level and just get the SERIAL's current value.)
>
> |---table1----------------| |---table2-----------------|
> | id SERIAL PRIMARY KEY | | id SERIAL REFERNCES table1 |
>
>
> Short version:
> I just inserted into table1 and need these in the same transaction.
> How can I get the id I just inserted into table2? Thanks folks.
> --
> ><><><><><><><><><><><><
> power2themacs(at)yahoo(dot)com
>
> _________________________________________________________
> Do You Yahoo!?
> Get your free @yahoo.com address at http://mail.yahoo.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>

>In table two you would not create a serial instead you would create an
>INTEGER because serial is a counter and the values in table 2 may not be
>in table 1.
>
>Use a transaction like as follows
>
>BEGIN;
>INSERT INTO TABLE1 VALUES (Whatever values);
>var = SELECT CURRVAL('sequence_name');
>INSERT INTO TABLE2 VALUES (var,whatever else);
>COMMIT;
>

But this is the race condition I am trying to avoid. Someone can
insert before I get the currval and it will beincremented and this
will result in invalid data. Right now, I'm doing exactly that but I
add SET ISOLATION LEVEL SERIALIZABLE; after the transaction which
locks out other INSERTS. I think I'll try PG Explorer's idea. Thanks
PG Explorer!

>So you would start the transaction then insert into the first table.
>You would then get the current value of the sequence that the first table
>created.
>You would then insert this value into table2 along with anything else
>
>HTH
>Darren Ferguson
>
>On Tue, 26 Mar 2002, power2themacs wrote:
>
>> I am using 7.2 and JDBC. I have a very simple situation where I
>> insert an item with a primary key which is a SERIAL. In the same
>> transaction I need to insert a reference in a separate table to this
>> item. Of course, race conditions could occur if I didn't use this
>> special isolation level. But is there no way in which I could use
>> row-level locking instead? Certainly, I am not updating the table I
>> just inserted to, so the FOR UPDATE would never unlock. Is the
>> isolation level my only option? I noticed that psql displays the oid
>> after an INSERT. That would be exactly what I need but JDBC doesn't
>> seem to offer this.
>>
>> (Right now I set the isolation level and just get the SERIAL's
>>current value.)
>>
>> |---table1----------------| |---table2-----------------|
>> | id SERIAL PRIMARY KEY | | id SERIAL REFERNCES table1 |
>>
>>
>> Short version:
>> I just inserted into table1 and need these in the same transaction.
>> How can I get the id I just inserted into table2? Thanks folks.
>> --
>> ><><><><><><><><><><><><
>> power2themacs(at)yahoo(dot)com
>>
>> _________________________________________________________
>> Do You Yahoo!?
>> Get your free @yahoo.com address at http://mail.yahoo.com
>>
>>
>> ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
> >

--
><><><><><><><><><><><><
AgentM
agentm(at)cmu(dot)edu

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

power2themacs <power2themacs(at)yahoo(dot)com> writes:

> >BEGIN;
> >INSERT INTO TABLE1 VALUES (Whatever values);
> >var = SELECT CURRVAL('sequence_name');
> >INSERT INTO TABLE2 VALUES (var,whatever else);
> >COMMIT;
> >
>
> But this is the race condition I am trying to avoid. Someone can
> insert before I get the currval and it will beincremented and this
> will result in invalid data.

Not true. Sequences are handled specially to avoid this problem--you
don't even have to SET TRANSACTION ISOLATION. 'currval' will always
return the last value YOU saw, regardless of what other backends might
be doing. See the docs for more details.

-Doug
--
Doug McNaught Wireboard Industries http://www.wireboard.com/

Custom software development, systems and network consulting.
Java PostgreSQL Enhydra Python Zope Perl Apache Linux BSD...

En un mensaje anterior, power2themacs escribió:
> >In table two you would not create a serial instead you would create an
> >INTEGER because serial is a counter and the values in table 2 may not be
> >in table 1.
> >
> >Use a transaction like as follows
> >
> >BEGIN;
> >INSERT INTO TABLE1 VALUES (Whatever values);
> >var = SELECT CURRVAL('sequence_name');
> >INSERT INTO TABLE2 VALUES (var,whatever else);
> >COMMIT;
> >
>
> But this is the race condition I am trying to avoid. Someone can

If you are in a trasaction, the RDBMS guarantees that your statements
executes as if their where the only ones (ie, as in a serial
execution). No race condition there.

Regards.

Fernando P. Schapachnik
fschapachnik(at)vianetworks(dot)com(dot)ar

On Tue, 26 Mar 2002, power2themacs wrote:

> >In table two you would not create a serial instead you would create an
> >INTEGER because serial is a counter and the values in table 2 may not be
> >in table 1.
> >
> >Use a transaction like as follows
> >
> >BEGIN;
> >INSERT INTO TABLE1 VALUES (Whatever values);
> >var = SELECT CURRVAL('sequence_name');
> >INSERT INTO TABLE2 VALUES (var,whatever else);
> >COMMIT;
> >
>
> But this is the race condition I am trying to avoid. Someone can
> insert before I get the currval and it will beincremented and this
> will result in invalid data. Right now, I'm doing exactly that but I

That will only happen if another insert occurs *in your session*.

Currval is defined to give the last value from your session, so whatever
happens in other sessions will not affect the value returned from currval.

power2themacs <power2themacs(at)yahoo(dot)com> writes:

> >In table two you would not create a serial instead you would create an
> >INTEGER because serial is a counter and the values in table 2 may not be
> >in table 1.
> >
> >Use a transaction like as follows
> >
> >BEGIN;
> >INSERT INTO TABLE1 VALUES (Whatever values);
> >var = SELECT CURRVAL('sequence_name');
> >INSERT INTO TABLE2 VALUES (var,whatever else);
> >COMMIT;
> >
>
> But this is the race condition I am trying to avoid. Someone can
> insert before I get the currval and it will beincremented and this
> will result in invalid data. Right now, I'm doing exactly that but I
> add SET ISOLATION LEVEL SERIALIZABLE; after the transaction which
> locks out other INSERTS. I think I'll try PG Explorer's idea. Thanks
> PG Explorer!

Actually currval is precisely what you need. It will return the
current value of the sequence in question for your particular backend
without paying attention to what might be going on in another
connection. So the above transaction is perfectly safe, and is, in
fact, the standard way of writing these sorts of transactions in
PostgreSQL.

So you can rest assured that I am not making this up, here's the
relevant bit from the PostgreSQL documentation.

currval

Return the value most recently obtained by nextval for this
sequence in the current server process. (An error is reported
if nextval has never been called for this sequence in this
process.) Notice that because this is returning a
process-local value, it gives a predictable answer even if
other server processes are executing nextval meanwhile.

I hope this is helpful,

Jason

Wow. RTFM for me huh? Thanks for the heads up folks!

>power2themacs <power2themacs(at)yahoo(dot)com> writes:
>
>> >In table two you would not create a serial instead you would create an
>> >INTEGER because serial is a counter and the values in table 2 may not be
>> >in table 1.
>> >
>> >Use a transaction like as follows
>> >
>> >BEGIN;
>> >INSERT INTO TABLE1 VALUES (Whatever values);
>> >var = SELECT CURRVAL('sequence_name');
>> >INSERT INTO TABLE2 VALUES (var,whatever else);
>> >COMMIT;
>> >
>>
>> But this is the race condition I am trying to avoid. Someone can
>> insert before I get the currval and it will beincremented and this
>> will result in invalid data. Right now, I'm doing exactly that but I
>> add SET ISOLATION LEVEL SERIALIZABLE; after the transaction which
>> locks out other INSERTS. I think I'll try PG Explorer's idea. Thanks
>> PG Explorer!
>
>Actually currval is precisely what you need. It will return the
>current value of the sequence in question for your particular backend
>without paying attention to what might be going on in another
>connection. So the above transaction is perfectly safe, and is, in
>fact, the standard way of writing these sorts of transactions in
>PostgreSQL.
>
>So you can rest assured that I am not making this up, here's the
>relevant bit from the PostgreSQL documentation.
>
> currval
>
> Return the value most recently obtained by nextval for this
> sequence in the current server process. (An error is reported
> if nextval has never been called for this sequence in this
> process.) Notice that because this is returning a
> process-local value, it gives a predictable answer even if
> other server processes are executing nextval meanwhile.
>
>I hope this is helpful,
>
>Jason

--
><><><><><><><><><><><><
AgentM
agentm(at)cmu(dot)edu

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com