Shulou(Shulou.com)06/01 Report--

Editor to share with you what the mysql database locking mechanism is. I hope you will gain a lot after reading this article. Let's discuss it together.

Concurrency control

The task of concurrency control in database management system is to ensure that when multiple transactions access the same data in the database at the same time, the isolation and unity of transactions and the unity of the database will not be destroyed.

Blocking, timestamp, optimistic concurrency control and pessimistic concurrency control are the main technical means of concurrency control.

Lock

When concurrent transactions access a resource at the same time, it may lead to data inconsistency, so a mechanism is needed to serialize data access to ensure the consistency of database data. Locking is one of the mechanisms (recommended tutorial: MySQL tutorial)

Classification of locks

According to the operation, it can be divided into DML lock and DDL lock.

According to the lock granularity, it can be divided into table-level lock, row-level lock and page-level lock (mysql)

According to the lock level, it can be divided into shared lock and exclusive lock.

According to the locking mode, it can be divided into automatic lock and display lock.

According to the mode of use, it can be divided into optimistic lock and pessimistic lock.

DML locks are used to protect the integrity of data, including row-level locks (TX locks) and table-level locks (TM locks). DDL locks are used to protect the structure of database objects, such as tables, indexes, etc., including exclusive DDL locks, shared DDL locks, and interruptible parsing locks

Row level lock

Row-level locks are the finest locks in Mysql, indicating that locks are only applied to the rows of the current operation. Row-level locks can greatly reduce conflicts in database operations. The locking granularity is the smallest, but the locking overhead is also the largest. Row-level locks are divided into shared locks and exclusive locks

Features: high overhead, slow locking, deadlock; minimum lock granularity, lowest probability of lock conflict and highest concurrency

Table level lock

Table-level lock is the largest lock granularity in Mysql, which means locking the whole table of the current operation. It is easy to implement, low resource consumption, and is supported by most Mysql engines. Both MYISAM and INNODB, the most commonly used, support table-level locking. Table level locking is divided into table shared read lock (shared lock) and table exclusive write lock (exclusive lock).

Features: low overhead, fast locking; no deadlock; large lock granularity, the highest probability of lock conflict and the lowest concurrency

Page level lock

Page-level lock is a lock whose granularity is between row-level lock and table-level lock in Mysql. The speed of table-level lock is fast, but there are more conflicts, less row-level conflicts, but slow speed. The page-level lock is folded to lock an adjacent set of records. BDB supports page-level locks

Locking Mechanism of commonly used Storage engine in Mysql

MyISAM and MEMORY use table-level locks

BDB uses page-level lock or table-level lock. The default is page-level lock.

InnoDB supports row-level and table-level locks, which are defaulted to row-level locks

Row lock and table lock of InnoDB

Both row and table locks are supported in the InnoDB engine, so when will the whole table be locked and when will the row be locked?

InnoDB row locking is achieved by locking the index items on the index, unlike Oracle, which is achieved by locking the corresponding data rows in the data block. The implementation feature of row locks such as InnoDB means that InnoDB uses row-level locks only when data is retrieved through index conditions, otherwise InnoDb will use table locks

In practical application, we should pay attention to this feature of InnoDB row locks, otherwise it will easily lead to a large number of lock conflicts, thus affecting the concurrency performance.

When querying without index conditions, InnoDB uses table locks instead of row locks

Because the row lock of MySQL is a lock for index, not a lock for record, although the records of different rows are accessed, lock conflicts will occur if the keys of the same index are used.

When a table has multiple indexes, different transactions can use different indexes to lock different rows, and InnoDB will use row locks to lock data, whether it is a primary key index, a unique index, or a normal index.

Even if index fields are used in the condition, whether or not to use indexes to retrieve data is determined by Mysql by determining the cost of different row plans. If Mysql thinks full table scans are more efficient, for example, for very small tables, it will not use indexes, in which case InnoDB will use table locks instead of row locks. So when analyzing lock conflicts, don't forget to check SQL's execution plan

