欢迎光临
我们一直在努力

MySQL的在RC和RR模式下的锁

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。

赞(0)
【声明】:本博客不参与任何交易,也非中介,仅记录个人感兴趣的主机测评结果和优惠活动,内容均不作直接、间接、法定、约定的保证。访问本博客请务必遵守有关互联网的相关法律、规定与规则。一旦您访问本博客,即表示您已经知晓并接受了此声明通告。