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

The locking mechanism of InnoDB:

The database is used to support better concurrency and provide data integrity and consistency. InnoDB is a storage engine that supports locks. The types of locks are: shared lock (S), exclusive lock (X), intended shared lock (IS), intentional exclusive lock (IX). To support better concurrency, InnoDB provides unlocked reads: you don't have to wait for the lock on the access row to be released, reading a snapshot of the row. This method is implemented through a close-up of InnoDB: MVCC.

Lock classification of InnoDB:

Record Lock: row lock: row lock on a single row record

Gap Lock: gap lock that locks a range, but does not include the record itself

Next-Key Lock:Gap+Record Lock, lock a range, and lock the record itself

No index + RC/RR

When an unindexed field is updated (at the RR level), all records are locked by locking the primary key, and the RC level does not lock all records.

Build tables and initialize data:

Mysql-uroot-pUSE test;DROP TABLE IF EXISTS tactile nonebot create TABLE `tnone` (`id` int (11) NOT NULL, `mem_ id` int (11) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;INSERT INTO t_none VALUES (1), (3), (5), (9), (11)

REPEATABLE-READ (RR) default level

Session A

Session B

Root@localhost [zjkj]: 10:53:18 > prompt A > >

PROMPT set to'A > >'

A > > select @ @ session.tx_isolation

Root@localhost [(none)]: 11:02:58 > prompt B > >

PROMPT set to'B > >'

B > > select @ @ session.tx_isolation

A > > begin

Query OK, 0 rows affected (0.00 sec)

B > > begin

Query OK, 0 rows affected (0.00 sec)

A > > select * from t_none

+-+ +

| | id | mem_id |

+-+ +

| | 1 | 1 |

| | 3 | 3 |

| | 5 | 5 |

| | 9 | 9 |

| | 11 | 11 |

+-+ +

5 rows in set (0.00 sec)

B > > select * from t_none

+-+ +

| | id | mem_id |

+-+ +

| | 1 | 1 |

| | 3 | 3 |

| | 5 | 5 |

| | 9 | 9 |

| | 11 | 11 |

+-+ +

5 rows in set (0.00 sec)

A > > select * from t_none where mem_id=3 for update

+-+ +

| | id | mem_id |

+-+ +

| | 3 | 3 |

+-+ +

1 row in set (0.01 sec)

B > > insert into t_none values (2jue 2)

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

B > > delete from t_none where id=9

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Show engin inondb status partial output:

-

TRANSACTIONS

-

Trx id counter 10661

Purge done for trx's n:o

< 10659 undo n:o < 0 state: running but idle History list length 351 Total number of lock structs in row lock hash table 2 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 10588, not started MySQL thread id 4, OS thread handle 0x7f6f5085c700, query id 339 localhost root init show engine innodb status ---TRANSACTION 10660, ACTIVE 17 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 11, OS thread handle 0x7f6f508de700, query id 338 localhost root update insert into t_none values(2,2) ------- TRX HAS BEEN WAITING 17 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 68 page no 3 n bits 72 index `PRIMARY` of table `test`.`t_none` trx id 10660 lock_mode X locks gap before rec insert intention waiting 结论：通过上面很容易的看到，没有通过索引for update时，当进行增删改都会锁住，MySQL内部会通过基于锁默认主键方式，对所有记录加X锁。 下面是RC级别的实验 Read Committed级别（RC） Session A Session B A>

> set @ @ session.tx_isolation= "read-committed"

Query OK, 0 rows affected (0.00 sec)

B > > set @ @ session.tx_isolation= "read-committed"

Query OK, 0 rows affected (0.00 sec)

A > > select @ @ session.tx_isolation

+-- +

| | @ @ session.tx_isolation |

+-- +

| | READ-COMMITTED |

+-- +

1 row in set (0.00 sec)

B > > select @ @ session.tx_isolation

+-- +

| | @ @ session.tx_isolation |

+-- +

| | READ-COMMITTED |

+-- +

1 row in set (0.01 sec)

A > > begin

Query OK, 0 rows affected (0.00 sec)

B > > begin

Query OK, 0 rows affected (0.00 sec)

A > > select * from t_none where mem_id=3 for update

+-+ +

| | id | mem_id |

+-+ +

| | 3 | 3 |

+-+ +

1 row in set (0.01 sec)

B > > insert into t_none values (2jue 2)

Query OK, 1 row affected (0.01sec)

B > > select * from t_none

+-+ +

| | id | mem_id |

+-+ +

| | 1 | 1 |

| | 2 | 2 |

| | 3 | 3 |

| | 5 | 5 |

| | 9 | 9 |

| | 11 | 11 |

+-+ +

6 rows in set (0.00 sec

A > > rollback

Query OK, 0 rows affected (0.00 sec)

B > > rollback

Query OK, 0 rows affected (0.00 sec)

Conclusion: transaction B can be added, deleted or modified at the RC level (except for the locked record itself).

Non-unique index + RR/RC

At the RR level, InnoDB adds Gap Lock (that is, locks an interval) to non-unique indexes, but none at the RC level.

Construct initialization tables and data:

Mysql-uroot-pUSE test;DROP TABLE IF EXISTS tweeidxmitCreate TABLE `tidx` (`id` int (11) NOT NULL, `mem_ id` int (11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_mem_ id` (`mem_ id`) ENGINE=InnoDB;INSERT INTO t_idx VALUES (1Mague 1), (3pjue 3), (5jue 5), (9pr 9), (11pc11)

REPEATABLE-READ (RR) default level (RR mode)

Session A

Session B

Root@localhost [(none)]: 06:01:59 > use test

Root@localhost [zjkj]: 10:53:18 > prompt A > >

PROMPT set to'A > >'

Root@localhost [(none)]: 06:01:59 > use test

Root@localhost [(none)]: 11:02:58 > prompt B > >

PROMPT set to'B > >'

A > > select @ @ session.tx_isolation

+-- +

| | @ @ session.tx_isolation |

+-- +

| | REPEATABLE-READ |

+-- +

1 row in set (0.00 sec)

B > > select @ @ session.tx_isolation

+-- +

| | @ @ session.tx_isolation |

+-- +

| | REPEATABLE-READ |

+-- +

1 row in set (0.02 sec)

A > > begin

Query OK, 0 rows affected (0.00 sec)

B > > begin

Query OK, 0 rows affected (0.00 sec)

A > > select * from t_idx

+-+ +

| | id | mem_id |

+-+ +

| | 1 | 1 |

| | 3 | 3 |

| | 5 | 5 |

| | 9 | 9 |

| | 11 | 11 |

+-+ +

5 rows in set (0.04 sec)

B > > select * from t_idx

+-+ +

| | id | mem_id |

+-+ +

| | 1 | 1 |

| | 3 | 3 |

| | 5 | 5 |

| | 9 | 9 |

| | 11 | 11 |

+-+ +

5 rows in set (0.00 sec)

A > > select * from t_idx where mem_id=3 for update

+-+ +

| | id | mem_id |

+-+ +

| | 3 | 3 |

+-+ +

1 row in set (0.05sec)

B > > insert into t_idx values (2jue 2)

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

# question? Why is there blocking here?

B > > insert into t_idx values (4pc4)

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

# question? Why is there blocking here?

B > > insert into t_idx values (3pr 3)

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

B > > insert into t_idx values (5, 5)

ERROR 1062 (23000): Duplicate entry'5' for key 'PRIMARY'

B > > insert into t_idx values (1)

ERROR 1062 (23000): Duplicate entry'1' for key 'PRIMARY'

# insert all below #

B > > insert into t_idx values (6. 6)

Query OK, 1 row affected (0.00 sec)

B > > insert into t_idx values (7 and 7)

B > > insert into t_idx values (8. 8)

Query OK, 1 row affected (0.01sec)

B > > insert into t_idx values (122.12)

Query OK, 1 row affected (0.00 sec)

B > > select * from t_idx

+-+ +

| | id | mem_id |

+-+ +

| | 1 | 1 |

| | 3 | 3 |

| | 5 | 5 |

| | 6 | 6 |

| | 7 | 7 |

| | 8 | 8 |

| | 9 | 9 |

| | 11 | 11 |

| | 12 | 12 |

+-+ +

9 rows in set (0.00 sec)

Show engine inondb status partial output:

-

TRANSACTIONS

-

Trx id counter 11044

Purge done for trx's n:o

< 11041 undo n:o < 0 state: running but idle History list length 372 Total number of lock structs in row lock hash table 5 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 3, OS thread handle 0x7fd0430df700, query id 47 localhost root init show engine innodb status ---TRANSACTION 11039, ACTIVE 228 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 4 MySQL thread id 1, OS thread handle 0x7fd064099700, query id 45 localhost root update insert into t_idx values(4,4) Trx read view will not see trx with id >

= 11040, sees

< 11038 ------- TRX HAS BEEN WAITING 22 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 70 page no 4 n bits 80 index `idx_mem_id` of table `test`.`t_idx` trx id 11039 lock_mode X locks gap before rec insert intention waitin 结论：通过上面可以看到，通过非唯一索引字段进行更新时，在进行增删改时，有的记录会出现阻塞，为什么会出现阻塞呢？其实就是用到了MySQL的间隙锁。那MySQL这里为什么要用间隙锁呢？目的主要是防止幻读。 那为什么有的记录可以插入有的不可以，因为InnoDB对于行的查询时采用了Next-Key Lock的算法，锁定的是一个范围（GAP）如下：（∞,1]，(1,3]，(3,5]，(5,9]，(9,11]，(11, ∞)。InnoDB对辅助索引下一个键值也要加上Gap Lock，例如上面进行插入2、4、1、3、5时，就可以看出，其实锁住的区间是(1,5)。Read Committed级别（RC） Session A Session B A>

> rollback

Query OK, 0 rows affected (0.00 sec)

B > > rollback

Query OK, 0 rows affected (0.00 sec)

A > > set @ @ session.tx_isolation= "read-committed"

Query OK, 0 rows affected (0.00 sec)

B > > set @ @ session.tx_isolation= "read-committed"

Query OK, 0 rows affected (0.00 sec)

A > > select @ @ session.tx_isolation

+-- +

| | @ @ session.tx_isolation |

+-- +

| | READ-COMMITTED |

+-- +

1 row in set (0.00 sec)

B > > select @ @ session.tx_isolation

+-- +

| | @ @ session.tx_isolation |

+-- +

| | READ-COMMITTED |

+-- +

1 row in set (0.01 sec)

A > > begin

Query OK, 0 rows affected (0.00 sec)

B > > begin

Query OK, 0 rows affected (0.00 sec)

A > > select * from t_idx where mem_id=3 for update

+-+ +

| | id | mem_id |

+-+ +

| | 1 | 3 |

| | 3 | 3 |

+-+ +

2 rows in set (0.00 sec)

B > > insert into t_idx values (1)

ERROR 1062 (23000): Duplicate entry'1' for key 'PRIMARY'

B > > insert into t_idx values (2jue 2)

Query OK, 1 row affected (0.00 sec)

B > > insert into t_idx values (3pr 3)

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

B > > insert into t_idx values (4pc4)

Query OK, 1 row affected (0.01sec)

Conclusion: under the RC level, transaction B can be added, deleted and modified (except the locked record itself), and there is no gap lock.

Unique index + RR/RC

Construct initialization tables and data:

Mysql-uroot-puse test;DROP TABLE IF EXISTS tactipkbot create TABLE `tpk` (`id` int (11) NOT NULL AUTO_INCREMENT, `mem_ id` int (11) NOT NULL, PRIMARY KEY (`id`), UNIQUE `uq_mem_ id` (`mem_ id`) ENGINE=InnoDB;INSERT INTO t_pk VALUES (1), (3), (5), (9), (11)); REPEATABLE READ (RR level)

Root@localhost [(none)]: 10:04:34 > use test

Root@localhost [test]: 10:04:41 > prompt A > >

PROMPT set to'A > >'

Root@localhost [(none)]: 10:04:37 > use test

Root@localhost [test]: 10:04:52 > prompt B > >

PROMPT set to'B > >'

A > > select @ @ session.tx_isolation

+-- +

| | @ @ session.tx_isolation |

+-- +

| | REPEATABLE-READ |

+-- +

1 row in set (0.01 sec)

B > > select @ @ session.tx_isolation

+-- +

| | @ @ session.tx_isolation |

+-- +

| | REPEATABLE-READ |

+-- +

1 row in set (0.00 sec)

A > > begin

Query OK, 0 rows affected (0.00 sec)

B > > begin

Query OK, 0 rows affected (0.00 sec)

A > > select * from t_pk

+-+ +

| | id | mem_id |

+-+ +

| | 1 | 1 |

| | 3 | 3 |

| | 5 | 5 |

| | 9 | 9 |

| | 11 | 11 |

+-+ +

5 rows in set (0.00 sec)

B > > select * from t_pk

+-+ +

| | id | mem_id |

+-+ +

| | 1 | 1 |

| | 3 | 3 |

| | 5 | 5 |

| | 9 | 9 |

| | 11 | 11 |

+-+ +

5 rows in set (0.00 sec)

A > > select * from t_pk where mem_id=3 for update

+-+ +

| | id | mem_id |

+-+ +

| | 3 | 3 |

+-+ +

1 row in set (0.00 sec)

B > > insert into t_pk values (2jue 2)

Query OK, 1 row affected (0.00 sec)

B > > insert into t_pk values (4pc4)

Query OK, 1 row affected (0.00 sec)

B > > insert into t_pk values (3pr 3)

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

B > > insert into t_pk values (5, 5)

ERROR 1062 (23000): Duplicate entry'5' for key 'PRIMARY'

B > > insert into t_pk values (7 and 7)

Query OK, 1 row affected (0.00 sec)

Conclusion: as you can see here, for updates based on unique indexes, MySQL simply locks the record itself.

By the same token, we can deduce that the primary key is the same. I will skip the experiment. In fact, I can just change the above mem_id to id.

Record Lock based on primary key, or RR level

A > > rollback

Query OK, 0 rows affected (0.00 sec)

B > > rollback

Query OK, 0 rows affected (0.00 sec)

A > > begin

Query OK, 0 rows affected (0.00 sec

B > > begin

Query OK, 0 rows affected (0.00 sec)

A > > select * from t_pk where id=3 for update

+-+ +

| | id | mem_id |

+-+ +

| | 3 | 3 |

+-+ +

1 row in set (0.00 sec)

B > > insert into t_pk values (2jue 2)

Query OK, 1 row affected (0.00 sec)

B > > insert into t_pk values (4pc4)

Query OK, 1 row affected (0.00 sec)

Conclusion: it shows that the above derivation is correct. Read-Committed level (RC)

A > > rollback

Query OK, 0 rows affected (0.00 sec)

B > > rollback

Query OK, 0 rows affected (0.00 sec)

A > > set @ @ session.tx_isolation= "read-committed"

Query OK, 0 rows affected (0.01 sec)

B > > set @ @ session.tx_isolation= "read-committed"

Query OK, 0 rows affected (0.00 sec)

A > > select @ @ session.tx_isolation

+-- +

| | @ @ session.tx_isolation |

+-- +

| | READ-COMMITTED |

+-- +

1 row in set (0.00 sec)

B > > select @ @ session.tx_isolation

+-- +

| | @ @ session.tx_isolation |

+-- +

| | READ-COMMITTED |

+-- +

1 row in set (0.00 sec)

A > > begin

Query OK, 0 rows affected (0.00 sec)

B > > begin

Query OK, 0 rows affected (0.00 sec)

A > > select * from t_pk

+-+ +

| | id | mem_id |

+-+ +

| | 1 | 1 |

| | 3 | 3 |

| | 5 | 5 |

| | 9 | 9 |

| | 11 | 11 |

+-+ +

5 rows in set (0.00 sec)

B > > select * from t_pk

+-+ +

| | id | mem_id |

+-+ +

| | 1 | 1 |

| | 3 | 3 |

| | 5 | 5 |

| | 9 | 9 |

| | 11 | 11 |

+-+ +

5 rows in set (0.00 sec)

A > > select * from t_pk where mem_id=3 for update

+-+ +

| | id | mem_id |

+-+ +

| | 3 | 3 |

+-+ +

1 row in set (0.00 sec)

B > > insert into t_pk values (2jue 2)

Query OK, 1 row affected (0.00 sec)

B > > insert into t_pk values (4), (6), (10)

Query OK, 3 rows affected (0.00 sec)

Records: 3 Duplicates: 0 Warnings: 0

Conclusion: there is no gap lock at RC level.

Primary key + RR/RC

This is the same as unique index + RR/RC, see unique index + RR/RC above.

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.