How to handle this situation

[shakti5385]

Hello friends Here is my comment, please post your comment if I am wrong here.

@Lilghtning
Transactions do not help at all. These are good to ensure that a set of database changes occur in unison, but they do nothing to help with two processes updating the database at the same time. One transaction does not delay the other transaction.

@dilettante
Pessimistic locking does not solve the problem. If you try it, you can either get an error (“Could not update; currently locked.”) or you can have two database routines running simultaneously without a runtime error. In the second case, the lack of a runtime error does not equal success. If the database is being accessed simultaneously from two processes, you could end up in a semantically incorrect state. You must guarantee that access to the database is atomic and not interrupted.

@techgnome
This reduces the likelihood of two transactions happening simultaneously, but it might not guarantee it. In any case, this applies only if the function is very simple. Imagine instead that the database transaction is complex, requiring several queries. You don’t want a solution that works only for trivial queries.


I invite you to try this yourself. You can simulate two processes running the code at exactly the same time with:

var n = rs( "Counter" )
      <sleep for a few seconds>
      rs( "Counter" ) = n + 1
      rs.Update

Let me know what is best.
Thanks
Shakti