A transaction can set an optimistic lock for a table row (data record). The database system informs the transaction of the current version number of the table row.
Each time a table row is changed, the database system increases the version number. By comparing the original and the current version number, database applications can determine whether the data read while setting the lock is still current or must be read again.
To get an optimistic lock, you have to request it explicitly with the LOCK statement. If an exclusive lock already exists for a database object, you cannot set an optimistic lock (lock collision). If an optimistic lock exists for a database object, other users can continue to set exclusive, shared, or additional optimistic locks.
Before a transaction can change a table row for which it has set an optimistic lock, the database system checks whether the table row has been changed by another user since the optimistic lock was set.
· If the table row has not been changed, the database system first converts the optimistic lock into an exclusive lock, and makes the changes.
· If another transaction has changed the table row in the meantime, the database system rejects the change and releases the optimistic lock.
An optimistic lock is only useful if one of the isolation levels 0, 1, 10, or 15 has been configured.