Re: ISOLATION LEVEL SERIALIZABLE

>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