Lock (DBMS)

By area of ​​effect, 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 - act on only one row of the database table, without limiting the manipulation of other rows of the table.

Granular blocking - affects the entire table or the entire page and all rows. A lock restricting the manipulation of a page of data in a table (a set of rows joined by a sign of shared storage) is sometimes called page locking .

Predicate locks act on the area bounded by predicates ^{[ specify ]} .

By severity

Joint blocking is imposed by a transaction on an object if the operation it performs is safe, that is, it does not change any data and has no side effects . At the same time, all transactions can perform the same type of operation on an object if a shared lock is imposed on it, usually this lock is used for read operations.

An exclusive lock is imposed by the transaction on the object in case 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.

By implementation logic

A pessimistic lock is imposed before the proposed data modification on all rows that such a modification allegedly affects. During the action of such a lock, data modification from third-party sessions is excluded; data from locked rows are available according to the transaction isolation level . Upon completion of the proposed modification, a consistent record of the results is guaranteed.

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