Re: Check before INSERT INTO

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 | 1
2 | 1 | 3
2 | 2 | 3
2 | 3 | 3
3 | 1 | 2
3 | 2 | 2
4 | 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(at)archonet(dot)com>
To: "Shavonne Marietta Wijesinghe" <shavonne(dot)marietta(at)studioform(dot)it>
Cc: "Jean-David Beyer" <jeandavid8(at)verizon(dot)net>; <pgsql-sql(at)postgresql(dot)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