域名备案怎么关闭网站,网页视频下载器手机版,有效的网站建设,交换友情链接的目的前言
MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类#xff0c;全局锁和表级锁是在server层实现的。 全局锁
全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法#xff0c;命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状… 前言
MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类全局锁和表级锁是在server层实现的。 全局锁
全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候可以使用这个命令之后其他线程的以下语句会被阻塞数据更新语句数据的增删改、数据定义语句包括建表、修改表结构等和更新类事务的提交语句。 获取全局锁FLUSH TABLES WITH READ LOCK 释放全局锁UNLOCK TABLES 全局锁的典型使用场景是做全库逻辑备份。也就是把整库每个表都 select 出来存成文本。以前有一种做法是通过 FTWRL 确保不会有其他线程对数据库做更新然后对整个库做备份。注意在备份过程中整个库完全处于只读状态。
但是让整库都只读
如果你在主库上备份那么在备份期间都不能执行更新业务基本上就得停摆
如果你在从库上备份那么备份期间从库不能执行主库同步过来的 binlog会导致主从延迟。
官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候导数据之前就会启动一个事务来确保拿到一致性视图。而由于 MVCC 的支持这个过程中数据是可以正常更新的。 mysqldump -h127.0.0.1 -uroot -p123456 --single-transaction --default-character-setutf8 accounting_global zg_tenant_entity /tmp/ccc.sql 为什么还需要 FTWRL 呢一致性读是好但前提是引擎要支持这个隔离级别。
比如对于 MyISAM 这种不支持事务的引擎如果备份过程中有更新总是只能取到最新的数据那么就破坏了备份的一致性。这时我们就需要使用 FTWRL 命令了。
所以single-transaction 方法只适用于所有的表使用事务引擎的库。如果有的表使用了不支持事务的引擎那么备份就只能通过 FTWRL 方法。这往往是 DBA 要求业务开发人员使用 InnoDB 替代 MyISAM 的原因之一。
既然要全库只读为什么不使用 set global readonlytrue 的方式呢确实 readonly 方式也可以让全库进入只读状态但我还是会建议你用 FTWRL 方式主要有两个原因
一是在有些系统中readonly 的值会被用来做其他逻辑比如用来判断一个库是主库还是备库。因此修改 global 变量的方式影响面更大我不建议你使用。
二是在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开那么 MySQL 会自动释放这个全局锁整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后如果客户端发生异常则数据库就会一直保持 readonly 状态这样会导致整个库长时间处于不可写状态风险较高。
业务的更新不只是增删改数据DML)还有可能是加字段等修改表结构的操作DDL。不论是哪种方法一个库被全局锁上以后你要对里面任何一个表做加字段操作都是会被锁住的。
表级锁
MySQL 里面表级别的锁有两种一种是表锁一种是元数据锁meta data lockMDL)。
表锁的语法是 lock tables … read/write。与 FTWRL 类似可以用 unlock tables 主动释放锁也可以在客户端断开的时候自动释放。需要注意lock tables 语法除了会限制别的线程的读写外也限定了本线程接下来的操作对象。
举个例子, 如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句则其他线程写 t1、读写 t2 的语句都会被阻塞。同时线程 A 在执行 unlock tables 之前也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许自然也不能访问其他表。在还没有出现更细粒度的锁的时候表锁是最常用的处理并发的方式。而对于 InnoDB 这种支持行锁的引擎一般不使用 lock tables 命令来控制并发毕竟锁住整个表的影响面还是太大。
另一类表级的锁是 MDLmetadata lock)。MDL 不需要显式使用在访问一个表的时候会被自动加上。MDL 的作用是保证读写的正确性。你可以想象一下如果一个查询正在遍历一个表中的数据而执行期间另一个线程对这个表结构做变更删了一列那么查询线程拿到的结果跟表结构对不上肯定是不行的。
因此在 MySQL 5.5 版本中引入了 MDL当对一个表做增删改查操作的时候加 MDL 读锁当要对表做结构变更操作的时候加 MDL 写锁。
读锁之间不互斥因此你可以有多个线程同时对一张表增删改查。
读写锁之间、写锁之间是互斥的用来保证变更表结构操作的安全性。因此如果有两个线程要同时给一个表加字段其中一个要等另一个执行完才能开始执行。
虽然 MDL 锁是系统默认会加的但却是你不能忽略的一个机制。比如下面这个例子我经常看到有人掉到这个坑里给一个小表加个字段导致整个库挂了。
你肯定知道给一个表加字段或者修改字段或者加索引需要扫描全表的数据。在对大表操作的时候你肯定会特别小心以免对线上服务造成影响。而实际上即使是小表操作不慎也会出问题。我们来看一下下面的操作序列假设表 t 是一个小表。
sessionAsessionBsessionCsessionDbegin; select * from Tselect * from Talter table T add F int (blocked)select * from T (blocked)
我们可以看到 session A 先启动这时候会对表 t 加一个 MDL 读锁。由于 session B 需要的也是 MDL 读锁因此可以正常执行。
之后 session C 会被 blocked是因为 session A 的 MDL 读锁还没有释放而 session C 需要 MDL 写锁因此只能被阻塞。如果只有 session C 自己被阻塞还没什么关系但是之后所有要在表 t 上新申请 MDL 读锁的请求也会被 session C 阻塞。
如果某个表上的查询语句频繁而且客户端有重试机制也就是说超时后会再起一个新 session 再请求的话这个库的线程很快就会爆满。 申请MDL锁的操作会形成一个队列队列中写锁获取优先级高于读锁。一旦出现写锁等待不但当前操作会被阻塞同时还会阻塞后续该表的所有操作。 事务一旦申请到MDL锁后直到事务执行完才会将锁释放。这里有种特殊情况如果事务中包含DDL操作mysql会在DDL操作语句执行前隐式提交commit以保证该DDL语句操作作为一个单独的事务存在同时也保证元数据排他锁的释放 事务中的 MDL 锁在语句执行开始时申请但是语句结束后并不会马上释放而会等到整个事务提交后再释放。
如何安全地给小表加字段
1、首先我们要解决长事务事务不提交就会一直占着 MDL 锁。在 MySQL 的 information_schema 库的 innodb_trx 表中你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行要考虑先暂停 DDL或者 kill 掉这个长事务。
2、比较理想的机制是在 alter table 语句里面设定等待时间如果在这个指定的等待时间里面能够拿到 MDL 写锁最好拿不到也不要阻塞后面的业务语句先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。
3、这里可以看出涉及到多线程锁相关的部分不管是java还是mysql解决的办法都差不多即为了防止死锁或者长时间卡顿通过加入超时时间的方法来解决。 ALTER TABLE tbl_name NOWAIT add column ... ALTER TABLE tbl_name WAIT N add column ... Online DDL的过程是这样的 拿MDL写锁 降级成MDL读锁 真正做DDL 升级成MDL写锁 释放MDL锁
1、2、4、5如果没有锁冲突执行时间非常短。第3步占用了DDL绝大部分时间这期间这个表可以正常读写数据是因此称为“online ”
行锁
MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁比如 MyISAM 引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁对于这种引擎的表同一张表上任何时刻只能有一个更新在执行这就会影响到业务并发度。InnoDB 是支持行锁的这也是 MyISAM 被 InnoDB 替代的重要原因之一。 锁粒度越大开销越小锁冲突的概率越小安全性也就越高但业务并发度以及性能越差反之锁粒度越小开销也就越大锁冲突的概率越大易导致死锁安全性也就越低但业务并发度以及性能越好。 两阶段锁
在 InnoDB 事务中行锁是在需要的时候才加上的但并不是不需要了就立刻释放而是要等到事务结束时才释放。这个就是两阶段锁协议。
如果你的事务中需要锁多个行要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。 假设你负责实现一个电影票在线交易业务顾客 A 要在影院 B 购买电影票。我们简化一点这个业务需要涉及到以下操作 1、从顾客 A 账户余额中扣除电影票价 2、给影院 B 的账户余额增加这张电影票价 3、记录一条交易日志。 也就是说要完成这个交易我们需要 update 两条记录并 insert 一条记录。当然为了保证交易的原子性我们要把这三个操作放在一个事务中。那么你会怎样安排这三个语句在事务中的顺序呢
根据两阶段锁协议不论你怎样安排语句顺序所有的操作需要的行锁都是在事务提交的时候才释放的。所以如果你把语句 2 安排在最后比如按照 3、1、2 这样的顺序那么影院账户余额这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待提升了并发度。 如果你的事务中需要锁多个行要把最可能造成锁冲突、最可能影响并发度的锁的申请时机尽量往后放。 lnnoDB存储引擎有3种行锁的算法其分别是 Record Lock:单个行记录上的锁 Gap Lock:间隙锁锁定一个范围但不包含记录本身 Next-Key Lock : Gap LockRecord Lock,锁定一个范围并且锁定记录本身
Record Lock总是会去锁住索引记录如果InnoDB存储引擎表在建立的时候没有设置任何一个索引那么这时InnoDB存储引擎会使用隐式的主键来进行锁定。
Next-Key Lock是结合了Gap Lock和Record Lock的一种锁定算法在Next-Key Lock算法下lnnoDB对于行的查询都是采用这种锁定算法。 当查询的索引含有唯一属性时InnoDB存储引擎会对Next-Key Lock进行优化将其降级为Record Lock,即仅锁住索引本身而不是范围。
