Optimistic and pessimistic locking

Pessimistic locking assumes that two or more users will often attempt to update the same record at the same time or is used when lost changes are not acceptable. Ususally a lock column is added to the row. A timestamp works well for reasons I will explain below.

Optimistic locking assumes that two or more users will rarely try to update the same record at the same time. The basic implementation is to store the state of the row in memory and on update, use that sate to check whether it was updated since it was read. If it has changed, the users updates are invalid and cannot be persisted. One way to do this is to set a last modified field (best when implemented by the DBMS.)

The main disadvantage to pessimistic locking is that even if a user never updates a record, having it locked prevents other users from updating it. If a user's system crashes or a record is otherwise left locked indefinitely, you have to provide a way to unlock the record. This feature can be abused by users so you may have to restrict access to it. Using a timestamp for the pessimistic lock helps users determine if the record is really being used by another user e.g. if the record has been locked for a week, it's probably safe to unlock the record. One more thing, you need to make sure users cannot update if their lock was broken.

The main disadvantage to optimistic locking should be obvious. However, it does not have the problem listed above and is much simpler to implement.