Optimistic and pessimistic locking strategies

In a traditional host-based or client/server application, you can enforce what is referred to as a pessimistic locking strategy. This means that your application always obtains an EXCLUSIVE-LOCK when it first reads any record that might be updated, to make sure that no other user tries to update the same record.

In a distributed application, this technique simply cannot work. If you read a single record or a set of records on the server, and pass them to a client session for display and possible update, your server-side session cannot easily hold locks on the records while the client is using them. When the server-side procedure ends and returns the temp-table of records to the client, the server-side record buffers are out of scope and the locks released. In addition, you would not want to maintain record locks for this kind of duration, as it would lead to likely record contention.

Instead, you need to adopt an optimistic locking strategy. This means that you always read records on the server with NO-LOCK, if they are going to be passed to another session for display or processing. When the other session updates one or more records and passes them back, presumably in another copy of the temp-table that sent the records to the client, the server-side procedure in charge of handling updates must:

1. Find again each updated record in the database with an EXCLUSIVE-LOCK, using its key or its RowID.
2. Verify that the record has not been changed by another user or at least verify that the current changes do not conflict with other changes.
3. Assign the changes to the database record.

If another user has changed the record, then the application must take appropriate action. This might involve rejecting the new changes and passing the other changes back for display, or otherwise reconciling the two sets of changes, depending on the application logic and the nature of the data.