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" <[EMAIL PROTECTED]>
To: "Shavonne Marietta Wijesinghe" <[EMAIL PROTECTED]>
Cc: "Jean-David Beyer" <[EMAIL PROTECTED]>; <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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
