Improving concurrency by using enhanced optimistic locking

When a record is selected for updating a table, a lock is acquired. This lock will be released only when the transaction ends. When this lock is acquired by a transaction, no other transaction can update or delete this record. In other words, the second transaction assumes that the first transaction may update the record and hence it waits until the lock is released. This is also known as pessimistic locking. The biggest downside of this type of locking is reduced concurrency.

To overcome such situations, optimistic locking has been introduced. Optimistic locking provides techniques that do not require locks to be held between selecting and updating/deleting the records. When optimistic locking is used, the application releases the lock immediately after the read operation. If optimistic locking is implemented, then the application assumes that this row will not be updated/deleted by other transactions and releases the lock. Before...