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 22:14:33
Message-ID: a05101417b8c6a321cbef@[128.2.161.124] (view raw or whole thread)
Thread:
Lists: pgsql-general
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


In response to

pgsql-general by date

Next:From: Robert WilleDate: 2002-03-26 23:18:35
Subject: Why are selects so slow on large tables, even when indexed?
Previous:From: tsmetsDate: 2002-03-26 22:06:01
Subject: Test data sets

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