mysql
1. 定义
-
当前读或者说current read，其实是指 MySQL 中的locking read，即：带锁的查询。 -
locking read是 MySQL 的 InnoDB 存储引擎中，防止：其他事务删除或者修改本事务正在查询的数据的一种安全策略。 - 在 InnoDB 中，
locking read是指：select ... for share和select ... from update - 使用
locking read的前提是：通过start transaction或set autocommit=0;取消autocommit。
2. select ... for share
-
select ... for share会在结果集的每一行上设置一个共享锁，其他事务可以读取结果集的这些行，但如果想修改这些行，必须要等待【共享锁】释放以后。 - 如果
select ... for share的结果集，正在被另外一个事务更新，并且那个事务还没有提交，那么select ... for share会一直等待那个事务结束，拿到最新的结果。 - 在老版本中
SELECT ... lock in share mode等同于select ... for share，但select ... for share支持table_name, nowait, skip locked选项。
3. select ... for update
-
select ... for update对于那些涉及到索引的查询，会将结果集和索引记录一起锁起来，就像执行 update 语句一样。 -
select ... for update后，当其他事务想要修改结果集，或者想过对结果集进行select ... for share时，或者在某些事务隔离级别上要查询数据，都会被阻塞。 - 对于
select ... for update后结果集上的锁，快照读或者说Consistent reads来说，这些锁是被忽略的，即：select ... for update不会对快照上锁。
4. for share 和 for update 锁释放
- 对于
for share和for update生成的结果集锁和索引锁，当事务提交或者回滚时，就会释放。
5. 子查询中的 locking read
for share 和 for update 子查询的结果集是否也会被锁上，取决于：子查询是否也使用了 【for share】 或者 【for update】，即：
# subquery's result will not be locked
mysql> select t1.* from t1 where t1.field1 in (select t2.field1 from t2) for share;
mysql> select t1.* from t1 where t1.field1 in (select t2.field1 from t2) for update;
# subquery's result will be locked
mysql> select t1.* from t1 where t1.field1 in (select t2.field1 from t2 for share) for share;
mysql> select t1.* from t1 where t1.field1 in (select t2.field1 from t2 for update) for update;
6. 例子
假想一个业务需求：
老师表：user_teacher(id, truename, ...)，班级表：user_teacher_class(id, teacher_id, teacher_name, classname, ...)，需求：
- 在创建班级时，要考虑到其他人有可能会删除老师，或者更新老师的姓名（老师姓名也冗余到了班级表）。
mysql> start transaction;
mysql> select * from user_teacher where id=1 for share;
# 1 rows found.
mysql> insert into user_teacher_class (teacher_id, ...) values (1, ...);
# 1 rows created.
mysql> commit;
加需求了：
user_student(id, class_id, teacher_id, student_name, ...)，user_teacher(id, truename, student_number, ...)，需求是：
- 老师表中冗余了所带学生的数据，在为班级添加学生时，要考虑到可能多个人同时都在添加学生。
分析：
此时，使用select student_number from user_teacher for share可能导致多个student_number = student_number + 1，出问题。
因此，使用for update让其他事务阻塞。
start transaction;
# 验证老师是否存在
select * from user_teacher where id=1 for update;
# 1 rows found.
insert into user_student (teacher_id, ...) values (1, ...);
# 1 rows found.
update user_teacher set student_number = student_number + 1 where id=1;
commit;
7. nowait 和 skip locked
-
for share和for update，会将结果集锁上，当其他for share和for update要访问相同结果集时，将出现阻塞，当没有必要为第二个for share或for update进行等到时，可以使用nowait或skip locked。 - 区别是：出现相同结果集的阻塞时，
nowait会报错ERROR 3572 (HY000): Do not wait for lock.，而skip locked则会跳过加锁的结果集，返回其他结果集。 - 需要注意的是，
nowait和skip locked只适用于row-level locked isolation level（行锁类型的隔离级别，如：InnoDB）。 - 给出一个文档上的例子：
# Session 1:
mysql> CREATE TABLE t (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;
mysql> INSERT INTO t (i) VALUES(1),(2),(3);
mysql> START TRANSACTION;
mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE;
+---+
| i |
+---+
| 2 |
+---+
# Session 2:
mysql> START TRANSACTION;
mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE NOWAIT;
ERROR 3572 (HY000): Do not wait for lock.
# Session 3:
mysql> START TRANSACTION;
mysql> SELECT * FROM t FOR UPDATE SKIP LOCKED;
+---+
| i |
+---+
| 1 |
| 3 |
+---+
网友评论