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

This article mainly explains "the introduction of MySQL shared lock and exclusive lock". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "the introduction of MySQL shared lock and exclusive lock".

Shared lock and exclusive lock

InnoDB implements standard row-level locking where there are two types of locks, shared (S) locks and exclusive (X) locks.

InnoDB implements two standard row-level locks, namely shared locks (S locks) and exclusive locks (X locks):

A shared (S) lock permits the transaction that holds the lock to read a row. The shared lock (S lock) allows transactions that (currently) hold the lock to read the row.

An exclusive (X) lock permits the transaction that holds the lock to update or delete a row. Exclusive locks (X locks) allow transactions that (currently) hold the lock to update or delete the row.

If transaction T1 holds a shared (S) lock on row r, then requests from some distinct transaction T2 for a lock on row r are handled as follows:

A request by T2 for an S lock can be granted immediately. As a result, both T1 and T2 hold an S lock on r.

A request by T2 for an X lock cannot be granted immediately.

If transaction T1 has a shared lock on the r row record, another transaction T2 will be processed as follows when requesting a lock on the r row:

T2 request shared lock will be released immediately. In this way, both T1 and T2 transactions have shared locks for r rows.

T2 requests an exclusive lock and will not be released immediately.

If a transaction T1 holds an exclusive (X) lock on row r, a request from some distinct transaction T2 for a lock of either type on rcannot be granted immediately. Instead, transaction T2 has to wait for transaction T1 to release its lock on row r.

If transaction T1 has an exclusive lock on line r, another transaction T2 will not be released immediately no matter what kind of lock is requested on line r. Instead, transaction T2 must wait for transaction T1 to release the lock on the r line.

The compatibility of exclusive locks and shared locks is as follows

XSXConflictConflictSConflictCompatibleIntention Locks intention lock

InnoDB supports multiple granularity locking which permits coexistence of row locks and table locks. For example, a statement such asLOCK TABLES... WRITE takes an exclusive lock (an X lock) on the specified table. To make locking at multiple granularity levels practical, InnoDB uses intention locks. Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table. There are two types of intention locks:

An intention shared lock (IS) indicates that a transaction intends to set a shared lock on individual rows in a table.

An intention exclusive lock (IX) indicates that a transaction intends to set an exclusive lock on individual rows in a table.

InnoDB supports multi-granularity locks, which allow row and table locks to exist at the same time. For example, the 'LOCK TABLES... WRITE' declaration statement adds an exclusive lock (X lock) to the explicit table. For the practice of locking at a multi-granularity level, InnoDB uses intention locks. An intention lock is a table-level lock that indicates what type of lock (shared or exclusive) is next requested by a transaction on a row in the table (after applying for a table-level lock). There are two types of intention locks:

The intention shared lock (IS lock) indicates that a transaction intends (after acquiring the table lock) to set a shared lock (S lock) on individual rows in the table.

The intention exclusive lock (IX lock) indicates that a transaction intends (after acquiring the table lock) to set an exclusive lock (X lock) on individual rows in the table.

For example, SELECT... FOR SHARE sets an IS lock, and SELECT... FOR UPDATE sets an IX lock.

For example, SELECT... FOR SHARE will set an intention sharing lock (IS lock), SELECT... FOR UPDATE sets an intention exclusive lock.

The intention locking protocol is as follows:

Before a transaction can acquire a shared lock on a row in a table, it must first acquire an IS lock or stronger on the table.

Before a transaction can acquire an exclusive lock on a row in a table, it must first acquire an IX lock on the table.

The agreement of intention lock is as follows:

Before a transaction can acquire a shared lock for a row in a table, it must first acquire an intended shared lock or a stronger lock for the table.

Before a transaction can acquire an exclusive lock for a row in a table, it must first acquire an intention exclusive lock for the table.

Table-level lock type compatibility is summarized in the following matrix.

The compatibility of table-level locks is summarized as follows:

XIXSISXConflictConflictConflictConflictIXConflictCompatibleConflictCompatibleSConflictConflictCompatibleCompatibleISConflictCompatibleCompatibleCompatible

A lock is granted to a requesting transaction if it is compatible with existing locks, but not if it conflicts with existing locks. A transaction waits until the conflicting existing lock is released. If a lock request conflicts with an existing lock and cannot be granted because it would cause deadlock, an error occurs.

If a transaction requests a lock and the lock is compatible with an existing lock, the lock will be released. However, if the lock conflicts with an existing lock, it will not be released. The transaction will wait until the existing lock that causes the conflict is released. If a lock request conflicts with an existing lock and will not be released, because if released, it will cause a deadlock and an error will occur.

Intention locks do not block anything except full table requests (for example, LOCK TABLES... WRITE). The main purpose of intention locks is to show that someone is locking a row, or going to lock a row in the table.

The intention lock will not block anything except full table scans (such as LOCK TABLES. WRITE). The main purpose of the intention lock is to show that someone is locking a row in the table, or is about to lock that row.

Transaction data for an intention lock appears similar to the following in SHOW ENGINE INNODB STATUS and InnoDB monitor output:

TABLE LOCK table `test`.`t` trx id 10080 lock mode IX Note: at the execution level, when MySQL-Server submits a SQL statement to InnoDB, the intended lock is implemented by InnoDB itself, and the statement will eventually get S lock or X lock for the corresponding row. Thank you for your reading, the above is the content of "introduction of MySQL shared lock and exclusive lock". After the study of this article, I believe you have a deeper understanding of the introduction of MySQL shared lock and exclusive lock, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.