Mysql高可用高性能存储应用系列2 - 深入理解锁和Mvcc

概述

Mysql数据库在处理并发中下了很多功夫，锁是为了更好的保护数据的正确和可靠，Mvcc是维持一个数据的多个版本，使得读写操作没有冲突的解决并发的数据库方案。

锁

当数据访问多了，就会出现并发的问题，Mysql锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构。

根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。

(资料图)

全局锁

全局锁就是对整个数据库实例加锁，当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

全局锁命令:

//加锁Flush tables with read lock;//释放锁命令unlock tables;

全局锁的典型使用场景是，做全库逻辑备份。也就是把整库每个表都 select 出来存成文本。

mysql> UPDATE runoob_tbl SET runoob_title="学习 C++" WHERE runoob_id=1;2013 - Lost connection to server during querymysql> INSERT INTO runoob_tbl (runoob_title, runoob_author, submission_date) VALUES ("学习 PHP", "菜鸟教程", NOW());2013 - Lost connection to server during querymysql> SELECT * FROM runoob_tbl;+-----------+--------------+---------------+-----------------+| runoob_id | runoob_title | runoob_author | submission_date |+-----------+--------------+---------------+-----------------+|         1 | Go 学习    | 菜鸟教程  | 2023-03-22      |+-----------+--------------+---------------+-----------------+1 row in set (0.01 sec)

释放全局锁后，所有的进程得到释放,需要注意的是如果是终端操作需要清空链接缓存，或者打开新链接重试，mysql释放锁在当前的链接中是不生效的。

mysql> use mysql2;Database changedmysql> INSERT INTO runoob_tbl (runoob_title, runoob_author, submission_date) VALUES ("学习 PHP", "菜鸟教程", NOW());Query OK, 1 row affected (0.03 sec)

表级锁

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

表锁是最常用的处理并发的方式。而对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大。

另一类表级的锁是 MDL（metadata lock)。

//加锁lock tables 表名 ... read/write;//释放锁unlock tables;

表级锁分读锁和写锁，1)读锁，在进行读锁时，读不会受到影响但是会阻塞其他进程的insert、update操作。

mysql> lock tables runoob_tbl read;Query OK, 0 rows affected (0.05 sec) mysql> select * from runoob_tbl;+-----------+--------------+---------------+-----------------+| runoob_id | runoob_title | runoob_author | submission_date |+-----------+--------------+---------------+-----------------+|         1 | 学习 C++   | 菜鸟教程  | 2023-03-22      ||         2 | 学习 PHP   | 菜鸟教程  | 2023-03-22      ||         3 | 学习 PHP   | 菜鸟教程  | 2023-03-22      |+-----------+--------------+---------------+-----------------+3 rows in set (0.02 sec)//写操作mysql> INSERT INTO runoob_tbl (runoob_title, runoob_author, submission_date) VALUES ("学习 PHP", "菜鸟教程", NOW());2013 - Lost connection to server during querymysql> UPDATE runoob_tbl SET runoob_title="学习 C++" WHERE runoob_id=1;2013 - Lost connection to server during query

2)写锁，对指定表加了写锁，会阻塞右侧客户端的读和写。

mysql> lock tables runoob_tbl write;Query OK, 0 rows affected (0.02 sec)mysql> unlock tables;Query OK, 0 rows affected (0.03 sec)

行锁

MySQL 的行锁是在引擎层由各个引擎自己实现的,但并不是所有的引擎都支持行锁，比如 MyISAM 引擎就不支持行锁。

行锁就是针对数据表中行记录的锁。这很好理解，比如事务 A 更新了一行，而这时候事务 B 也要更新同一行，则必须等事务 A 的操作完成后才能进行更新。

两阶段锁协议：在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作，要耗费大量的 CPU 资源，应该在逻辑上进行优化。

Mvcc

因为加锁会影响效率，MVCC全称多版本并发控制(Multiversion concurrency control， MCC 或 MVCC)，是数据库管理系统常用的一种并发控制，理念是维持一个数据的多个版本，使得读写操作没有冲突的解决并发的数据库方案。

当前读和快照读

Mvcc 把事务的执行语句分为当前读和快照读。

//select * from Table //快照读//Insert Update Delete //当前读//Select ... lock in share mode //当前读//Select ... for update //当前读

事务的隔离解决有四种，可重复读(RR)、读已提交(RC)、读未提交、序列化,查看全局隔离权限语句，旧版的myql使用tx开头，否则报错1193 - Unknown system variable "tx_isolation"。

mysql> show variables like "transaction_isolation";+-----------------------+-----------------+| Variable_name         | Value           |+-----------------------+-----------------+| transaction_isolation | REPEATABLE-READ |+-----------------------+-----------------+1 row in set (0.07 sec)mysql> select @@transaction_isolation;+-------------------------+| @@transaction_isolation |+-------------------------+| REPEATABLE-READ         |+-------------------------+

如果没有设置隔离级别，可使用下面语句进行设置。

mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;Query OK, 0 rows affected (0.03 sec)

MVCC的实现原理

MVCC的目的就是多版本并发控制，在数据库中的实现，就是为了解决读写冲突，它的实现原理主要是依赖记录中的 3个隐式字段，undo日志 ，Read View 来实现的。

每行记录除了我们自定义的字段外，还有数据库隐式定义的DB_TRX_ID,DB_ROLL_PTR,DB_ROW_ID等字段。

read-view:事务在快照时产生的读视图。

Mvcc判断的规则：

1.比较DB_TRX_ID和up_limit_id，如果小于，则当前事务能看到DB_TRX_ID的记录，如果大于和等于，则进入下一个判断

2.比较DB_TRX_ID和low_limit_id，如果大于等于则代表DB_TRX_ID的记录在read-view生成后出现的，那么对于当前事务不可见。如果小于，则进入下一个判断。

3.判断DB_TRX_ID是否在活跃事务中，如果在，代表read-view生成时，事务还在活跃状态，修改的数据当前的事务是看不到的，如果不在，说明事务在read-view之前就commit了，那么修改的结果就是可见的。

可重复读（repeatable read）：每次进行快照读时都生成读视图。

读已提交（read committed）：只有第一次时生成读视图，之后没次都使用第一次时的读视图。

Mysql ( Innodb引擎 ) 保证数据的一致性

1.执行更新语句update table set a= 1 where id = 2

2.将id=2的行上的列值改为1

3.将修改更新到内存中

4.记录在第N个Page的地方做修改，并将这行记录状态为prepare

5.修改好了，可以提交事务了

6.写入binlog

7.commit，提交事务

8.将redo log里这个事务的相关记录状态置为commit状态

这个数据提交过程，就是两阶段提交，在恢复数据时，用binlog和redolog两部分来比较做数据恢复就可以了。

事务

事务的特性:

问题

1.既然有了redolog,为什么还要有binlog呢？

redolog依赖于搜索引擎层，并不是每一个引擎都有redolog，binlog是属于MysqlServer层。

关键词：