MySQL shared lock-exclusive lock

Turn InnoDB row-level locks

InnoDB Row-level locksCategory: Database 2013-03-13 16:40 1745 people read comments (0) favorite reports

Nnodb lock mode and lock method

InnoDB implements the following two types of row locks.

? Shared Lock (S): Allows a transaction to read one line, preventing other transactions from acquiring an exclusive lock on the same data set.

? Exclusive Lock (X): A transaction that allows an exclusive lock to update data, preventing other transactions from acquiring shared read and exclusive write locks of the same data set.

In addition, in order to allow row and table locks to coexist and implement a multi-granularity locking mechanism, INNODB also has two intent locks (Intention Locks) that are used internally, both of which are table locks.

? Intent shared Lock (IS): The transaction intends to add a row of shared locks to the data row, and the transaction must obtain the IS lock of the table before sharing it with a data row.

? Intent exclusive Lock (ix): The transaction intends to add an exclusive lock to the data row, and the transaction must obtain an IX lock on the table before adding an exclusive lock to the data row.

The compatibility of the above lock modes is shown in table 20-6.

Table 20-6 InnoDB row lock mode Compatibility List

Request Lock mode is compatible Current lock mode	X	Ix	S	Is
X	Conflict	Conflict	Conflict	Conflict
Ix	Conflict	Compatible	Conflict	Compatible
S	Conflict	Conflict	Compatible	Compatible
Is	Conflict	Compatible	Compatible	Compatible

If the lock mode of a transaction request is compatible with the current lock, INNODB grants the requested lock to the transaction and, conversely, if the two are incompatible, the transaction waits for the lock to be released.

The intent lock is innodb automatically and does not require user intervention. For update, Delete, and INSERT statements, InnoDB automatically adds an exclusive lock (X) to the data set involved, and InnoDB does not add any locks for the normal SELECT statement, and the transaction can be displayed to the recordset with shared or exclusive locks.

• Shared Lock (S): SELECT * FROM table_name WHERE ... LOCK in SHARE MODE.

• Exclusive Lock (X): SELECT * FROM table_name WHERE ... For UPDATE.

Use SELECT ... In SHARE mode, a shared lock is used primarily to confirm the existence of a row of records when data dependencies are needed, and to ensure that no one is doing an update or delete operation on the record. However, if the current transaction also requires an update to the record, it is most likely to cause deadlocks, and for applications that require an update operation after locking the row records, you should use SELECT ... The for Update method obtains an exclusive lock.

In the example shown in table 20-7, the use of select ... In SHARE mode locks up and then updates the record to see what happens, where the actor table's actor_id field is the primary key.

Table 20-7 Shared lock example for INNODB storage engine

Session_1	Session_2
mysql> Set autocommit = 0; Query OK, 0 rows Affected (0.00 sec) Mysql> Select Actor_id,first_name,last_name from actor where actor_id = 178; +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 178 | LISA | MONROE | +----------+------------+-----------+ 1 row in Set (0.00 sec)	mysql> set autocommit = 0; Query OK, 0 rows Affected (0.00 sec)   Mysql> Select Actor_id,first_name,last_name from actor where actor_id = 178; +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 178      | lisa       | monroe   | +----------+------------+-----------+ 1 row in Set (0.00 sec)
Current session to actor_id=178 record plus share mode  shared lock: Mysql> Select Actor_id,first_ Name,last_name from actor where actor_id = 178 lock in share mode; +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 178      | lisa       | monroe   | +----------+------------+-----------+ 1 row in Set (0.01 sec)
	The other session can still query the record, and can also add share mode to the shared lock: Mysql> Select Actor_id,first_name,last_name from actor where actor_id = 178 lock in share mode; +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 178 | LISA | MONROE | +----------+------------+-----------+ 1 row in Set (0.01 sec)
The current session updates the locked record, waiting for the lock: Mysql> Update actor Set last_name = ' MONROE T ' where actor_id = 178; Wait
	Other sessions also update the record, which causes a deadlock to exit: Mysql> Update actor Set last_name = ' MONROE T ' where actor_id = 178; ERROR 1213 (40001): Deadlock found when trying to get lock; Try restarting transaction
After the lock is acquired, it can be successfully updated: Mysql> Update actor Set last_name = ' MONROE T ' where actor_id = 178; Query OK, 1 row affected (17.67 sec) Rows matched:1 changed:1 warnings:0

When using SELECT ... The for update locks up and then updates the record, as shown in table 20-8.

Table 20-8 Example of an exclusive lock for InnoDB storage engine

Session_1	Session_2
mysql> set autocommit = 0; Query OK, 0 rows Affected (0.00 sec)   Mysql> Select Actor_id,first_name,last_name from actor where actor_id = 178; +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 178      | lisa       | monroe   | +----------+------------+-----------+ 1 row in Set (0.00 sec)	mysql> set autocommit = 0; Query OK, 0 rows Affected (0.00 sec)   Mysql> Select Actor_id,first_name,last_name from actor where actor_id = 178; +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 178      | lisa       | monroe   | +----------+------------+-----------+ 1 row in Set (0.00 sec)
The current session of ACTOR_ID=178 's record plus a shared lock for update: Mysql> Select Actor_id,first_name,last _name from actor where actor_id = 178 for update; +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 178      | lisa       | monroe   | +----------+------------+-----------+ 1 row in Set (0.00 sec)
	Other sessions can query the record, but cannot add a shared lock to the record, waiting to get the lock: MySQL > select Actor_id,first_name,last_name from actor where actor_id = 178; +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 178      | lisa       | monroe   | +----------+------------+-----------+ 1 row in Set (0.00 sec)   Mysql> Select Actor_id,first_name,last_name from actor where actor_id = 178 for update; Wait
The current session can update the locked record and release the lock after the update: Mysql> Update actor Set last_name = ' MONROE T ' where actor_id = 178; Query OK, 1 row Affected (0.00 sec) Rows matched:1 changed:1 warnings:0 Mysql> commit; Query OK, 0 rows affected (0.01 sec)
	The other session gets the lock and gets the records submitted by the other session: Mysql> Select Actor_id,first_name,last_name from actor where actor_id = 178 for update; +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 178 | LISA | MONROE T | +----------+------------+-----------+ 1 row in Set (9.59 sec)

MySQL shared lock-exclusive lock