"Go" in MySQL row-level locks, table-level locks, page-level locks

In computer science, a lock is a synchronization mechanism used to forcibly restrict access to resources when executing multiple threads, which is used to guarantee the satisfaction of mutex requirements in concurrency control.

As described in the lock mechanism of the database, the database locks can be divided into row-level locks (InnoDB engines), table-level locks (MyISAM engines), and page-level locks (BDB engines) in the DBMS according to the granularity of the locks.

Row-level Locks

A row-level lock is the most granular lock in MySQL that locks only on the row of the current operation. Row-level locks can greatly reduce the conflict of database operations. The lock granularity is minimal, but the cost of locking is the largest. Row-level locks are divided into 共享锁 and 排他锁 .

Characteristics

The cost is big, locking is slow; there is a deadlock, the lock granularity is minimal, the probability of lock conflict is the lowest, and the concurrency is the highest.

Table-Level Locks

Table-level lock is the largest lock in MySQL, which indicates that the whole table of the current operation is locked, it is simple, the resource consumption is low, and is supported by most MySQL engines. The most commonly used MyISAM and INNODB support table-level locking. Table-level locking is divided into 表共享读锁 (shared lock) and 表独占写锁 (exclusive).

Characteristics

The cost is small, lock fast, no deadlock, lock granularity is high, the probability of lock conflict is highest, and the concurrency is the lowest.

Page-level Locks

Page-level locks are a lock in MySQL that has a lock granularity between row-level and table-level locks. Table-level lock speed is fast, but conflicts are many, row-level conflict is small, but slow. So we took a compromised page level and locked a contiguous set of records at once. BDB support page-level locks

Characteristics

Overhead and lock times are bounded between table and row locks, deadlock occurs, lock granularity bounds between table and row locks, and concurrency is common

The lock mechanism of MySQL common storage engine

MyISAM and memory with table-level lock (Table-level locking)

BDB with page lock (page-level locking) or table-level lock, default to page lock

InnoDB supports row-level locks (row-level locking) and table-level locks, which are row-level locks by default

Row locks and table locks in InnoDB

As mentioned earlier, both row and table locks are supported in the InnoDB engine, so when will the entire table be locked, or only one line locked?

InnoDB row locks are implemented by locking the index entries on the index, which is different from Oracle, which is achieved by locking the corresponding data rows in the data block. InnoDB This type of row lock implementation is characterized by the fact that InnoDB uses row-level locks only if the data is retrieved by index criteria, otherwise INNODB will use a table lock!

In practice, it is important to pay special attention to this feature of the InnoDB row lock, otherwise, it may lead to a lot of lock conflicts, which can affect the concurrency performance.

• InnoDB does use a table lock instead of a row lock when querying without an index condition.
• Since the MySQL row lock is for the index plus lock, not for the record plus the lock, so although it is access to the record, but if you use the same index key, there will be a lock conflict. Be aware of this when applying design.
• When a table has multiple indexes, different transactions can lock different rows with different indexes, and InnoDB uses row locks to lock the data, whether it is using a primary key index, a unique index, or a normal index.
• Even if an indexed field is used in the condition, but whether the index is used to retrieve the data is determined by the cost of MySQL judging the different execution plans, if MySQL thinks the full table sweep is more efficient, such as for some very small tables, it will not use the index, in which case the InnoDB will use table locks instead of row locks. Therefore, when parsing a lock conflict, don't forget to check the SQL execution plan to verify that the index is actually used.

Row-level locks and deadlocks

There is no deadlock in MyISAM because MyISAM always gets all the locks needed, either all or all. In the InnoDB, the lock is gradually obtained, resulting in the possibility of deadlock.

In MySQL, a row-level lock is not a direct lock record, but a lock index. Indexes are primary key index and non-primary key index Two, if a SQL statement operation primary Key index, MySQL will lock the primary key index, if a statement operation non-primary key index, MySQL will first lock the non-primary key index, and then lock the relevant primary key index. In the update, delete operation, MySQL not only locks all index records scanned by the Where condition, but also locks adjacent key values, known as Next-key locking.

When two transactions are executed simultaneously, one locks the primary key index and waits for other related indexes. Another lock has a non-primary key index, waiting for the primary key index. This will cause a deadlock to occur.

After a deadlock occurs, InnoDB can generally detect and cause one transaction to release the lock fallback, and the other to acquire the lock to complete the transaction.

There are several ways to avoid deadlocks, and here are just a few of the three common

1, if different programs will access multiple tables concurrently, as far as possible to agree to access the table in the same order, can greatly reduce the deadlock opportunity.

2, in the same transaction, as far as possible to lock all the resources needed to reduce the deadlock generation probability;

3, for very easy to generate deadlock in the business part, you can try to use the upgrade lock granularity, through table-level locking to reduce the probability of deadlock;

Resources

20.3.4 InnoDB Line Lock Implementation method

"Go" in MySQL row-level locks, table-level locks, page-level locks