From: Richard Huxton <dev@archonet.com>
To: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
Cc: Jean-David Beyer <jeandavid8@verizon.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Check before INSERT INTO
Date: Tue, 12 Feb 2008 10:35:43 +0000
Message-ID: <47B1767F.3030802@archonet.com> (raw)
In-Reply-To: <00ae01c86d5e$0f2b2ba0$3102a8c0@dream>
References: <010901c86ca6$3b66e870$3102a8c0@dream>
<873arzscnu.fsf@oxford.xeocode.com>
<024301c86cca$591c9c60$3102a8c0@dream>
<47B07DFA.8030003@verizon.net>
<00ae01c86d5e$0f2b2ba0$3102a8c0@dream>
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: dev@archonet.com, shavonne.marietta@studioform.it, jeandavid8@verizon.net
Subject: Re: Check before INSERT INTO
In-Reply-To: <47B1767F.3030802@archonet.com>
* 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