From: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
To: Richard Huxton <dev@archonet.com>
Cc: Jean-David Beyer <jeandavid8@verizon.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Check before INSERT INTO
Date: Tue, 12 Feb 2008 12:06:12 +0100
Message-ID: <00fa01c86d67$47e04bc0$3102a8c0@dream> (raw)
References: <010901c86ca6$3b66e870$3102a8c0@dream>
<873arzscnu.fsf@oxford.xeocode.com>
<024301c86cca$591c9c60$3102a8c0@dream>
<47B07DFA.8030003@verizon.net>
<00ae01c86d5e$0f2b2ba0$3102a8c0@dream>
<47B1767F.3030802@archonet.com>
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@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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: shavonne.marietta@studioform.it, dev@archonet.com, jeandavid8@verizon.net
Subject: Re: Check before INSERT INTO
In-Reply-To: <00fa01c86d67$47e04bc0$3102a8c0@dream>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox