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 in the following categories:
(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 it is manipulating data, and other transactions cannot process data for those rows at the same time. Row-level locks consume the least amount of data resources, allowing other transactions to manipulate other data in the same table during transaction processing.
(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 are not 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 table locks are used, the wait time for other transactions is delayed and system concurrency is reduced.
(4) A database lock that prevents any transactions and users from accessing the database. 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