Optimistic and Pessimistic Transactions

For transactions you have the choice, how long the transaction should last. The diagram illustrates this:

The pessimistic transaction includes every partial operation from reading the data until saving it. The optimistic transaction includes only the operations that are necessary to store the data back to the database.

Pessimistic Transactions

If several users work with the same system and a lot of simultaneous processes access the database, you might be tempted to establish a rigid transaction protection with pessimistic transactions. But that’s not the best way to cope with this situation. Mostly, a lot of time passes between reading and saving the data, especially if user input comes into play. Pessimistic transactions together with a corresponding isolation level lock the data; the database cannot access it and raises an exception. Pessimistic transactions are very expensive in terms of processing time. Generally more data are blocked than what is really needed for the transaction. Because of that, pessimistic transactions are unsuitable for web sites or other systems where a concurrent access of many users is expected.

Optimistic Transactions

Optimistic transactions only span the period of time where changes are written back to the database. This happens very fast so optimistic transactions are the method of choice for highly frequented data sources. The major drawback: The data can be loaded simultaneously by different users and the database may become inconsistent.

The solution

A working solution for web sites and frequently used data sources are optimistic transactions combined with collision detection. Collision detection prevents a data row from being overwritten if it was changed by another user since the last read operation. NDO includes a mechanism for collision detection that is based on Guids. Details can be found in the chapter Collision Detection.

Alternatively you can create your own locking mechanism in your application. At the application level you know exactly which data rows you need to protect and how long the protection should last. For example the starting point may be when an object is loaded and displayed in a form and the ending point when the Save or Cancel buttons are pressed. Before reading the object or object tree, you could set a lock entry in a specific table if the object or object tree is not already locked by another user. Checking and setting the lock can be accomplished in a separate transaction which is very fast and thus can be protected with a rigid isolation level. As soon as the lock is set the protected objects can be read and changed. Another transaction unlocks the data at the end. If it is not possible to set the lock (because another user works on the same data) a message would notify the user and ask him to try the transaction again later.