Mysql锁-表级别锁

losetowin 发布于:2019-2-18 22:51 分类:技术  有 1020 人浏览,获得评论 0 条 标签: mysql  

本文地址:https://www.dutycode.com/mysql_suo_biaosuo.html
除非注明,文章均为 www.dutycode.com 原创,欢迎转载!转载请注明本文地址,谢谢。

表级别锁

表级别锁包含两种: 表锁和元数据锁


表锁

  • 命令:

    • 客户端和服务端断开时,服务端也会主动释放锁

    • lock table 除了限制其他线程的读写外,本身线程接下来的操作对象读写也受限制

    • 例子:

    • 线程A 执行 lock table t1 read, t2 write 时,线程B无法对t1写操作, t2读写操作。 同时, 线程A在释放锁前,也无法对t1进行写操作, t2进行读写操作

    • lock table ... read/write 上锁

    • unlock tables 释放锁


元数据锁MDL(Metadata Lock)

  • 不需要显式使用,访问一张表时会被默认加上

  • 目的:保证读写正确性

    • 例:正在读的表,表结构变了,发现查询出来的数据和表结构对不上,肯定不允许

    • 防止DML和DDL并发冲突

  • Mysql 5.5以后引入

  • 对表做增删改查时,加MDL读锁。 对表结构进行变更时,加MDL写锁

    • 读锁之间不互斥,所以可以有多个线程同时对一张表进行增删改查

    • 读写锁、写锁之间互斥,用于保证变更表结构操作的安全性。eg:有两个线程同时要给表加字段时,必须等其中一个线程完成之后才能执行

  • 注意:事务中的MDL锁,在语句开始执行时申请,在事务提交后释放,而不是在语句执行完之后释放

    • Mysql默认配置下,事务都是默认自动提交的,即sql执行完会立马Commit (BTW:这时候MDL锁会很快释放,不会出现上述问题)

    • 显式启动事务:使用BEGIN / START TRANSACTION 或者SET AUTOCOMMIT=0 来禁止当前会话的自动提交

    • 长事务下,事务不提交,就会一直占有MDL锁,如果表的查询频繁,且客户端有重试机制的话,整个库的线程池很快就会被占满(重试发起新的session进行请求)


  • 如何安全的给小表加字段?

    • 可在alter表的时候,增加等待时间,如果在等待的时间内拿到了MDL锁,就可以变更,如果拿不到也不要堵塞业务查询。 之后再重复尝试修改

    • Mysql未支持alter等待时间, AliSQL和MariaDb支持

    • 语法:
      ALTER TABLE tbl_name NOWAIT add column ...
      ALTER TABLE tbl_name WAIT N add column ...

    • 在Mysql的information_schema库中的innodb_trx表中可以查询到当前正在执行的事务。 如果要做DDL变更的表有正在执行的长事务,可以暂停DDL,或者kill掉长事务

    • 1、解决长事务,将长事务kill掉之后再改表

    • 2、热点表,即使kill掉事务后,可能很快就又会启动新的事务,不太适合kill的方式解决

  • OnlineDDL步骤

    • 1. 拿MDL写锁

    • 2. 降级成MDL读锁

    • 3. 真正做DDL

    • 4. 升级成MDL写锁

    • 5. 释放MDL锁



版权所有:《攀爬蜗牛》 => 《Mysql锁-表级别锁
本文地址:https://www.dutycode.com/mysql_suo_biaosuo.html
除非注明,文章均为 《攀爬蜗牛》 原创,欢迎转载!转载请注明本文地址,谢谢。

上一篇:Mysql count
下一篇:Mysql锁-行锁