SQL Lock

Exclusive

• Tables

• Rows

• Entries in the database catalog

The system sets exclusive locks for all SQL statements that change data, such as INSERT, UPDATE, or DELETE.

Other transactions have neither read nor write access to the locked database object. This means that other users cannot access the locked database object.

If another transaction also tries to set an exclusive lock, a lock collision occurs (the lock request is rejected).

Share

• Tables

• Rows

• Entries in the database catalog

Other transactions have read access to the locked database object. They can still set share locks for the database object. However, other transactions cannot set exclusive locks for the database object and therefore cannot change it.

Optimistic

• Rows

An optimistic lock keeps track of changes to a database object. This enables database applications to determine whether the data that they have read while setting the lock is still up-to-date, or whether they must read it again.

Other transactions can still set share, exclusive, or optimistic locks for the locked database object.

Before a transaction can change a row for which it has set an optimistic lock, the system checks whether the row has been changed by another user since the optimistic lock was set.

• If the row has not been changed, the system converts the optimistic lock into an exclusive lock, and then makes the changes.

• If the row has been changed, the system rejects the change and releases the optimistic lock.

An optimistic lock is only useful in isolation levels 0, 1, 10, and 15.