Re: 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