• 某、人
    2018-12-26
    最近几张干货越来越多了,很实用,收获不少.先回答今天的问题
    版本5.7.13
    rc模式下:
    session 1:
    begin;
    select * from t where c=5 for update;
    session 2:
    delete from t where c=10 --等待
    session 3:
    insert into t values(100001,8) --成功
    session 1:
    commit
    session 2:事务执行成功
    rr模式下:
    begin;
    select * from t where c=5 for update;
    session 2:
    delete from t where c=10 --等待
    session 3:
    insert into t values(100001,8) --等待
    session 1:
    commit
    session 2:事务执行成功
    session 3:事务执行成功
    从上面这两个简单的例子,可以大概看出上锁的流程.
    不管是rr模式还是rc模式,这条语句都会先在server层对表加上MDL S锁,然后进入到引擎层。

    rc模式下,由于数据量不大只有10W。通过实验可以证明session 1上来就把该表的所有行都锁住了。
    导致其他事务要对该表的所有现有记录做更新,是阻塞状态。为什么insert又能成功?
    说明rc模式下for update语句没有上gap锁,所以不阻塞insert对范围加插入意向锁,所以更新成功。
    session 1commit后,session 2执行成功。表明所有行的x锁是在事务提交完成以后才释放。

    rr模式下,session 1和session 2与rc模式下都一样,说明rr模式下也对所有行上了X锁。
    唯一的区别是insert也等待了,是因为rr模式下对没有索引的更新,聚簇索引上的所有记录,都被加上了X锁。其次,聚簇索引每条记录间的间隙(GAP),也同时被加上了GAP锁。由于gap锁阻塞了insert要加的插入意向锁,导致insert也处于等待状态。只有当session 1 commit完成以后。session 1上的所有锁才会释放,S2,S3执行成功

    由于例子中的数据量还比较小,如果数据量达到千万级别,就比较直观的能看出,上锁是逐行上锁的一个过程.扫描一条上一条,直到所有行扫描完,rc模式下对所有行上x锁。rr模式下不仅对所有行上X锁,还对所有区间上gap锁.直到事务提交或者回滚完成后,上的锁才会被释放。
    展开

    作者回复: 分析得非常好。
    两个模式下,各增加一个session 4 : update t set c=100 where id=10看看哦

    基本就全了👍🏿

     5
     63
  • 薛畅
    2018-12-26
    回来老师的问题:
    在 Read Committed 隔离级别下,会锁上聚簇索引中的所有记录;
    在 Repeatable Read 隔离级别下,会锁上聚簇索引中的所有记录,并且会锁上聚簇索引内的所有 GAP;
    在上面两个隔离级别的情况下,如果设置了 innodb_locks_unsafe_for_binlog 开启 semi-consistent read 的话,对于不满足查询条件的记录,MySQL 会提前放锁,不过加锁的过程是不可避免的。
    
     24
  • 似水流年
    2018-12-28
    请问老师,为什么select blocking_pid from sys.schema_table_lock_waits;查不到mdl锁的进程id,显示为空。
     4
     18
  • 沙漠里的骆驼
    2018-12-26
    @高枕
    这里有些资料提供给你参考:
    1. 何登成的技术博客: 加锁分析 http://hedengcheng.com/?p=771
    2. 锁的常见种类: http://www.aneasystone.com/archives/2017/11/solving-dead-locks-two.html
     1
     17
  • 张永志
    2018-12-27
    RR隔离级别下,为保证binlog记录顺序,非索引更新会锁住全表记录,且事务结束前不会对不符合条件记录有逐步释放的过程。

    作者回复: 准确

    
     11
  • 老杨同志
    2018-12-26
    愉快的做一下思考题
    begin;
    select * from t where c=5 for update;
    commit;
    历史知识的结论是,innodb先锁全表的所有行,返回server层,判断c是否等于5,然后释放c!=5的行锁。
    验证方法:
    事务A执行 锁住一行c!=5的记录 比如id =3 c=3
     select * from t where id = 3 for update 或者 update t set c=4 where id =3
    然后启动新事务B执行上面的语句select * from t where c=5 for update; 看看有没有被阻塞。
    用于判断事务B的语句会不会试图锁不满足条件的记录。
    然后把事务A和事务B的执行顺序对调一下,也就是先执行B在执行A。看看有没有阻塞,
    判断在事务B加锁成功的情况下会不会释放不满足查询条件记录的行锁。
    展开

    作者回复: 👍🏿 思路清晰


    隔离级别再愉快地改成RR试试😄

    
     11
  • xm
    2018-12-28
    @Elvis
    Elvis
    
    0
    老师,最近项目mysql遇到一个难题, 表数据每天230万,一条语句的查询条件有1—40个,最坏情况下40,请问老师有没有好的建议,非常感谢
    2018-12-27
     作者回复
    这个是索引最头大的问题之一了… 如果有明显的某类组合查询是最多的还好,否则确实很麻烦。

    我觉得应对这种大数据量的多条件的查询的话换种思路,将mysql复杂的组合查询条件导入到es中作为key,主键id作为value,复杂的查询经过es后得到主键id,之后走mysql会好很多,目前公司是这样做的,老师觉得怎么样?
    展开

    作者回复: 嗯,这个是一种思路,见过这么干的😄

    
     7
  • 小确幸
    2018-12-26
    问一下:索引扫描与全表扫描,有什么异同点?

    作者回复: 一般说全表扫描默认是值“扫瞄主键索引”

    
     7
  • Tony Du
    2018-12-27
    对于课后问题,select * from t where c=5 for update,
    当级别为RR时,因为字段c上没有索引,会扫主键索引,这时会把表中的记录都加上X锁。同时,因为对于innodb来说,当级别为RR时,是可以解决幻读的,此时对于每条记录的间隙还要加上GAP锁。也就是说,表上每一条记录和每一个间隙都锁上了。
    当级别为RC时,因为字段c上没有索引,会扫主键索引,这时会把表中的记录都加上X锁。

    另外,之前看过相关文章,MySQL在实际实现中有些优化措施,比如当RC时,在MySQL server过滤条件,发现不满足后,会把不满足条件的记录释放锁(这里就是把 c!=5的记录释放锁),这里会违背两阶段的约束。当然,之前每条记录的加锁操作还是不能省略的。
    还有,对于semi consistent read开启的情况下,也会提前释放锁。
    展开
    
     6
  • 张永志
    2018-12-27
    RC隔离级别下,对非索引字段更新,有个锁全表记录的过程,不符合条件的会及时释放行锁,不必等事务结束时释放;而直接用索引列更新,只会锁索引查找值和行。


    
     6
  • 蠢蠢欲动的腹肌
    2018-12-28
    老师,您好
        我的mysql版本5.7.24,尝试的时候发现了如下问题
        锁住了表T
            mysql> lock table T write;
            Query OK, 0 rows affected (0.00 sec)
       另一个terminal查询时被阻塞,但是查不到blocking_pid ,这是什么情况呢
       mysql> select blocking_pid from sys.schema_table_lock_waits;
       Empty set (0.00 sec)
      ps:发现查询schema_table_lock_waits表与lock table的语句不能放在一个terminal执行,否则会报
    Table 'schema_table_lock_waits' was not locked with LOCK TABLES
    自行尝试的同学要注意下,老师有空的话也可以帮看看为什么。。。
    展开
     2
     5
  • 尘封
    2018-12-26
    课后问题:d这一列不存在,但是还是要加MDL锁,释放时间应该是事务提交时。

    作者回复: 抱歉,是要写成where c=5 , 发起堪误了

    
     5
  • 残天噬魂
    2019-07-09
    为啥我开启了性能监控,按照案例1执行加锁,select * from sys.schema_table_lock_waits;这个没有结果啊
     1
     4
  • godtrue
    2019-07-31
    课前思考
    1:为啥只查一行的语句,也执行这么慢?
    查的慢,基本上就是索引使用的问题,和查一行还是N行(N不是巨大),没有必然联系。查一行慢,猜测没有走索引查询,且数据量比较大。
    课后思考
    1:阅后发现自己的无知,只查询一行的语句,也比较慢,原因从大到小可分为三种情况?
    第一MySQL数据库本身被堵住了,比如:系统或网络资源不够
    第二SQL语句被堵住了,比如:表锁,行锁等,导致存储引擎不执行对应的SQL语句
    第三确实是索引使用不当,没有走索引
    第四是表中数据的特点导致的,走了索引,但回表次数庞大
    感谢老师的分享,真是醍醐灌顶呀😄
    展开
    
     3
  • 浩涛
    2019-03-11
    老师,我们公司今年打算迁数据库,也就是oracle迁到mysql,目前要决定选用哪个版本和分支,不知道要怎么分析,您有什么好的建议吗?

    作者回复: 目前5.7是比较主流的,就用最新GA版本吧,记得做全量回归测试

    
     3
  • 尘封
    2018-12-26
    老师,有没有遇到过select语句一直处于killed状态的情况?

    作者回复: 有😄 这个是在后面的文章中会用到的例子

    
     3
  • 简海青
    2019-05-04
    performance_schema=ON;
    server version: 5.7.25-28 (percona server)
    林老师,好想有个群可以快速交流的,但也知道IM 信息过多,容易给人困扰
    
     2
  • M
    2019-01-09
    老师讲的很好

    作者回复: 多谢鼓励

    看文章的同学都很细致,不敢不认真😄

    
     2
  • 滔滔
    2018-12-28
    老师,我有个问题问,在讲隔离级别的时候讲过可重复读级别下,默认读操作(select查询,不是当前读)读到的是某条数据的某个历史版本,而所有这些历史版本数据都是以undo log的形式存在的对吗?需要找某个历史版本数据就直接执行对应undo log,而不是事先把历史版本的数据保存下来,是这样的吗?

    作者回复: 是的

    
     2
  • 小李子
    2018-12-27
    老师,为什么session B 执行了 select in share mode ,在等行锁的时候,session C 执行
    select * from sys.innodb_lock_waits where locked_table='`test`.`t`' 会报这个错
    [Err] 1356 - View 'sys.innodb_lock_waits' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them,而超时之后,又可以查了?另外,\G 参数会报语法错误?
     1
     2
我们在线,来聊聊吧