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]
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-general by date

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