14.5.2.4 Locking Reads 锁定读: 如果你查询数据然后插入或者修改相关数据在相同的事务里, 常规的SELECT 语句不能给予足够的保护。 其他事务可以修改或者删除你刚查询相同的记录,InnoDB 支持2个锁定读的类型提供额外的安全: /******************* 测试MYSQL RR 的重复读: CREATE TABLE `t3` (
`sn` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增编号',
`phoneNo` int(11) DEFAULT NULL,
PRIMARY KEY (`sn`)); mysql> show create table t3;
+------- +------------------------------------------------------------------------------------------------------------------------------------------------------------ --------------------+
| Table | Create Table |
+------- +------------------------------------------------------------------------------------------------------------------------------------------------------------ --------------------+
| t3 | CREATE TABLE `t3` (
`sn` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增编号',
`phoneNo` int(11) DEFAULT NULL,
PRIMARY KEY (`sn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------- +------------------------------------------------------------------------------------------------------------------------------------------------------------ --------------------+
1 row in set (0.00 sec) Session 1:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec) mysql> select * from t3;
+----+---------+
| sn | phoneNo |
+----+---------+
| 1 | 11 |
| 2 | 22 |
+----+---------+
2 rows in set (0.00 sec) mysql> select * from t3;
+----+---------+
| sn | phoneNo |
+----+---------+
| 1 | 11 |
| 2 | 22 |
+----+---------+
2 rows in set (0.00 sec) Session 2: Database changed
mysql> delete from t3 where sn=2;
Query OK, 1 row affected (0.00 sec) mysql> commit;
Query OK, 0 rows affected (0.00 sec) 此时session 1看到的还是2条 表明RR是重复读 1.SELECT ... LOCK IN SHARE MODE 设置一个共享锁锁定在任何你要读的记录,其他session 只能读取记录, 因为不能修改它们直到你的事务提交。 如果任何其他记录被其他事务改变 没有被提交,你的查询等待直到那个事务借宿 然后使用最新的数据 mysql> select * from t3;
+----+---------+
| sn | phoneNo |
+----+---------+
| 1 | 11 |
| 2 | 22 |
+----+---------+
2 rows in set (0.00 sec) mysql> select * from t3 LOCK IN SHARE MODE; +----+---------+
| sn | phoneNo |
+----+---------+
| 1 | 11 |
| 2 | 99 |
+----+---------+
2 rows in set (4.78 sec) 看到的是最新的数据: mysql> select * from t3;
+----+---------+
| sn | phoneNo |
+----+---------+
| 1 | 11 |
| 2 | 22 |
+----+---------+
2 rows in set (0.00 sec) mysql> select * from t3 LOCK IN SHARE MODE;
+----+---------+
| sn | phoneNo |
+----+---------+
| 1 | 11 |
| 2 | 99 |
+----+---------+
2 rows in set (0.00 sec) 2. mysql> select * from t3 where sn=2 for update --看到的也是最新的记录
-> ; +----+---------+
| sn | phoneNo |
+----+---------+
| 2 | 99 |
+----+---------+
1 row in set (4.26 sec) mysql>
mysql> select * from t3;
+----+---------+
| sn | phoneNo |
+----+---------+
| 1 | 11 |
| 2 | 22 |
+----+---------+
2 rows in set (0.00 sec) 对于所有搜索遇到, SELECT ... FOR UPDATE locks 锁定记录和任何相关的index entries, 相当于你执行一个update语句对于那些记录。 其他事务是被堵塞的来更新那些记录,做 SELECT ... LOCK IN SHARE MODE, 或者从读取的数据在某些事务隔离级别。 一直读忽略任何锁设置在记录上(老的记录不能被锁定,它们是应用undo log重构的) 当 LOCK IN SHARE MODE and FOR UPDATE 查询的索引锁是被释放放事务是被提交或者回滚 锁定记录用于更新使用SELECT FOR UPDATE 只使用与当autocommit 被关闭(或者开始事务使用START TRANSACTION or by setting autocommit to 0. 如果自动提交启用,匹配的记录不会被锁定)