Message-ID: <47B1767F.3030802@archonet.com>
Date: Tue, 12 Feb 2008 10:35:43 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.9 (X11/20071031)
MIME-Version: 1.0
To: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
CC: Jean-David Beyer <jeandavid8@verizon.net>, pgsql-sql@postgresql.org
Subject: Re: Check before INSERT INTO
References: <010901c86ca6$3b66e870$3102a8c0@dream>
	<873arzscnu.fsf@oxford.xeocode.com>
	<024301c86cca$591c9c60$3102a8c0@dream>
	<47B07DFA.8030003@verizon.net>
	<00ae01c86d5e$0f2b2ba0$3102a8c0@dream>
In-Reply-To: <00ae01c86d5e$0f2b2ba0$3102a8c0@dream>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Shavonne Marietta Wijesinghe wrote:
> Thanks for the replies.. But my problem still continues even after 
> setting the isolation level.
> 
> Set oRs = oConn.Execute("SELECT N_GEN FROM MY_TABLE ORDER BY N_GEN::INT 
> DESC")
> 
> If err <> 0 then 'If table not found
> GetFieldValue = "1"
> WriteToFile logfilepath, date & " " & time & " -- no table Numero 
> progressivo: 1" & vbcrlf , True
> else
> BEGIN
> SET TRANSACTION = "ISOLATION LEVEL SERIALIZABLE"
> if tipo_scheda = "SINGOLA" then
>  GetFieldValue = oRs("N_GEN") + 1
> else
>  GetFieldValue = oRs("N_GEN")
> end if
> end if
> COMMIT
> 
> Debugging my code(with logs) i see that still when 2 users login at the 
> same time the N_GEN value is the same. (they enter the first if)

Of course it is.

> In "My_Table" the last record has the value "5" so the next user that 
> logs in shoul get the value "6". Sadly both the current users get the 
> value "6".

Why sadly? What do you think should happen?

> Have i set the isolation level correctly??

I think you are having problems with thinking through the concurrency of 
this problem.

Scenario 1 - will work
==========
User1: Read value 5
User1: new value = 5 + 1
User1: Commit changes
User2: Read value 6
User2: new value = 6 + 1
User2: Commit changes

Scenario 2 - will not work
==========
User1: Read value 5
User1: new value = 5 + 1
User2: Read value 5 (there is no "6" yet, it's not been committed)
User2: new value = 5 + 1
User1: Commit changes
User2: Commit changes - ERROR

There are only two alternatives in scenario #2 - block user 2 from 
reading a value until user1 commits/rolls back or give them a value that 
might be out of date. That's what the isolation level controls.

 From your original email you have n_gen defined as a serial. That's 
basically an integer column with a default value from a 
sequence-generator. I'd just let the default value be accepted when you 
want a new number, that guarantees you a different value each time 
(although you can't guarantee you'll get 1,2,3,4,5...)

-- 
   Richard Huxton
   Archonet Ltd