第二十二天：mysql并发控制及事务日志
一、锁机制
锁类型：
读锁：共享锁，也称为 S 锁,只读不可写（包括当前事务） ，多个读互不阻塞
写锁：独占锁，排它锁，也称为 X 锁,写锁会阻塞其它事务（不包括当前事务）的读和写
S 锁和 S 锁是兼容的，X 锁和其它锁都不兼容，举个例子，事务 T1 获取了一个行 r1 的 S 锁，另外事务 T2 可以立即获得行 r1 的 S 锁，此时 T1 和 T2 共同获得行 r1 的 S 锁，此种情况称为锁兼容，但是另外一个事务 T2 此时如果想获得行 r1 的 X 锁，则必须等待 T1 对行 r1 锁的释放，此种情况也称为锁冲突
锁粒度：
表级锁：MyISAM
行级锁：InnoDB
实现
存储引擎：自行实现其锁策略和锁粒度
服务器级：实现了锁，表级锁，用户可显式请求
分类：
隐式锁：由存储引擎自动施加锁
显式锁：用户手动请求
锁策略：在锁粒度及数据安全性寻求的平衡机制
二、显式使用锁
帮助:https://mariadb.com/kb/en/lock-tables/
加锁
LOCK TABLES tbl_name [[AS] alias] lock_type [, tbl_name [[AS] alias] lock_type] ... lock_type: READ #读锁 WRITE #写锁
解锁
UNLOCK TABLES
关闭正在打开的表（清除查询缓存），通常在备份前加全局读锁
FLUSH TABLES [tb_name[,...]] [WITH READ LOCK]
查询时加写或读锁
SELECT clause [FOR UPDATE | LOCK IN SHARE MODE]
三、事务
事务 Transactions：一组原子性的 SQL语句，或一个独立工作单元
事务日志：记录事务信息，实现undo,redo等故障恢复功能
1、事务特性
ACID特性：
A：atomicity 原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚
C：consistency一致性；数据库总是从一个一致性状态转换为另一个一致性状态,类似于能量守恒定律(N50周启皓语录)
I：Isolation隔离性；一个事务所做出的操作在提交之前，是不能为其它事务所见；隔离有多种隔离级别，实现并发
D：durability持久性；一旦事务提交，其所做的修改会永久保存于数据库中
Transaction 生命周期
2、管理事务
显式启动事务：
BEGIN BEGIN WORK START TRANSACTION
结束事务：
#提交,相当于vi中的wq保存退出 COMMIT #回滚,相当于vi中的q!不保存退出 ROLLBACK
注意：只有事务型存储引擎中的DML语句方能支持此类操作
自动提交：
set autocommit={1|0}
默认为1，为0时设为非自动提交
建议：显式请求和提交事务，而不要使用"自动提交"功能
事务支持保存点
SAVEPOINT identifier ROLLBACK [WORK] TO [SAVEPOINT] identifier RELEASE SAVEPOINT identifier
查看事务：
#查看当前正在进行的事务 SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX; #以下两张表在MySQL8.0中已取消 #查看当前锁定的事务 SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; #查看当前等锁的事务 SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
死锁：
两个或多个事务在同一资源相互占用，并请求锁定对方占用的资源的状态
范例：找到未完成的导致阻塞的事务(支持Mariadb)
#在第一会话中执行 MariaDB [hellodb]> begin; Query OK, 0 rows affected (0.000 sec) MariaDB [hellodb]> update students set classid=10; #在第二个会话中执行 MariaDB [hellodb]> update students set classid=20; #在第三个会话中执行 MariaDB [hellodb]> show engine innodb status;
（3）事务隔离级别
MySQL 支持四种隔离级别，事务隔离级别从上至下更加严格
READ UNCOMMITTED
可读取到未提交数据，产生脏读
READ COMMITTED
可读取到提交数据，但未提交数据不可读，产生不可重复读，即可读取到多个提交数据，导致每次读取数据不一致
REPEATABLE READ
可重复读，多次读取数据都一致，产生幻读，即读取过程中，即使有其它提交的事务修改数据，仍只能读取到未修改前的旧数据。此为MySQL默认设置
SERIALIZABLE
可串行化，未提交的读事务阻塞写事务（加读锁，但不阻塞读事务），或者未提交的写事务阻塞读和写事务（加写锁，其它事务的读，写都不可以执行）。会导致并发性能差
MVCC和事务的隔离级别：
MVCC（多版本并发控制机制）只在READ COMMITTED和REPEATABLE READ两个隔离级别下工作。其他两个隔离级别都和MVCC不兼容,因为READ UNCOMMITTED总是读取最新的数据行，而不是符合当前事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁
指定事务隔离级别：
服务器变量tx_isolation(MySQL8.0改名为transaction_isolation)指定，默认为REPEATABLE-READ，可在GLOBAL和SESSION级进行设置
#MySQL8.0之前版本 SET tx_isolation='READ-UNCOMMITTED|READ-COMMITTED|REPEATABLEREAD|SERIALIZABLE' #MySQL8.0 SET transaction_isolation='READ-UNCOMMITTED|READ-COMMITTED|REPEATABLEREAD|SERIALIZABLE'
服务器选项中指定
vim /etc/my.cnf [mysqld] transaction-isolation=SERIALIZABLE
四、日志管理
MySQL 支持丰富的日志类型，如下：
事务日志：transaction log
事务日志的写入类型为"追加"，因此其操作为"顺序IO"；通常也被称为：预写式日志 write aheadlogging
事务日志文件： ib_logfile0， ib_logfile1
错误日志 error log
通用日志 general log
慢查询日志 slow query log
二进制日志 binary log
中继日志 reley log，在主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取的事件
1、事务日志
事务日志：transaction log
redo log：记录某数据块被修改后的值，数据更新前先记录redo log( WALWrite Ahead Log ),可以用来恢复未写入data file的已成功事务更新的数据
undo log：保存与执行的操作相反的操作,即记录某数据被修改前的值，可以用来在事务失败时进行rollback
事务型存储引擎自行管理和使用，建议和数据文件分开存放
Innodb事务日志相关配置：
show variables like '%innodb_log%'; innodb_log_file_size 50331648 #每个日志文件大小 innodb_log_files_in_group 2 #日志组成员个数 innodb_log_group_home_dir ./ #事务文件路径
事务日志性能优化
innodb_flush_log_at_trx_commit=0|1|2
1 此为默认值，日志缓冲区将写入日志文件，并在每次事务后执行刷新到磁盘。 这是完全遵守ACID特性 0 提交时没有写磁盘的操作; 而是每秒执行一次将日志缓冲区的提交的事务写入刷新到磁盘。 这样可提供 更好的性能，但服务器崩溃可能丢失最后一秒的事务 2 每次提交后都会写入OS的缓冲区，但每秒才会进行一次刷新到磁盘文件中。 性能比0略差一些，但操作系 统或停电可能导致最后一秒的交易丢失
高并发业务行业最佳实践，是使用第三种折衷配置（=2）：
1.配置为2和配置为0，性能差异并不大，因为将数据从Log Buffer拷贝到OS cache，虽然跨越用户态与内 核态，但毕竟只是内存的数据拷贝，速度很快 2.配置为2和配置为0，安全性差异巨大，操作系统崩溃的概率相比MySQL应用程序崩溃的概率，小很多，设置 为2，只要操作系统不奔溃，也绝对不会丢数据
2、错误日志
错误日志
mysqld启动和关闭过程中输出的事件信息
mysqld运行中产生的错误信息
event scheduler运行一个event时产生的日志信息
在主从复制架构中的从服务器上启动从服务器线程时产生的信息
错误文件路径
SHOW GLOBAL VARIABLES LIKE 'log_error' ;
记录哪些警告信息至错误日志文件
#CentOS7 mariadb 5.5 默认值为1 #CentOS8 mariadb 10.3 默认值为2 log_warnings=0|1|2|3... #MySQL5.7之前 log_error_verbosity=0|1|2|3... #MySQL8.0
3、通用日志
通用日志：记录对数据库的通用操作，包括:错误的SQL语句
通用日志可以保存在：file（默认值）或 table（mysql.general_log表）
通用日志相关设置
general_log=ON|OFF general_log_file=HOSTNAME.log log_output=TABLE|FILE|NONE
4、慢查询日志
慢查询日志：记录执行查询时长超出指定时长的操作
慢查询相关变量
slow_query_log=ON|OFF #开启或关闭慢查询，支持全局和会话，只有全局设置才会生成慢查询文件 long_query_time=N #慢查询的阀值，单位秒,默认为10s slow_query_log_file=HOSTNAME-slow.log #慢查询日志文件 log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan, query_cache,query_cache_miss,tmp_table,tmp_table_on_disk #上述查询类型且查询时长超过long_query_time，则记录日志 log_queries_not_using_indexes=ON #不使用索引或使用全索引扫描，不论是否达到慢查询阀值的语 句是否记录日志，默认OFF，即不记录 log_slow_rate_limit = 1 #多少次查询才记录，mariadb特有 log_slow_verbosity= Query_plan,explain #记录内容 log_slow_queries = OFF #同slow_query_log，MariaDB 10.0/MySQL 5.6.1 版后已删除
5、二进制日志(备份)
记录导致数据改变或潜在导致数据改变的SQL语句
记录已提交的日志
不依赖于存储引擎类型
功能：通过"重放"日志文件中的事件来生成数据副本
注意：建议二进制日志和数据文件分开存放
二进制日志记录三种格式
基于"语句"记录：statement，记录语句，默认模式（ MariaDB 10.2.3 版本以下 ），日志量较少
基于"行"记录：row，记录数据，日志量较大，更加安全，建议使用的格式,MySQL8.0默认格式
混合模式：mixed, 让系统自行判定该基于哪种方式进行，默认模式（ MariaDB 10.2.4及版本以上）
二进制日志文件的构成
有两类文件 1.日志文件：mysql|mariadb-bin.文件名后缀，二进制格式,如： on.000001,mariadb-bin.000002 2.索引文件：mysql|mariadb-bin.index，文本格式,记录当前已有的二进制日志文件列表
二进制日志相关的服务器变量：
sql_log_bin=ON|OFF：#是否记录二进制日志，默认ON，支持动态修改，系统变量，而非服务器选项 log_bin=/PATH/BIN_LOG_FILE：#指定文件位置；默认OFF，表示不启用二进制日志功能，上述两项都开启才可以 binlog_format=STATEMENT|ROW|MIXED：#二进制日志记录的格式，mariadb5.5默认STATEMENT max_binlog_size=1073741824：#单个二进制日志文件的最大体积，到达最大值会自动滚动，默认为1G #说明：文件达到上限时的大小未必为指定的精确值 binlog_cache_size=4m #此变量确定在每次事务中保存二进制日志更改记录的缓存的大小（每次连接） max_binlog_cache_size=512m #限制用于缓存多事务查询的字节大小。 sync_binlog=1|0：#设定是否启动二进制日志即时同步磁盘功能，默认0，由操作系统负责同步日志到磁盘 expire_logs_days=N：#二进制日志可以自动删除的天数。 默认为0，即不自动删除
二进制日志相关配置
查看mariadb自行管理使用中的二进制日志文件列表，及大小
SHOW {BINARY | MASTER} LOGS
查看使用中的二进制日志文件
SHOW MASTER STATUS
在线查看二进制文件中的指定内容
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
mysqlbinlog：二进制日志的客户端命令工具，支持离线查看二进制日志
命令格式：
--start-position=# 指定开始位置 --stop-position=# --start-datetime= #时间格式：YYYY-MM-DD hh:mm:ss --stop-datetime= --base64-output[=name] -v -vvv
二进制日志事件的格式：
# at 328 #151105 16:31:40 server id 1 end_log_pos 431 Query thread_id=1 exec_time=0 error_code=0 use `mydb`/*!*/; SET TIMESTAMP=1446712300/*!*/; CREATE TABLE tb1 (id int, name char(30)) /*!*/; 事件发生的日期和时间：151105 16:31:40 事件发生的服务器标识：server id 1 事件的结束位置：end_log_pos 431 事件的类型：Query 事件发生时所在服务器执行此事件的线程的ID：thread_id=1 语句的时间戳与将其写入二进制文件中的时间差：exec_time=0 错误代码：error_code=0 事件内容： GTID：Global Transaction ID，mysql5.6以mariadb10以上版本专属属性：GTID
清除指定二进制日志
PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }
删除所有二进制日志，index文件重新记数
RESET MASTER [TO #]; #删除所有二进制日志文件，并重新生成日志文件，文件名从#开始记数，默认从 1开始，一般是master主机第一次启动时执行，MariaDB 10.1.6开始支持TO #
切换日志文件：
FLUSH LOGS;
