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

The Innodb storage engine implements two kinds of row-level locks:

L shared lock

L exclusive lock

The compatibility between the two locks is shown in the following figure

Shared lock

Exclusive lock

Shared lock

Compatible

Incompatible

Exclusive lock

Incompatible

Incompatible

It is easier to understand about row-level locks. Let's introduce table-level intention locks.

The Innodb storage engine supports multi-granularity locking, in other words, allowing transactions to hold locks at both the table and row levels. An intention lock is a table-level lock that is maintained by the storage engine itself and does not require manual user command intervention. If a transaction wants to add row-level shared locks to several rows of data in a table, it needs to first add an intentional shared lock (IS) at the table level; if a transaction wants to add row-level exclusive locks to several rows of data in a table, it needs to add an intentional exclusive lock (IX) at the table level. So what's the point of these two table-level locks?

For example, there is such a situation. A session An opens the following transaction, adds an exclusive lock on top of a row in the table, and does not commit.

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > SELECT * FROM t_compact WHERE C1 ='a 'FOR UPDATE

+-+

| | C1 | c2 | c3 |

+-+

| | a | aa | aaa |

+-+

1 row in set (0.00 sec)

Session B needs to add a read lock to the table, such as lock tables t_compact read;, then add a shared lock at the table level. One thing you need to confirm before locking is that there are no exclusive locks on all rows in the table, otherwise there will be a conflict between row locks and table locks. If there is no table-level intention lock, session B needs to go to each row to check if there is a row-level exclusive lock, so the intention table-level lock is used to shorten the checking process. It changes the exclusive lock for each row to just check a table-level lock.

After there is an intention lock, the whole process becomes like this: session A needs to add a table-level intention exclusive lock before locking a row with an exclusive lock, and then add a row-level exclusive lock on the row that needs to be locked after success. At this time, session B needs to add a shared lock at the table level, and it finds that an intention exclusive lock already exists on the table, so session B needs to wait if there is already a row-level exclusive lock on the data row in the table.

Table-level intention locks are fully compatible with each other.

IS

IX

IS

Compatible

Compatible

IX

Compatible

Compatible

The following columns can help you understand that table-level intention locks are compatible with each other.

It is still the table t_compact. Notice that there is a primary key on the table.

Mysql > show create table t_compact\ G

* * 1. Row *

Table: t_compact

Create Table: CREATE TABLE `t _ compact` (

`c1` varchar (10) NOT NULL

`c2` char (10) DEFAULT NULL

`c3` varchar (10) DEFAULT NULL

PRIMARY KEY (`c1`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

Session A, row-level exclusive lock on one line

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > select * FROM t_compact WHERE C1 ='a 'FOR UPDATE

+-- +

| | C1 | c2 | c3 |

+-- +

| | a | aa | aaa |

+-- +

1 row in set (0.00 sec)

Session B, add a row-level exclusive lock on the other line. Because of session A, the intention exclusive lock already exists on the table, and session B also needs to add the intention exclusive lock to the list. Because of the compatibility of the intention lock, the intention exclusive lock can also be added. Next, check if there is a row-level exclusive lock on the C1 ='b' line. After checking that there is no row-level exclusive lock, then the C1 ='b' line-level exclusive lock will not be blocked.

However, if session B also adds a row-level exclusive lock on C1 ='a', then after adding the intention exclusive lock, checking the row-level exclusive lock will find that it already exists and will be blocked.

Therefore, this compatibility helps to improve concurrency.

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > select * FROM t_compact WHERE C1 ='b' FOR UPDATE

+-- +

| | C1 | c2 | c3 |

+-- +

| | b | bb | bbb |

+-- +

1 row in set (0.00 sec)

Integrate the compatibility of row-level locks and intention table-level locks

S

X

IS

IX

S

Compatible

Incompatible

Compatible

Incompatible

X

Incompatible

Incompatible

Incompatible

Incompatible

IS

Compatible

Incompatible

Compatible

Compatible

IX

Incompatible

Incompatible

Compatible

Compatible

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.