Granularity of database Locks

The so-called granularity, that is, the degree of refinement. The greater the granularity of the lock, the lower the concurrency and the overhead; the smaller the lock granularity, the higher the concurrency and the less overhead.

The size of the lock is mainly the following types:

(1) Row lock, row lock is the smallest resource in granularity. A row lock is a transaction that locks one or more rows of data while the data is being manipulated, and other transactions cannot process data for those rows at the same time. Row-level locks consume the least amount of data resources, so during transaction processing, other transactions are agreed to manipulate other data in the same table.

(2) page lock, lock one page at a time. 25 row locks can be upgraded to a single page lock.

(3) Table lock, lock the entire table. When the entire data table is locked, other transactions will not be able to use the other data in this table. Using table locks can make the amount of data processed by the transaction large and use less system resources. However, when a table lock is used, the wait time for other transactions is delayed and system concurrency is reduced.

(4) Database lock to prevent access to this database regardless of the transaction and user. You can control the operation of the entire database.

Lock efficiency can be reduced by using table locks to reduce the use of locks to ensure efficiency.

Granularity of database Locks