Row-level locks and deadlocks

MyISAM does not produce deadlocks, because MyISAM always gets all the locks you need at once, either satisfying them or waiting for them all. In InnoDB, the lock is acquired step by step, which leads to the possibility of deadlock

In MySQL, row-level locks are not direct lock records, but lock indexes. Indexes are divided into primary key index and non-primary key index. If a sql statement operates on a primary key index, MySQL will lock the primary key index; if a statement operates on a non-primary key index, MySQL will lock the non-primary key index first, and then lock the relevant primary key index. During the Update,delete operation, MySQL locks not only all index records scanned by where conditions, but also adjacent key values, the so-called next-key locking

Deadlock: when two transactions execute at the same time, one locks the primary key index and waits for other related indexes. The other locks the non-primary key index and is waiting for the primary key index. This leads to a deadlock.

After a deadlock occurs, InnoDB can generally detect and cause one transaction to release the lock and roll back, while the other acquires the lock to complete the transaction

Avoid deadlock

If different programs access multiple tables concurrently, try to agree to access the tables in the same order, which can greatly reduce the chance of deadlock.

In the same transaction, try to lock all the resources needed at once to reduce the probability of deadlock.

For business parts that are very prone to deadlocks, you can try to use upgrade locking granularity to reduce deadlocks through table-level locking

Shared lock and exclusive lock

Row-level lock is the most fine-grained lock in MySQL, and row-level lock can greatly reduce the conflict of database operations. Row-level locks are divided into shared locks and exclusive locks

1. Shared lock

A shared lock, also known as a read lock, is a lock created by a read operation. Other users can read data concurrently, but no transaction can modify the data until all shared locks have been released.

If transaction T adds a shared lock to data A, then other transactions can only add a shared lock to A, not an exclusive lock. Transactions that are allowed to share locks can only read data and cannot modify data

If transaction T modifies data An after adding a shared lock to data A, other transactions will not be able to acquire a shared lock; similarly, if multiple transactions acquire a shared lock on the same data, no transaction can modify the data

Usage: SSELECT... LOCK IN SHARE MODE

Adding LOCK IN SHARE MODE,Mysql after the query statement adds a shared lock to every row in the query result. When no other thread uses an exclusive lock on any row in the query result set, you can successfully apply for a shared lock, otherwise it will be blocked. Other threads can also read tables that use shared locks, and these threads read the same version of the data

two。 Exclusive lock

An exclusive lock is also called a write lock. If transaction T adds an exclusive lock to data A, other transactions can no longer impose any type of lock on A. A transaction that acquires an exclusive lock can both read and modify data

Usage: SELECT... FOR UPDATE . Adding FOR UPDATE,MySQL after the query statement adds an exclusive lock to every row in the query result. When no other thread uses an exclusive lock on any row of the query result set, you can successfully apply for an exclusive lock, otherwise it will be blocked.

3. Intention lock

An intention lock is a table-level lock designed to reveal the type of lock that the next row will be requested in a transaction. Two table locks in InnoDB:

Intention shared lock (IS): indicates that the transaction is ready to add a shared lock to a data row, that is, a data row must acquire the IS lock of the table before adding a shared lock

Intention exclusive lock (IX): indicates that the transaction is ready to add an exclusive lock to the data row, indicating the IX lock of the table that the transaction must go to before adding the exclusive lock to the data row.

The intention lock is automatically added by InnoDB without user intervention.

Summary

For insert,update,delete,InnoDB, exclusive locks are automatically added to the data involved; for general Select statements, InnoDB does not add any locks, and transactions can explicitly add shared or exclusive locks through the following statements

Shared lock: select... LOCK IN SHARE MODE

Exclusive lock: SELECT. FOR UPDATE

After reading this article, I believe you have a certain understanding of mysql database locking mechanism, want to know more related knowledge, welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.