[Database Transactions and locks] detailed six: Shared and exclusive locks in MySQL

Note: This article is reproduced from http://www.hollischuang.com/archives/923

Row-level locks, table-level locks, page-level locks are described in MySQL, row-level locks are the most granular lock in MySQL, and row-level locks can significantly reduce database operation conflicts. Row-level locks are divided into shared and exclusive locks, this article will detail the concept of shared locks and exclusive locks, how to use and precautions.

Shared Lock (Share 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 will be able to modify the data (to get an exclusive lock on the data) until all shared locks have been freed.

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

Usage

SELECT ... LOCK IN SHARE MODE;

After the query statement is incremented LOCK IN SHARE MODE , MySQL adds a shared lock to each row in the query result, and when no other thread uses an exclusive lock on any row in the query result set, the shared lock can be successfully requested, otherwise it will be blocked. Other threads can also read tables that use shared locks, and those threads are reading the same version of data.

Exclusive lock (EXclusive lock)

An exclusive lock is also called a write lock, and if the transaction T adds an exclusive lock to data A, other transactions can no longer block a with any type. A transaction that is granted an exclusive lock can read and modify data.

Usage

SELECT ... FOR UPDATE;

After the query statement is incremented FOR UPDATE , MySQL adds an exclusive lock to each row in the query result, and when no other thread uses an exclusive lock on any row in the query result set, it can successfully request an exclusive lock or it will be blocked.

Intent lock

InnoDB also has two table locks:

Intent shared Lock (IS): Indicates that a transaction is ready to join a shared lock on a data row, that is, a data row must be acquired before a shared lock

Intent exclusive Lock (IX): Similar to the above, indicates that the transaction is prepared to add an exclusive lock to the data row, stating that the transaction must obtain an IX lock on the table before a data row is added to the exclusive lock.

Intent locks are innodb automatically and do not require user intervention.

For INSERT, UPDATE, DELETE,INNODB automatically adds an exclusive lock (X) to the data involved, and for the general SELECT statement, the INNODB does not have any locks, and the transaction can display a shared or exclusive lock through the following statement.

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

Exclusive Lock:SELECT ... FOR UPDATE;

Resources:

MySQL table-level lock and row-level lock

The use of MySQL lock row level lock

The InnoDB of the lock mechanism in MySQL

[Database Transactions and locks] detailed six: Shared and exclusive locks in MySQL