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 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
