How to handle this situation

Hello friends here is some question.

VB Code:

var connect = Server.CreateObject( "ADODB.Connection" )
connect.Open( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\visitors.mdb;User Id=admin;Password=;" )
var rs = Server.CreateObject( "ADODB.Recordset" )
rs.Open( "SELECT Counter FROM Visitors", connect, adOpenStatic, adLockOptimistic )
if ( !rs.EOF ) {
      rs( "Counter" ) = rs( "Counter" ) + 1
      rs.Update
}
rs.Close
connect.Close
rs = connect = null

It’s not the most efficient means of updating a counter, but never mind that. This is about concurrent access.

The code above will work, but if two visitors hit the page at exactly the same time then one process won’t wait for the other to conclude. In the worst case one of the processes will abort with an error, saying that the database is locked. You could use a try/catch block, but then you’d need some wait-and-try-again logic, which would be extremely clumsy.

How can we write this code to avoid runtime errors?

You could (should) use transactions in such situations

Hi Shakti...

You need to use pessimistic locking. Optimistic suggests you'll deal with update collision errors.

Generally you'll want to use row-level locking as well.

how about changing the SQL?

SELECT COUNT(Counter) as TotCount FROM Visitors


Or... if all you want to do it update it...

UPDATE Visitors SET Count = Count + 1


-tg

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:

VB Code:

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

Let me know what is best.
Thanks
Shakti

If you don't want trivial answers, don't give trivial questions & examples.... in your example. you are getting a recordset, looping through it to update the count one by one.... that's an expensive operation for a trivial task. So I gave a simplified solution to it. There's no guarantee that transactions will never clash... all you can do is minimize the likelihood of it happening... which is exactly what my solution offers.... a quick in and out, with the least amount of fuss.

You last sample doesn't simulate two process at all.... it's a sigle process that does one thing with a sleep in it... what does that prove? Nothing.

Ultimately if you are trying to avoid errors, write queries that fit and don't do trivial non-usefull operations... secondly... error handling. If you're worried about errors, handle them.... when they happen, handle them gracefully so that the application doesn't tank.

-tg

@TG Thanks for reply
First, I’m not sure why you talk about looping. There is no looping of any kind.

Next, don’t rely on the trivial nature of the query. I was trying to keep the discussion simple and not to get sidetracked by details. If you like, consider this instead:

Vb Code:

if ( !rs.EOF ) {
      <request a value from a patient’s medical records>
      <do some work>
      <update the patient’s medical records>
      rs.Update
}

You say that “there's no guarantee that transactions will never clash” but this is exactly the problem: we can’t tolerate a clash. If we are dealing with financial or medical data, we’d better be sure that we don’t update the database improperly. One must also consider hardware failure and that sort of thing (this is where transactions help) but in this question we’re just dealing with the issue of concurrent access.

I should have better explained my code with the <sleep> in it. Of course it doesn’t simulate two processes, but what it does do is make it possible to get a conflict if you access this code simultaneously from two clients (browsers). If you don’t have a sleep in there the chance of getting a conflict is about 0%, but with the sleep your chances go up to 100%. If you’re curious, you might try this yourself to see how the server handles the conflict.

You’re right that trapping errors helps -- to some degree. But there are two problems with that. First, you have to wrap every database access in error checking code, and then handle it by waiting, and then trying again, and continuing to try -- for how many iterations? This code would be ugly and there would be arbitrary decisions about wait times and so on, and because it’s clunky are you sure that it’s done always, and done right? Have you always put this code around your queries? Second, you might not get an error, even with pessimistic locking, but that doesn’t mean that all is well. Are you absolutely sure that if no errors occurs that one transaction has finished completely before the next begins? If two transactions get interleaved, you could end up with a semantically incorrect database.

If my application has thousands of users accessing the database simultaneously, we need to be sure that our transactions are going through safely. We won’t settle for minimizing the conflicts, so that we get a clash only, say, once an hour. We want to eliminate conflicts. How can we achieve it?

Use paper products. That's the only guarantee of 0% conflicts.

It might sound like I'm being flippant about it, and in a way I am.... but that's what it amounts to. With that kind of volume you will NEVER EVER get it to 0. Your best bet is to minimize the chance. Open, get the data, get out, close it. Open, update, get out, close... as fast as you can. And if you are going to have that kind of volume, might want to think of something other than Access... MySQL, SQL Server, Oracle. They can handle transactions and concurrency much better than Access can.

-tg

Thanks for your reply. How could you solve this problem using MySQL, SQL Server or Oracle?

Is this VB6.0? I don't recognize "var connect = Server.CreateObject( "ADODB.Connection" )". And you mentioned about try/catch block which is for .Net.

It looks like JScript in an ASP context and probably shouldn't be here.


I've attached a VB6 demo that appears to do the job. Just compile to an EXE, then run about 3 copies. Wait around 25 seconds each for them to finish. Check the resulting MDB for the final Counter value. It should be 500 for each copy you run (as written) if all goes well.

I'm not saying it is perfect yet, and I am less than satisfied since it can't return the updated Counter on each update. But that wasn't listed as a requirement either.

Truly testing concurrency isn't easy though.

I had a Sleep() API call of 50 ms. in there between each update. Even commenting that out (which of course makes each copy complete more quickly) there was no error and the final Counter value was correct.

I tested on a quad core machine.