innodb锁测试2(未完)

 

环境说明,默认RR级别
CREATE TABLE `trx_fee` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`userid` int(10) unsigned NOT NULL DEFAULT '0',
`fee` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `userid` (`userid`)
) ENGINE=InnoDB;


Insert into trx_fee(id,userid,fee)
Values(1,1,263),(2,2,537),(3,3,466),(4,5,299),(5,4,22),(6,6,99),(7,7,293),(10,10,0);

select * from trx_fee;
+----+--------+-----+
| id | userid | fee |
+----+--------+-----+
|  1 |      1 | 263 |
|  2 |      2 | 537 |
|  3 |      3 | 466 |
|  4 |      5 | 299 |
|  5 |      4 |  22 |
|  6 |      6 |  99 |
|  7 |      7 | 293 |
| 10 |     10 |   0 |
+----+--------+-----+
8 rows in set (0.00 sec)

　　

session1:
root@localhost:mysql.sock  19:09:17 [school]>begin;
root@localhost:mysql.sock  19:11:21 [school]>select * from trx_fee where id >=7 and id <=10 for update;
+----+--------+-----+
| id | userid | fee |
+----+--------+-----+
|  7 |      7 | 293 |
| 10 |     10 |   0 |
+----+--------+-----+
2 rows in set (0.00 sec)
主键只存在7和10,但是7~10的所有范围(7,8,9,10)全部锁定

session2: root@localhost:mysql.sock 19:11:31 [school]>insert into trx_fee select 9,9, 10;
等待 

以下简写事务

  T1：begin;select * from trx_fee where id>=7 and id<=10 for update;
  T2：insert into t1 select 8,8,8; 等待

  T3：insert into t1 select 9,9,9; 等待