Re: Optimistic Locking

viki wrote:
>
> While working on a web-based application, there is no straight forward
> means to lock records. The reason for this is the statelessness of web
> based applications. The web browser sends a request and the web server
> responds to the request. The transaction is restricted to this
> duration and all locks holds only within this transaction.

True.  

> The only way in which you can achieve locking in such a system is by
> what is called as optimistic locking, where you maintain a time stamp
> column (date and time of last update) in every table. When a page is
> requested, the date and time of last update of the record is also
> sent. Now, when the user updates the record, check if the time stamp
> of the record has changed. If yes you can throw an error.
>
> I guess this should work in you case as well.

The problem with doing it this way is that it's enforced at the application level, not at the database level. If you have multiple applications hitting your database and one of them isn't playing by the rules, it all breaks down. Plus you have the headache of building it into every single application that you write.

Another approach would be to build a com object (or it's U*ix equivalent) that acts as an intermediary between the webserver cgi and the database. This object would keep connections and transactions open across web requests. Every time the web server processes a request, it would send back a 'handle' to the open transaction so that when the next web request comes in it can find it's way back to the previously opened transaction.

Building this would be far from trivial, and you run the risk of abandonded transactions, so it's not something that I'd jump into lightly. Unless somebody's already built this kind of middleware...

-- 
//-Walt
// 
//