InnoDB的锁机制:

数据库使用所是为了支持更好的并发,提供数据的完整性和一致性。InnoDB是一个支持锁的存储引擎,锁的类型有:共享锁S锁(X意向共享锁(IS意向排锁(IX支持更好的并发,InnoDB提供了锁定读:不需要等待访问行上的锁释放,读取行的一个快照。该方法是通过InnoDB的一个特写:MVCC实现的。

InnoDB的分类:

  • Record Lock:行锁:单个行记录上的行锁

  • Gap Lock:间隙锁,锁定一个范围,但不包括记录本身

  • Next-Key Lock:Gap+Record Lock,锁定一个范围,并且锁定记录本身



  • 索引+RC/RR

对无索引的字段进行更新时RR级别通过锁主键的方式,来锁住所有记录RC级别不会锁所有记录。

构建及初始化数据:

mysql -uroot -p
USE test;
DROP TABLE IF EXISTS t_none;
CREATE TABLE `t_none` (
  `id` int(11) NOT NULL,
  `mem_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
INSERT INTO t_none VALUES(1,1),(3,3),(5,5),(9,9),(11,11);

REPEATABLE-READRR)默认级别

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(2,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部分输出:

------------

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(2,2);

Query OK, 1 row affected (0.01 sec)


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)

结论:在RC级别下,事务B是可以进行增删改(除被锁定的记录本身

  • 非唯一索引+RR/RC

  在RR级别下InnoDB对于非唯一索引会加Gap Lock(也即锁定一个区间),而RC级别

构造初始化表数据

mysql -uroot -p
USE test;
DROP TABLE IF EXISTS t_idx;
CREATE TABLE `t_idx` (
  `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(1,1),(3,3),(5,5),(9,9),(11,11);


REPEATABLE-READ(RR)默认级别(RR模式)

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.05 sec)



B>>insert into t_idx values(2,2);

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

#问题?这里为什么会出现阻塞呢?

B>>insert into t_idx values(4,4);

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

#问题?这里为什么会出现阻塞呢?

B>>insert into t_idx values(3,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,1);

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

#######下面插入全部可以######

B>>insert into t_idx values(6,6);

Query OK, 1 row affected (0.00 sec)

B>>insert into t_idx values(7,7);

B>>insert into t_idx values(8,8);

Query OK, 1 row affected (0.01 sec)

B>>insert into t_idx values(12,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部分输出:

------------

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、4135,就可以看出其实锁住的区间是(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,1);

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

B>>insert into t_idx values(2,2);

Query OK, 1 row affected (0.00 sec)

B>>insert into t_idx values(3,3);

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

B>>insert into t_idx values(4,4);

Query OK, 1 row affected (0.01 sec)

结论:在RC级别下,事务B是可以进行增删改(除被锁定的记录本身),没有出现间隙锁的现象


  • 唯一索引+RR/RC

构造初始数据

mysql -uroot –p
use test;
DROP TABLE IF EXISTS t_pk;
CREATE TABLE `t_pk` (
  `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,1),(3,3),(5,5),(9,9),(11,11);
REPEATABLE READRR级别

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(2,2);

Query OK, 1 row affected (0.00 sec)

B>>insert into t_pk values(4,4);

Query OK, 1 row affected (0.00 sec)

B>>insert into t_pk values(3,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,7);

Query OK, 1 row affected (0.00 sec)

结论:从这里可以看到,对于基于唯一索引的更新,MySQL只是锁定了记录本身。

同理,我们可以推导出主键也是一样的。实验的话我就略了,其实就是上面的mem_id改成id即可

基于主键Record Lock还是RR级别

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(2,2);

Query OK, 1 row affected (0.00 sec)

B>>insert into t_pk values(4,4);

Query OK, 1 row affected (0.00 sec)

结论:说明上面的推导正确。
Read-Committed级别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(2,2);

Query OK, 1 row affected (0.00 sec)

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

Query OK, 3 rows affected (0.00 sec)

Records: 3  Duplicates: 0  Warnings: 0

结论:说明RC级别下,没有间隙锁存在。
  • 主键+RR/RC

这跟唯一索引+RR/RC一样的,请参看上面的唯一索引+RR/RC。