One solution would be to keep a "last modified at" field for that row, which gets updated each time the row is written to. Then, if an editing operation is started, carry the value of that field in the web page. Upon submit, retrieve the field from the DB, and compare its value to the one that was submitted along with the new data from the web page. If the value in the DB is later than the one from the submit, then the data was modified by someone else during the edit operation, and the edit should be rejected.
This is a problem with this implementation of "optimistic locking" (which is what Ulf is describing), albeit one that will occur only very, very rarely. If you use a Timestamp datatype for your versioning field you use such a small unit that it becomes very unlikely that the threads will end up with the same value. If this does happen you will get lost updates - since one will overwite the other. It is better practice to use a numeric datatype for your versioning field and increment it every time your application starts an transaction that will update the record.
Have a google for "optimistic locking" if you want to find out more.
Your ideas are very useful in my application--which is not very big.
Still, I am curious about the giant website,like ebay, amazon, how do they solve this kind of problem: say, there are more than two users operating exactly the same in an auction. It may happen, although not that often.
Hi, In addition to optimized locking, have you considered synchronizing the part of your code that actually updated the database, then , at any given time only one thread may access the method, though this can slow your app, if the db is responding slow or if the connection fails, it may solve your issue. ( provided you use it carefully)
Originally posted by Allen Mathew Williams: Hi, In addition to optimized locking, have you considered synchronizing the part of your code that actually updated the database, then , at any given time only one thread may access the method, though this can slow your app, if the db is responding slow or if the connection fails, it may solve your issue. ( provided you use it carefully)
Generally, this is not a good idea. All RDBMSs are designed to handle multiple, concurrent connections. Synchronizing access turn your database into a single user resource (from the point of view of your application anyway), and why do that? Also it does nothing to prevent another application updating the database at the same time so will not guarentee you won't get lost updates.
subject: How to keep database safe from multiple operations?