Re: Check before INSERT INTO - Mailing list pgsql-sql

Even though n_gen is defined as a serial I can't let it handle the 
progressive key by its self since there is the need that some records should 
have the same value. That's why i use 3 primary keys.
A |  B |  C
---+---+---1  |  1  |  12  |  1  |  32  |  2  |  32  |  3  |  33  |  1  |  23  |  2  |  24  |  1  |  1

The 3 keys A, B, C are defined as Serial and Primay Keys

Anyway the other suggestion, Blocking the second user from reading the db. 
So for the second user I could give A temp key something like 0 and then do 
a select before the submit and change the value.

But how is it possible to know if some other user is reading the db??

Thanks

----- Original Message ----- 
From: "Richard Huxton" <dev@archonet.com>
To: "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it>
Cc: "Jean-David Beyer" <jeandavid8@verizon.net>; <pgsql-sql@postgresql.org>
Sent: Tuesday, February 12, 2008 11:35 AM
Subject: Re: [SQL] Check before INSERT INTO


> 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