Skip site navigation (1) Skip section navigation (2)

Re: ISOLATION LEVEL SERIALIZABLE

From: power2themacs <power2themacs(at)yahoo(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: ISOLATION LEVEL SERIALIZABLE
Date: 2002-03-26 19:42:39
Message-ID: a05101411b8c67c36aced@[128.2.161.124] (view raw, whole thread or download thread mbox)
Thread:
Lists: pgsql-general
>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


In response to

Responses

pgsql-general by date

Next:From: Doug McNaughtDate: 2002-03-26 19:58:47
Subject: Re: ISOLATION LEVEL SERIALIZABLE
Previous:From: Sonia Sanchez DiazDate: 2002-03-26 19:38:22
Subject: Re: Performance in subconsult

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group