>Martin, no it's not generalities ;-) and thanks to this great discussion I've altered my approach: if I were to move to NET today, I'd create data processes with change tracking plus an inbuilt pessimistic system that may be quiescent most of the time, or may be invoked most of the time, depending what the customer decides now or later. I'm now very happy about this, whereas I certainly wasn't before. Thanks for the assistance!

Hmmm... I'm still against pessimistic locking, and for cases where the customer needs to get hold of a specific entity, I would go to semaphores instead, never at a "table", but at an "entity" level. This is something I always have a single API to handle.

Let me give you an example of what I mean. Let's assume you have an accounting app with invoices and payments on them. For simplicity, let's assume we have a single table for invoices and another for payments. The Invoices table holds its header data (customer, date, amount, current balance, and shipping date).

Also, a payment can be done involving several invoices, and the payment implies updating the invoice current balance.

If you use pessimistic locking, when a clerk is entering a payment (which could involve several invoices), you have to go on locking each one of them, but then you can't process the invoice shipment (updating the shipping date).

Of course this can be solved by normalization, but sometimes you can't do that. Bear with me for this example...

Now, I can have a Semaphores table where I can "reserve" operations, not tables or records, entering for each invoice selected for payment something like:

"ACC-REC INV 0007676", "JRYAN", "2007-04-26 09:48:33"

The first part can look very arbitrary, but it is the serialization of some properties the API takes on it's own. The string I used is something I just made up. The actual meaning of this record is something like "Invoice 7676 is reserved for Account Receivable operations by John Ryan at this time". The API call for this reservation checks whether there is a previous and still valid reservation for this operation. If there is nothing, it places its own reservation, and if it fails, you can query the API to tell your user who is holding the data and since when. This has proven to be great for my users.

As you may guessed, there is a time-to live mechanism, and previous to committing your changes you can check whether someone stole you a reservation (you can check if that happened at any time, indeed). But if nobody did, you can still go on even if the "guaranteed reservation" time expired.

In this scenario, you can access the same invoice to do two completely different operations without they colliding. And of course, you can try having a complete reserve for other operations. To void an invoice, for example, you need to avoid any other operation on it.

All this is done with no actual locks on the database. DB locks are very resource consuming and of course can cause unexpected deadlocks depending on the isolation level you are using, etc. Very difficult and tricky stuff to debug and optimize in a high concurrency application.

Hope this gives you more food for thought. :-)