MySQL从入门到高级 --- 12.事务 && 13.锁机制 && 14.日志

第十二章 && 第十三章 && 第十四章：

12.事务

• 可用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么都不执行

• 事务用来管理DDL 、DML 、DCL操作，如insert、update、delete语句，默认自动提交

• MySQL中事务由存储引擎实现，在MySQL只有InnoDB存储引擎才支持事务

操作：

1. 开启事务：BEGIN或START TRANSACTION，任何一条DML语句执行，标志着事务的开启

2. 提交事务：COMMIT TRANSACTION，成功的结束，将所有DML语句操作历史记录和底层硬盘数据来一次同步

3. 回滚事务：ROLLBACK TRANSACTION，失败的结束，将所有DML语句操作历史记录全部清空

改变MySQL自动提交模式

语法：set autocommit=0 – 禁止自动提交

        set autocommit=1 -- 开启自动提交

12.1 特性

• 原子性

• 一致性

• 隔离性

• 持久性

12.2 隔离级别

隔离：将事务与另一个事务隔离开

作用：一个事务正在操作的数据被另一事务修改或删除，最后执行结果可能无法达到预期。如果没有隔离性还会导致其他的问题。

• 读未提交(read uncommitted)：一个事务可以读取到另一个未提交事务的数据，最低级别，任何情况都无法保证，会造成脏读。

• 读已提交(committed)：一个事务要等另一个事务提交后才能读取数据，可避免到脏读的发生，会造成不可重复读。

• 可重复读(repeatable read)：在开始读取数据时，不再允许修改操作，可避免脏读、不可重复值的发生，会造成幻读。

• 串行化(erializable)：最高的事务隔离级别，在该级别下，事务串行化顺序执行，可避免脏读、不可重复读和幻读。但该隔离级别效率低，耗数据库性能，一般不用。

MySQL默认隔离级别是可重复读。

事务隔离级别	脏读	不可重复读	幻读
读未提交	是	是	是
读已提交	否	是	是
可重复读	否	否	是
串行化	否	否	否

13.锁机制

锁是计算机协调多个进程或线程并发访问某一资源的机制

锁冲突也是影响数据库并发访问性能的一个重要因素

按数据操作的粒度分类：

• 表锁：操作时，会锁定整个表

• 行锁：操作时，会锁定当前操作行

按数据操作的类型分类：

读锁 (共享锁) ：针对同一份数据，多个读操作可同时进行而不会互相影响

写锁 (排它锁) ：当前操作没完成前，它会阻断其他写锁和读锁

13.1 各存储引擎对锁的支持状况：

存储引擎	表级锁	行级锁
MyISAM	支持	不支持
InnoDB	支持	支持
MEMORY	支持	不支持
BDB	支持	不支持

13.2 锁特性

锁类型	特性
表级锁	偏向MyISAM存储引擎，开销小，加锁快，不会出现死锁；锁定粒度最大，发生锁冲突的概率最高，并发度最低
行级锁	偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高

• 表级锁适用于以查询为主，只有少量按索引条件更新数据的应用

• 行级锁适用于有大量按索引条件并发跟新少量不同数据，同时又有并查询的应用

13.3 MyISAM表锁

13.3.1 加表锁

• MyISAM在执行查询语句前，会自动给涉及的所有表加读锁，在执行更新操作前，会自动给涉及的表加写锁

lock table table_name read;  -- 加读锁

lock table table_name write; -- 加写锁

13.4 InnoDB行锁

13.4.1 行锁特点

• 偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高

InnoDB与MyISAM最大不同的两点：

1. 支持事务

2. 采用行级锁

两种类型行锁：

• 共享锁 (S) ：又称读锁，简称S锁，共享锁就是多个事务对于同一数据可共享一把锁，都能访问数据，但只能读不能修改

• 排他锁 (X) ：又称写锁，简称X锁，排他锁就是不能与其他锁并存

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁；对于普通SELECT语句，InnoDB不会加任何锁

13.4.2 行锁模式

SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE -- 共享
SELECT * FROM table_name WHERE ... FOR UPDATE -- 排他锁

• 显示给记录集加共享锁或排他锁

14.日志

作用：记录着数据库工作的方方面面，帮助数据库管理员追踪数据库曾经发生过的各种事件

分类：

• 错误日志

• 二进制日志

• 查询日志

• 慢查询日志

14.1 错误日志

它记录了mysqld启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息

错误日志默认开启，默认存放目录为mysql的数据目录，默认日志文件名为hostname.err(hostname 主机名)

语法：show variables like ‘log_error%’;

• 该指令查看日志位置

14.2 二进制日志

它记录了所有的DDL语句和DML语句，但不包括数据查询语句，该日志对灾难时的数据恢复起着重要作用，MySQL主从复制，就是通过该日志实现

二进制文件，8.0版本默认已开启，低版本需通过配置文件开启，并配置MySQL日志的格式

Windows：my.ini Linux：my.cnf

-- 配置开启binlog日志，日志文件前缀为mysqlbin
log_bin=mysqlbin

-- 配置二进制日志的格式
binlog_format=STATEMENT


-- 在my.ini文件中进行配置，配置完后需重启服务

14.2.1 日志格式

• STATEMENT：记录的都是SQL语句，每一条对数据进行修改的SQL都会被记录在日志文件中

• ROW：记录的是每一行的数据变更

• MIXED：STATEMENT和ROW两种格式的混合

14.3 查询日志

查询日志中记录了客户端的所有操作语句

默认情况下，查询日志未开启，若需要开启，可进行配置

-- 该选项用作开启查询日志 1：开启 0：关闭
general_log=1

-- 设置日志的文件名，若没指定，默认文件名为host_name.log
general_log_file=file_name

14.4 慢查询日志

它记录了所有执行事件超过参数long_query_time 设置值并且扫描记录不小于min_examined_row_limit 的所有QL语句的日志。

long_query_time默认为10秒，最小为0，精度可达到微秒

-- 控制慢查询日志是否开启 1：开启 0：关闭
slow_query_log=1

-- 指定慢查询日志的文件名
slow_query_log_file=slow_query.log

-- 配置查询的时间限制，超过该时间将被认定慢查询，将需要进行日志记录，默认10秒
long_query_time=10