美文网首页
MySQL之当前读

MySQL之当前读

作者: 猿来是八阿哥 | 来源:发表于2020-01-13 17:32 被阅读0次
    mysql
    1. 定义
    • 当前读 或者说 current read,其实是指 MySQL 中的 locking read,即:带锁的查询。
    • locking read 是 MySQL 的 InnoDB 存储引擎中,防止:其他事务删除或者修改本事务正在查询的数据的一种安全策略。
    • 在 InnoDB 中,locking read 是指:select ... for shareselect ... from update
    • 使用 locking read 的前提是:通过 start transactionset 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 sharefor update 锁释放
    • 对于 for sharefor update 生成的结果集锁和索引锁,当事务提交或者回滚时,就会释放。
    5. 子查询中的 locking read

    for sharefor 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, ...),需求:

    1. 在创建班级时,要考虑到其他人有可能会删除老师,或者更新老师的姓名(老师姓名也冗余到了班级表)。
    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, ...),需求是:

    1. 老师表中冗余了所带学生的数据,在为班级添加学生时,要考虑到可能多个人同时都在添加学生。

    分析:
    此时,使用 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. nowaitskip locked
    • for sharefor update,会将结果集锁上,当其他 for sharefor update 要访问相同结果集时,将出现阻塞,当没有必要为第二个 for sharefor update 进行等到时,可以使用 nowaitskip locked
    • 区别是:出现相同结果集的阻塞时, nowait 会报错 ERROR 3572 (HY000): Do not wait for lock.,而 skip locked 则会跳过加锁的结果集,返回其他结果集。
    • 需要注意的是,nowaitskip 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 |
    +---+
    

    相关文章

      网友评论

          本文标题:MySQL之当前读

          本文链接:https://www.haomeiwen.com/subject/igrnactx.html