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]
范例: 加读锁
mysql> lock tables students read;
Query OK, 0 rows affected (0.00 sec)
mysql> update students set classid=2 where stuid=24;
ERROR 1099 (HY000): Table 'students' was locked with a READ lock and can't be
updated
mysql> unlock tables ;
mysql> update students set classid=2 where stuid=24;
Query OK, 1 row affected (1 min 45.52 sec)
Rows matched: 1 Changed: 1 Warnings: 0
范例: 同时在两个终端对同一行记录修改
#同时对同一行记录执行update
#在第一终端提示1行成功
MariaDB [hellodb]> update students set classid=1 where stuid=24;
Query OK, 1 row affected (0.002 sec)
Rows matched: 1 Changed: 1 Warnings: 0
#在第二终端提示0行修改
MariaDB [hellodb]> update students set classid=1 where stuid=24;
Query OK, 0 rows affected (0.000 sec)
Rows matched: 1 Changed: 0 Warnings: 0
事务
事务 Transactions：一组原子性的 SQL语句，或一个独立工作单元
事务日志：记录事务信息，实现undo,redo等故障恢复功能
事务特性
ACID特性：
- A：atomicity 原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚
- C：consistency一致性；数据库总是从一个一致性状态转换为另一个一致性状态,类似于能量守恒
定律 - I：Isolation隔离性；一个事务所做出的操作在提交之前，是不能为其它事务所见；隔离有多种隔
离级别，实现并发 - D：durability持久性；一旦事务提交，其所做的修改会永久保存于数据库中
Transaction 生命周期
管理事务
显式启动事务：
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 INFOMATION_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;
...省略...
---TRANSACTION 120, ACTIVE 673 sec
2 lock struct(s), heap size 1136, 28 row lock(s), undo log entries 27
MySQL thread id 13, OS thread handle 139719808595712, query id 206 localhost
root
...省略...
#此指令不支持MySQL8.0
MariaDB [hellodb]> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
+-----------+-------------+-----------+-----------+----------------------+------
------+------------+-----------+----------+-----------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table |
lock_index | lock_space | lock_page | lock_rec | lock_data |
+-----------+-------------+-----------+-----------+----------------------+------
------+------------+-----------+----------+-----------+
| 123:9:3:2 | 123 | X | RECORD | `hellodb`.`students` |
PRIMARY | 9 | 3 | 2 | 1 |
| 120:9:3:2 | 120 | X | RECORD | `hellodb`.`students` |
PRIMARY | 9 | 3 | 2 | 1 |
+-----------+-------------+-----------+-----------+----------------------+------
------+------------+-----------+----------+-----------+
2 rows in set (0.001 sec)
#此指令不支持MySQL8.0
MariaDB [hellodb]> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 123 | 123:9:3:2 | 120 | 120:9:3:2 |
+-------------------+-------------------+-----------------+------------------+
2 rows in set (0.001 sec)
1 row in set (0.000 sec)
#查看正在进行的事务
MariaDB [hellodb]> SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
trx_id: 123
trx_state: LOCK WAIT
trx_started: 2019-11-22 19:17:06
trx_requested_lock_id: 123:9:3:2
trx_wait_started: 2019-11-22 19:18:50
trx_weight: 2
trx_mysql_thread_id: 15 #线程ID
trx_query: update students set classid=20
trx_operation_state: starting index read
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 2
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
trx_id: 120
trx_state: RUNNING
trx_started: 2019-11-22 19:08:51
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 29
trx_mysql_thread_id: 13 #线程ID
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 28
trx_rows_modified: 27
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.000 sec)
MariaDB [hellodb]> show processlist;
+----+-------------+-----------+---------+---------+------+---------------------
-----+------------------+----------+
| Id | User | Host | db | Command | Time | State
| Info | Progress |
+----+-------------+-----------+---------+---------+------+---------------------
-----+------------------+----------+
| 1 | system user | | NULL | Daemon | NULL | InnoDB purge
coordinator | NULL | 0.000 |
| 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker
| NULL | 0.000 |
| 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker
| NULL | 0.000 |
| 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker
| NULL | 0.000 |
| 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown
handler | NULL | 0.000 |
| 11 | root | localhost | hellodb | Query | 0 | Init
| show processlist | 0.000 |
| 13 | root | localhost | hellodb | Sleep | 38 |
| NULL | 0.000 |
| 15 | root | localhost | hellodb | Query | 10 | Updating
| update students set classid=20 | 0.000
+----+-------------+-----------+---------+---------+------+---------------------
-----+------------------+----------+
7 rows in set (0.000 sec)
#杀掉未完成的事务
MariaDB [hellodb]> kill 13;
Query OK, 0 rows affected (0.000 sec)
#查看事务锁的超时时长，默认50s
MariaDB [hellodb]> show global variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50 |
+--------------------------+-------+
1 row in set (0.001 sec)
事务隔离级别
MySQL 支持四种隔离级别，事务隔离级别从上至下更加严格
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 加读锁 |
|---|---|---|---|---|
| 读未提交 (Read Uncommitted) | 可以出现 | 可以出现 | 可以出现 | 否 |
| 读提交 (Read Committed) | 不允许出现 | 可以出现 | 可以出现 | 否 |
| 可重复读 (Repeatable Read) | 不允许出现 | 不允许出现 | 可以出现 | 否 |
| 序列化 (Serializable) | 不允许出现 | 不允许出现 | 不允许出现 | 是 |
- 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|REPEATABLE-READ|SERIALIZABLE'
#MySQL8.0
SET transaction_isolation='READ-UNCOMMITTED|READ-COMMITTED|REPEATABLE-READ|SERIALIZABLE'
- 服务器选项中指定
vim /etc/my.cnf
[mysqld]
transaction-isolation=SERIALIZABLE
范例: MySQL8.0 事务隔离级别系统变量tx_isolation已取消
mysql> select @@tx_isolation;
ERROR 1193 (HY000): Unknown system variable 'tx_isolation'
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ |
+-------------------------+
1 row in set (0.00 sec)
日志管理
MySQL 支持丰富的日志类型，如下：
- 事务日志：transaction log
- 事务日志的写入类型为"追加"，因此其操作为"顺序IO"；通常也被称为：预写式日志 write ahead
logging - 事务日志文件： ib_logfile0， ib_logfile1
- 事务日志的写入类型为"追加"，因此其操作为"顺序IO"；通常也被称为：预写式日志 write ahead
- 错误日志 error log
- 通用日志 general log
- 慢查询日志 slow query log
- 二进制日志 binary log
- 中继日志 reley log，在主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取的事件
事务日志
事务日志：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，只要操作系统不奔溃，也绝对不会丢数据
说明：
- 设置为1，同时sync_binlog = 1表示最高级别的容错“双 1”配置（最高级别容错） 就是慢
- innodb_use_global_flush_log_at_trx_commit=0 时，将不能用SET语句重置此变量（ MariaDB10.2.6 后废弃）
错误日志
错误日志
- mysqld启动和关闭过程中输出的事件信息
- mysqld运行中产生的错误信息
- event scheduler运行一个event时产生的日志信息
- 在主从复制架构中的从服务器上启动从服务器线程时产生的信息
错误文件路径
SHOW GLOBAL VARIABLES LIKE 'log_error' ;
范例：
MariaDB [hellodb]> SHOW GLOBAL VARIABLES LIKE 'log_error';
+---------------+------------------------------+
| Variable_name | Value |
+---------------+------------------------------+
| log_error | /var/log/mariadb/mariadb.log |
+---------------+------------------------------+
1 row in set (0.001 sec)
记录哪些警告信息至错误日志文件
#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
范例: MySQL8.0变量变化
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_log_error_verbosity
| log_error_verbosity 值 | 允许记录的消息优先级 (Permitted Message Priorities) |
|---|---|
| 1 | ERROR |
| 2 | ERROR, WARNING |
| 3 | ERROR, WARNING, INFORMATION |
mysql> SHOW GLOBAL VARIABLES LIKE 'log_error_verbosity';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| log_error_verbosity | 2 |
+---------------------+-------+
1 row in set (0.00 sec)
通用日志
通用日志：记录对数据库的通用操作，包括:错误的SQL语句
通用日志可以保存在：file（默认值）或 table（mysql.general_log表）
通用日志相关设置
general_log=ON|OFF
general_log_file=HOSTNAME.log
log_output=TABLE|FILE|NONE
范例: 启用通用日志并记录至文件中
#默认没有启用通用日志
mysql> select @@general_log;
+---------------+
| @@general_log |
+---------------+
| 0 |
+---------------+
1 row in set (0.00 sec)
#启用
mysql> set global general_log=1;
Query OK, 0 rows affected (0.01 sec)
mysql> select @@general_log;
+---------------+
| @@general_log |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
#默认通用日志存放在文件中
mysql> SHOW GLOBAL VARIABLES LIKE 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+
1 row in set (0.00 sec)
#通用日志存放的文件路径
mysql> select @@general_log_file;
+----------------------------+
| @@general_log_file |
+----------------------------+
| /var/lib/mysql/centos8.log |
+----------------------------+
1 row in set (0.00 sec)
范例：通用日志记录到表中
#修改通用日志，记录通用日志至mysql.general_log表中
MariaDB [mysql]> set global log_output="table";
MariaDB [mysql]> SHOW GLOBAL VARIABLES LIKE 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | TABLE |
+---------------+-------+
1 row in set (0.002 sec)
#general_log表是CSV格式的存储引擎
mysql> show table status like 'general_log'\G
*************************** 1. row ***************************
Name: general_log
Engine: CSV
Version: 10
Row_format: Dynamic
Rows: 1
Avg_row_length: 0
Data_length: 0
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2026-02-28 08:56:13
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment: General log
1 row in set (0.01 sec)
#general_log表是CSV的文本文件
[root@centos8 ~]#file /var/lib/mysql/mysql/general_log.CSV
/var/lib/mysql/mysql/general_log.CSV: ASCII text
[root@centos8 ~]#head /var/lib/mysql/mysql/general_log.CSV
"2021-02-05 10:01:42.506955","root[root] @ localhost []",8,1,"Query","select
count(*) from general_log"
"2021-02-05 10:01:48.436410","root[root] @ localhost []",8,1,"Query","select *
from general_log"
"2021-02-05 10:01:57.619597","root[root] @ localhost []",8,1,"Query","select *
from students"
"2021-02-05 10:02:03.628124","root[root] @ localhost []",8,1,"Query","SELECT
DATABASE()"
"2021-02-05 10:02:03.628309","root[root] @ localhost []",8,1,"Init DB","hellodb"
"2021-02-05 10:02:03.629031","root[root] @ localhost []",8,1,"Query","show
databases"
"2021-02-05 10:02:03.629901","root[root] @ localhost []",8,1,"Query","show
tables"
MariaDB [mysql]> select * from mysql.general_log\G
...省略...
*************************** 6. row ***************************
event_time: 2019-11-25 11:03:41.163896
user_host: root[root] @ localhost []
thread_id: 9
server_id: 1
command_type: Query
argument: xxx
*************************** 7. row ***************************
event_time: 2019-11-25 11:03:44.549211
user_host: root[root] @ localhost []
thread_id: 8
server_id: 1
command_type: Query
argument: select * from general_log
7 rows in set (0.000 sec)
#注意:MySQL8.0 argument字段是加密形式
范例: 查找执行次数最多的前三条语句
MariaDB [mysql]> select argument,count(argument) num from mysql.general_log
group by argument order by num desc limit 3;
+---------------------------+-----------------+
| argument | num |
+---------------------------+-----------------+
| select * from teachers | 6 |
| select * from general_log | 4 |
| select * from students | 3 |
+---------------------------+-----------------+
3 rows in set (0.002 sec)
范例: Mariadb对访问的语句进行排序
ysql -e 'select argument from mysql.general_log' | awk'{sql[$0]++}END{for(i in sql){print sql[i],i}}'|sort -nr
[root@centos8 ~]#mysql -e 'select argument from mysql.general_log' |sort |uniq -
c |sort -nr
范例: MySQL8.0对访问的语句进行排序
[root@rocky8 ~]#awk -F"," '{sql[$NF]++}END{for(i in sql){print sql[i],i}}'/var/lib/mysql/mysql/general_log.CSV |sort -nr|head
7 "select * from students"
3 "show tables"
3 "select * from teachers"
2 "show table status like 'general_log'"
2 "show databases"
2 "SELECT DATABASE()"
1 "user "
1 "toc "
1 "time_zone_transition_type "
1 "time_zone_transition "
[root@rocky8 mysql]#
慢查询日志
慢查询日志：记录执行查询时长超出指定时长的操作
慢查询相关变量
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 版后已删除
范例: 慢查询分析工具mysqldumpslow
[root@centos8 ~]#mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
Parse and summarize the MySQL slow query log. Options are
--verbose verbose
--debug debug
--help write this text to standard output
-v verbose
-d debug
-s ORDER what to sort by (aa, ae, al, ar, at, a, c, e, l, r, t), 'at' is
default
aa: average rows affected
ae: aggregated rows examined
al: average lock time
ar: average rows sent
at: average query time
a: rows affected
c: count
e: rows examined
l: lock time
r: rows sent
t: query time
-r reverse the sort order (largest last instead of first)
-t NUM just show the top n queries
-a don't abstract all numbers to N and strings to 'S'
-n NUM abstract numbers with at least n digits within names
-g PATTERN grep: only consider stmts that include this string
-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
default is '*', i.e. match all
-i NAME name of server instance (if using mysql.server startup script)
-l don't subtract lock time from total time
[root@centos8 ~]#mysqldumpslow -s c -t 2 /var/lib/mysql/centos8-slow.log
Reading mysql slow query log from /var/lib/mysql/centos8-slow.log
Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows_sent=2.0 (2),
Rows_examined=25.0 (25), Rows_affected=0.0 (0), root[root]@localhost
select * from students where age=N
Count: 1 Time=4.00s (4s) Lock=0.00s (0s) Rows_sent=4.0 (4), Rows_examined=4.0
(4), Rows_affected=0.0 (0), root[root]@localhost
select sleep(N) from teachers