S
S.L.
This code is executed by 2 procedures simultanously. Suppose 1st proc run
before the 2nd proc for a millisecond and field1 is 1 at the starting of 1st
proc. At the ending of 1st proc, it is 1001. This is ok.
During 1st proc is running, the 2nd proc run thru "Point 1". It can read A
and can get field1 which is equal to 1. When 2nd proc run thru "Point 2", it
wait (I think it is retring to lock) until 1st proc is finished. Then 2nd
proc can pass "Point 2". Problem is here, 2nd proc updates field1 with value
1001 without any error thru end of this code.
I expect 2nd proc should error at "Point 2". It should not able to update
field1 because field1 was changed by 1st proc already. I think all DBMS can
protect this data conflict. How to correct my code to make it run correctly
?
sqlcmd = "select * from A where A_Key = 1"
cnn.open cnnstr
cnn.begintrans
for ii = 1 to 1000
rs.open sqlcmd,cnn,adOpenKeyset,adLockPessimistic '<- Point 1
rs!field1 = rs!field1 + 1
rs.update ' <- Point 2
next ii
cnn.committrans
TIA
before the 2nd proc for a millisecond and field1 is 1 at the starting of 1st
proc. At the ending of 1st proc, it is 1001. This is ok.
During 1st proc is running, the 2nd proc run thru "Point 1". It can read A
and can get field1 which is equal to 1. When 2nd proc run thru "Point 2", it
wait (I think it is retring to lock) until 1st proc is finished. Then 2nd
proc can pass "Point 2". Problem is here, 2nd proc updates field1 with value
1001 without any error thru end of this code.
I expect 2nd proc should error at "Point 2". It should not able to update
field1 because field1 was changed by 1st proc already. I think all DBMS can
protect this data conflict. How to correct my code to make it run correctly
?
sqlcmd = "select * from A where A_Key = 1"
cnn.open cnnstr
cnn.begintrans
for ii = 1 to 1000
rs.open sqlcmd,cnn,adOpenKeyset,adLockPessimistic '<- Point 1
rs!field1 = rs!field1 + 1
rs.update ' <- Point 2
next ii
cnn.committrans
TIA