mysql使用读锁和写锁（MySQL内核对读写分离的支持--设置只读属性）

MySQL内核为读写分离的实现提供了支持，包括通过系统variable设置目标节点，session或者是事务的只读属性，等待/检查指定的事务是否已经apply到只读节点上，以及事务状态的实时动态跟踪等的能力，今天小编就来聊一聊关于mysql使用读锁和写锁?接下来我们就一起去研究一下吧!

mysql使用读锁和写锁

MySQL内核为读写分离的实现提供了支持，包括通过系统variable设置目标节点，session或者是事务的只读属性，等待/检查指定的事务是否已经apply到只读节点上，以及事务状态的实时动态跟踪等的能力。

今天主要分享下mysql内核对读写分离的支持特性，以下基于mysql5.7版本。

如需设置节点为只读状态，将该read_only参数设置为1或TRUE，但设置 read_only=1 状态有几个需要注意的地方：

read_only=1只读模式，不会影响slave同步复制的功能，所以在MySQL slave库中设定了read_only=1后，通过 show slave status\G 命令查看salve状态，可以看到salve仍然会读取master上的日志，并且在slave库中应用日志，保证主从数据库同步一致

read_only=1只读模式，可以限定普通用户进行数据修改的操作，但不会限定具有super权限的用户的数据修改操作

临时表的操作不受限制

log表（mysql.general_log和mysql.slow_log）的插入不受影响

Performance Schema表的update，例如update和truncate操作

ANALYZE TABLE或者OPTIMIZE TABLE语句 为了让所有的用户都不能进行读写操作，MySQL 5.6就需要执行给所有的表加读锁的命令 flush tables with read lock\G，这样使用具有super权限的用户登录数据库，想要发生数据变化的操作时，也会提示表被锁定不能修改的报错，同时，slave的同步复制也会受到影响。

在5.7之后，可以通过设置这个variable, 使得具有super权限的用户也不能对数据做修改操作，而不必通过flush tables with read locK\G的方式了。

把super_read_only设置成on， read_only会隐式的被设置成on；反过来，把read_only设置成off，super_read_only就会隐式的被设置成off。

如果这个variable设置为ON，事务的访问模式就变成了只读，不能对表做更新，但对临时表的更新操作仍然是允许的。

设置只读事务在引擎层可以走优化过的逻辑，相比读写事务的开销更小，例如不用分配事务id，不用分配回滚段，不用维护到全局事务链表中。

读写节点之间的数据通常是有gap的，如果有办法知道在主节点上的执行的事务已经被复制到了只读节点，对这（些）事务敏感的读操作就可以被路由到只读节点上，这就是“读一致性”。

MySQL 5.6 引入了GTID （GTID 实际上 是由 UUID TID 组成的。其中 UUID 是一个 MySQL 实例的唯一标识。TID 代表了该实例上已经提交的事务数量，并且随着事务提交单调递增。)，提升了MySQL节点复制的功能。

图：基于GTID的主从复制

MySQL 5.6提供了WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS(GTID_SET[,TIMEOUT])函数来等待从节点把GTID_SET指定事务都执行完毕，除非timeout（以秒为单位）的时间已经耗费而超时。 这个方法存在一些缺点，例如：

该功能依赖于slave来运行，如果复制线程没有启动或者出错了，就会返回错误。在某些情况下我们需要一直等待；

返回的是执行的事件的个数，这通常是没有意义的，返回成功或者失败即可。

MySQL 5.7为解决上面的几个问题，又添加了新的函数 WAIT_FOR_EXECUTED_GTID_SET\(GTID_SET[,TIMEOUT])。当GTID_SUBSET(GTID_SET, @@global.gtid_executed)成立时，即指定的GTID是gtid_executed的子集时，返回0表示成功，否则返回1，表示失败，如果超时，也会失败。

读写分离是MySQL实现负载均衡，保证高可用和高扩展性的重要手段，MySQL内核提供了对读写分离的多种手段的支持，从通过设置系统variable在事务，session，以及节点级别设置只读属性，到通过使用GTID和WAIT_FOR_EXECUTED_GTID_SET函数，都可以保证只读节点与主几点的读一致性。

后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~