MySQL supports row-level locks, table-level locks, and page-level locks.

MySQL supports row-level locks, table-level locks, and page-level locks.

In computer science, a lock is a synchronization mechanism used to forcibly restrict resource access when multiple threads are executed, that is, to ensure that mutex requirements are met in concurrency control.

In DBMS, database locks can be divided into row-level locks (INNODB engine), table-level locks (MYISAM engine), and page-level locks (BDB engine) according to the lock granularity ).

1. Row-Level Lock

Row-level locks are the most fine-grained locks in Mysql, indicating that only the current row is locked. Row-level locks can greatly reduce database operation conflicts. The minimum lock granularity, but the maximum lock overhead. Row-level locks are divided into shared locks and exclusive locks.

Features

High overhead, slow locking, deadlock, minimum lock granularity, the lowest probability of lock conflict, and the highest concurrency.

Ii. Table-Level Lock

Table-level locks are the most granular locks in MySQL. They indicate locking the entire table of the current operation. They are easy to implement, consume less resources, and are supported by most MySQL engines. The most commonly used MYISAM and INNODB support table-level locking. Table-level locks are divided into tablesShared read lock)AndExclusive table write lock (exclusive lock).

Features

Small overhead, fast locking; no deadlock; large lock granularity, the highest probability of lock conflict, the lowest concurrency.

3. Page lock

Table-level locks are a kind of locks in MySQL with a granularity between Row-level locks and table-level locks. Table-level locks are fast, but have many conflicts, with fewer row-level conflicts, but are slow. Therefore, the compromised page level is used to lock a group of adjacent records at a time. BDB supports page-level locks

Features

Overhead and lock time are between table locks and row locks. Deadlocks may occur. The lock granularity is between table locks and row locks. Concurrency is average.

Lock Mechanism of common MySQL storage engines

• MyISAM and MEMORY use table-level locking)
• BDB uses page-level locking or table-level locking. The default value is page lock.
• InnoDB supports row-level locking and table-level locking. The default value is row-level locking.

4. Row locks and table locks in Innodb

As mentioned above, the Innodb Engine supports both row and table locks. When will the entire table be locked or only one row be locked?

InnoDB row locks are implemented by locking the index items on the index. This is different from Oracle in MySQL. The latter is implemented by locking the corresponding data rows in the data block. The implementation of InnoDB row locks means that InnoDB uses row-level locks only when data is retrieved through index conditions. Otherwise, InnoDB uses table locks!

In practical applications, pay special attention to the InnoDB row lock feature. Otherwise, it may lead to a large number of lock conflicts, thus affecting concurrent performance.

Row-level locks are based on indexes. If an SQL statement does not use an index, row-level locks are not used, and table-level locks are used. The disadvantage of Row-level locks is that the request requires a large number of lock resources, so the speed is slow and the memory consumption is high.

5. Row-level locks and deadlocks

MyISAM does not produce deadlocks, because MyISAM always obtains all the required locks at a time, either all satisfied or all waiting. In InnoDB, locks are obtained gradually, resulting in a deadlock.

In MySQL, row-level locks do not directly lock records, but lock indexes. Indexes can be classified into primary key indexes and non-primary key indexes. If an SQL statement operates on a primary key index, MySQL locks the primary key index. If a statement operates on a non-primary key index, mySQL will first lock the non-primary key index, and then lock the relevant primary key index. During the UPDATE and DELETE operations, MySQL not only locks all index records scanned by the WHERE condition, but also locks adjacent key values, that is, the so-called next-key locking.

When two transactions are executed at the same time, one locks the gradual index while waiting for other related indexes, and the other locks the non-primary key index while waiting for the primary key index. In this way, a deadlock occurs.

After a deadlock occurs, InnoDB can generally detect and release a transaction lock to roll back, and the other can obtain the lock to complete the transaction.

There are multiple ways to avoid deadlocks. Here we only introduce the common three methods, as shown below:

1. If different programs concurrently Access Multiple tables, try to agree to access the tables in the same order, which can greatly reduce the deadlock opportunity.

2. Try to lock all required resources at a time in the same transaction to reduce the probability of deadlock;

3. for businesses that are very prone to deadlock, you can try to use the upgrade lock granularity to reduce the probability of deadlock through table-level locking;

The above is all the content of this article, hoping to help you learn.

Articles you may be interested in:

• MySQL row-level locks, table-level locks, and page-level locks