Introduction to locks in MYSQL

As introduced in the database locking mechanism, 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.

Row-level lock

Row-level lock is the most granular lock in Mysql, which means that only the row of the current operation is locked. Row-level locks can greatly reduce conflicts in database operations. Its 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

The overhead is high and locking is slow; deadlocks may occur; the locking granularity is the smallest, the probability of lock conflicts is the lowest, and the concurrency is the highest.

Table-level lock

Table-level lock is the lock with the largest locking granularity in MySQL. It means locking the entire table of the current operation. It is simple to implement, consumes less resources, and is used Supported by most MySQL engines. The most commonly used MYISAM and INNODB support table-level locking. Table-level locks are divided into table shared read locks (shared locks) and table exclusive write locks (exclusive locks).

Features

It has low overhead and fast locking; no deadlock will occur; the locking granularity is large, the probability of lock conflict is the highest and the concurrency is the lowest.

Page-level lock

Page-level lock is a kind of lock in MySQL whose locking granularity is between row-level lock and table-level lock. Table-level locks are fast but have many conflicts. Row-level locks have few conflicts but are slow. So a compromised page level was adopted, locking a group of adjacent records at a time. BDB supports page-level locks

Features

The cost and locking time are bounded between table locks and row locks; deadlocks will occur; locking granularity is bounded between table locks and row locks. Concurrency is average

MySQL commonly used storage engine locking mechanism

MyISAM and MEMORY use table-level locking (table-level locking)

BDB uses page locking (page -level locking) or table-level lock, the default is page lock

InnoDB supports row-level locking (row-level locking) and table-level lock, the default is row-level lock

---

Row locks and table locks in Innodb

As mentioned earlier, the Innodb engine supports both row locks and table locks, so when will the entire lock be locked? With a table, when or only one row should be locked?

InnoDB row lock is implemented by locking the index entry on the index. This is different from MySQL and Oracle, which is implemented by locking the corresponding data row in the data block. This row lock implementation feature of InnoDB means that InnoDB uses row-level locks only when data is retrieved through index conditions. Otherwise, InnoDB will use table locks!

In practical applications, special attention should be paid to this feature of InnoDB row locks, otherwise it may cause a large number of lock conflicts, thus affecting concurrency performance.

• When querying without index conditions, InnoDB does use table locks instead of row locks.

• Since MySQL's row lock is for the index, not for the record, although records of different rows are accessed, if the same index key is used, the Lock conflicts will occur. Please pay attention to this when designing your application.

• When a table has multiple indexes, different transactions can use different indexes to lock different rows. In addition, whether using primary key index, unique index or ordinary index, InnoDB will Use row locks to lock data.

• Even if an index field is used in the condition, whether to use the index to retrieve data is determined by MySQL by judging the cost of different execution plans. If MySQL believes that the full table scan is more efficient High, for example, for some very small tables, it will not use indexes. In this case, InnoDB will use table locks instead of row locks. Therefore, when analyzing lock conflicts, don't forget to check the SQL execution plan to confirm whether the index is actually used.

---

Row-level locks and deadlocks

No deadlocks will occur in MyISAM, because MyISAM always obtains all the locks it needs at one time. Either satisfy them all or wait for them all. In InnoDB, locks are acquired gradually, causing the possibility of deadlock.

In MySQL, row-level locks do not directly lock records, but lock indexes. Indexes are divided into primary key indexes and non-primary key indexes. If a SQL statement operates on the primary key index, MySQL will lock 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 it. Related primary key index. During UPDATE and DELETE operations, MySQL not only locks all index records scanned by the WHERE condition, but also locks adjacent key values, which is so-called next-key locking.

When two transactions are executed at the same time, one locks the primary key index and is waiting for other related indexes. The other locks the non-primary key index and is waiting for the primary key index. A deadlock will occur.

After a deadlock occurs, InnoDB can generally detect it and make one transaction release the lock and roll back, and another acquire the lock to complete the transaction.

---

There are many ways to avoid deadlock. Here are only three common ones

1. If different programs will access multiple tables concurrently, try to agree on the same order. Accessing the table can greatly reduce the chance of deadlock.

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

3. For business parts that are very prone to deadlocks, you can try to use upgraded locking granularity to reduce the probability of deadlocks through table-level locking.

Row-level locks have the finest locking granularity in Mysql. A kind of lock, row-level lock can greatly reduce conflicts in database operations. Row-level locks are divided into shared locks and exclusive locks. This article will introduce the concepts, usage and precautions of shared locks and exclusive locks in detail.

Shared Lock (Share Lock)

Shared lock, also known as read lock, is a lock created by a read operation. Other users can read the data concurrently, but no transaction can modify the data (acquire an exclusive lock on the data) until all shared locks have been released.

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

Usage

SELECT ... LOCK IN SHARE MODE;

Add LOCK IN SHARE MODE after the query statement, Mysql will add a share to each row in the query result Lock, when no other thread uses an exclusive lock on any row in the query result set, it can successfully apply for a shared lock, otherwise it will be blocked. Other threads can also read tables using shared locks, and these threads read the same version of data.

Exclusive lock (eXclusive Lock)

Exclusive lock is also called write lock. If transaction T adds an exclusive lock to data A, other transactions cannot add any type of blockade to A. . Transactions granted exclusive locks can both read and modify data.

Usage

SELECT ... FOR UPDATE;

Add FOR UPDATE after the query statement. Mysql will add an exclusive lock to each row in the query result. If there is no When other threads use an exclusive lock on any row in the query result set, they can successfully apply for an exclusive lock, otherwise they will be blocked.

Intention lock

Intention lock is a table-level lock, which is mainly designed to reveal the type of lock that will be requested for the next row in a transaction. Two table locks in InnoDB:

Intention shared lock (IS): Indicates that the transaction is preparing to add a shared lock to the data row, which means that before adding a shared lock to a data row, the IS lock of the table must first be obtained

Intentional exclusive lock (IX): Similar to the above, it means that the transaction is preparing to add an exclusive lock to the data row, indicating that the transaction must first obtain the IX lock of the table before adding an exclusive lock to a data row.

Intention locks are automatically added by InnoDB and do not require user intervention.

For insert, update, and delete, InnoDB will automatically add exclusive locks (X) to the data involved; for general Select statements, InnoDB will not add any locks, and transactions can be displayed with shared locks through the following statements Or exclusive lock.

Shared lock: SELECT ... LOCK IN SHARE MODE;

Exclusive lock: SELECT ... FOR UPDATE;

Related recommendations:

The connection between mysql locks and indexes

MySQL deadlock and log analysis

Methods to implement MySQL statement locking

The above is the detailed content of Introduction to locks in MYSQL. For more information, please follow other related articles on the PHP Chinese website!