Share the usage of shared locks and exclusive locks in MySQL

Among the row-level locks, table-level locks and page-level locks in MySQL, we have introduced that row-level locks are the most granular locks in MySQL. Row-level locks Locks 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 after the query statement SHARE MODE, MySQL will add a shared lock to each row in the query result set. 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 is also called write lock and exclusive lock. If transaction

T adds exclusive lock to data A , then other transactions can no longer 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 set. When no other thread uses an exclusive lock on any row in the query result set, it can successfully apply for an exclusive lock, otherwise it will be blocked. Intention Lock (Intention Lock)

Intention lock is a table-level lock. Its main design purpose is 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 a data row must be obtained before adding a shared lock.
• IS

  lock of the table;

Intention exclusive lock (IX): similar to the above, indicating that the transaction is preparing to add an exclusive lock to the data row, indicating that the transaction is in a data row Before adding an exclusive lock, you must first obtain the
• IX

  lock on the table.

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

For

INSERT

, UPDATE and DELETE, InnoDB will automatically add exclusive locks to the data involved; for general SELECT statement, InnoDB will not add any locks, transactions can explicitly add shared locks or exclusive locks through the following statements. Shared lock:

SELECT ... LOCK IN SHARE MODE;

Exclusive lock:

SELECT ... FOR UPDATE;

The above is the detailed content of Share the usage of shared locks and exclusive locks in MySQL. For more information, please follow other related articles on the PHP Chinese website!