Blocking (DBMS) Pessimistic Optimistic Joint Exclusive Lowercase Granular

A lock (English lock ) in a DBMS is a mark about an object being captured by a transaction with limited or exclusive access in order to prevent collisions and maintain data integrity.

Classification of locks

By scope, locks are classified into lowercase, granular, and predicate. By severity, locks are divided into joint (English shared ) and exclusive (exclusive, English exclusive ). According to the logic of implementation, locks are divided into optimistic and pessimistic.

By scope

Line lock - only affect one row of the database table, without restricting manipulation of other rows of the table.

Granular blocking - affects the entire table or the whole page and all rows. A lock that limits the manipulation of a data page in a table (a set of rows, combined with a shared storage feature) is sometimes called a page locking .

Predicative locks affect the area bounded by predicates ^{[to clarify ]} .

By severity

A joint lock is imposed by a transaction on an object if the operation performed by it is safe, that is, it does not change any data and has no side effects. In this case, all transactions can perform an operation of the same type on an object, if a joint lock is imposed on it, usually such a lock is used for read operations.

An exclusive lock is imposed by a transaction on an object in the event that the operation performed by it changes the data. Only one transaction can perform a similar operation on an object if an exclusive lock is imposed on it. A lock cannot be imposed on an object if a joint lock has already been imposed on it.

According to the logic of implementation

A pessimistic lock is imposed before the intended modification of the data on all the lines that this modification presumably affects. The entire duration of such a lock prevents modification of data from third-party sessions, data from blocked rows is available according to the isolation level of the transaction. Upon completion of the proposed modification, a consistent record of the results is guaranteed.

Optimistic locking does not limit the modification of the processed data by third-party sessions, but before starting the proposed modification, it requests the value of some selected attribute of each of the data lines (usually the VERSION name and the integer type with the initial value 0 are used). Before writing modifications to the database, the value of the selected attribute is checked and, if it has changed, the transaction is rolled back or different collision resolution schemes are applied. If the value of the selected attribute has not changed - the modifications are made while changing the value of the selected attribute (for example, increment) to signal to other sessions that the data has changed.