SQL复习（五）-- 事务（进阶）& 锁 & 日志

1 事务

1.1 概念及特性

事务, Transaction, 通常被简写为tx. 在某些情况下, 一个操作有可能涉及到多条DML语句. 例如银行转账, 此时, 要求多条DML要么同时成功, 要么同时失败. 事务有四大特性:

• 原子性(A): Automicity, 事务是操作数据库的最小单元, 不可分割.
• 一致性(C): Consistency, 事务执行前后状态要保持一致.即数据不会被破坏。如A转账100元给B，不管操作是否成功，A和B的账户总额是不变的。
• 隔离性(I): Isolation, 多个事务之间项目独立, 互不影响.
• 持久性(D): Durability, 事务一旦提交, 数据将永久保存, 不可逆.

1.2 MySQL的事务管理

默认情况下, MySQL的事务是自动被提交的. 每当执行一条DML语句, 事务就自动进行提交. 如果要手动管理事务, 需要先开启事务, 然后通过指定的命令对事务进行提交或回滚.

• 开启事务: start transaction;
• 提交事务: commit;
• 回滚事务: rollback;

1.3 并发事务带来哪些问题?

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对同一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题。

脏读（Dirty read）

当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

例如：事务1读取某表中的数据A=20，修改A=A-1，尚未提交，事务2也读取A=20，事务2读取的A=20就是脏数据。

丢失修改（Lost to modify）

指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。

例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。

不可重复读（Unrepeatableread）

指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

幻读（Phantom read）

幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复读和幻读区别

1. 不可重复读的重点是修改，比如多次读取一条记录发现其中某些列的值被修改
2. 幻读的重点在于新增或者删除，比如多次读取一条记录发现记录增多或减少了

1.4 事务隔离级别有哪些?MySQL的默认隔离级别是?

SQL 标准定义了四个隔离级别：

READ-UNCOMMITTED ( read-uncommitted 读取未提交 )

 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

READ-COMMITTED ( read-committed 读取已提交 )

允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

REPEATABLE-READ ( repeatable-read 可重复读 ) 

对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

SERIALIZABLE ( serializable 可串行化 )

最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

隔离级别	脏读	不可重复读	幻读
read-uncommitted	√	√	√
read-committed	×	√	√
repeatable-read	×	×	√
serializable	×	×	×

 

 

 

 

 

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。

我们可以通过SELECT @@tx_isolation; 命令来查看

这里需要注意的是：与 SQL 标准不同的地方在于 InnoDB 存储引擎在 REPEATABLE-READ（可重读）事务隔离级别下使用的是Next-Key Lock 锁算法，因此可以避免幻读的产生，这与其他数据库系统(如SQL Server) 是不同的。

所以说InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读） 已经可以完全保证事务的隔离性要求，即达到了 SQL标准的SERIALIZABLE(可串行化) 隔离级别。

因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是 READ-COMMITTED(读取已提交) ，但是InnoDB 存储引擎默认使用 REPEAaTABLEREAD（可重读） 并不会有任何性能损失。
InnoDB 存储引擎在 分布式事务 的情况下一般会用到 SERIALIZABLE(可串行化) 隔离级别。

2 锁

2.1 锁的分类    

内部锁 ：由服务器执行，不涉及其他程序

• 行级锁定  Row-Level Locking

MySQL对InnoDB表使用行级锁来支持多个会话的同时写访问，使它们适合于多用户、高度并发和OLTP应用程序。

为了避免在单个InnoDB表上执行多个并发写操作时出现死锁，在事务开始时通过发出SELECT...FOR UPDATE命令获取必要的锁，为了修改预期中的每一组行，即使这个数据在事务中稍后会更改命令。如果事务修改或锁定多个表，则在每个事务中以相同的顺序发出适用的语句。死锁影响性能而不是表示严重错误，因为InnoDB会自动检测死锁情况并回滚一个受影响的事务。

在高并发性系统中，当多个线程等待同一个锁时，死锁检测可能会导致速度减慢。有时，禁用死锁检测和在发生死锁时依赖innodb_lock_wait_timeout设置进行事务回滚可能更有效。可以使用innodb_Deadlock_detect配置选项禁用死锁检测。

行级锁定的优点：

• 当不同的会话访问不同的行时，锁冲突更少。
• 回滚的更改更少。
• 可以长时间锁定单一的行。

• 表级锁定 Table-Level Locking

• MySQL对MyISAM、MEMORY 和 MERGE 表使用表级锁定，一次只允许一个会话更新这些表。此锁定级别使这些存储引擎更适合于只读、多读或单用户应用程序。

• 这些存储引擎总是在查询开始时一次请求所有需要的锁，并且总是以相同的顺序锁定表，从而避免死锁。减少了并发性；要修改表的其他会话必须等到当前数据更改语句完成。

表级锁定的优点：

• 所需内存相对较少（行锁定要求每行或每组锁定的行具有内存）
• 在表的很大一部分使用时很快，因为只涉及一个锁。
• 如果经常对大部分数据执行分组操作，或者必须经常扫描整个表，则速度很快。

MySQL按如下方式授予表写锁：

• 如果表上没有锁，就在上面加一个写锁。
• 否则，将锁请求放入写锁队列。

MySQL按如下方式授予表读锁：

• 如果表上没有写锁，则对其设置读锁。
• 否则，将锁请求放入读锁队列。

InnoDB锁模式：

InnoDB 实现了以下两种类型的行锁：

• 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
• 排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：

• 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
• 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。

InnoDB加锁方法：

• 意向锁是 InnoDB 自动加的， 不需用户干预。
• 对于 UPDATE、 DELETE 和 INSERT 语句， InnoDB
  会自动给涉及数据集加排他锁（X)；
• 对于普通 SELECT 语句，InnoDB 不会加任何锁；
  事务可以通过以下语句显式给记录集加共享锁或排他锁：
  • 共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。 其他 session 仍然可以查询记录，并也可以对该记录加 share mode 的共享锁。但是如果当前事务需要对该记录进行更新操作，则很有可能造成死锁。
  • 排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。其他 session 可以查询该记录，但是不能对该记录加共享锁或排他锁，而是等待获得锁
• 隐式锁定：

InnoDB在事务执行过程中，使用两阶段锁协议：

随时都可以执行锁定，InnoDB会根据隔离级别在需要的时候自动加锁；

锁只有在执行commit或者rollback的时候才会释放，并且所有的锁都是在同一时刻被释放。

• 显式锁定 ：

• -- 共享锁 
  select ... lock in share mode
  -- 排他锁 
  select ... for update 

  乐观锁、悲观锁

• 乐观锁(Optimistic Lock)：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。 乐观锁不能解决脏读的问题。

• 乐观锁, 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。

• 悲观锁(Pessimistic Lock)：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。

• 悲观锁，顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

如何选择锁的类型

1. 表锁优于行级锁的情况 ：

• 该表的大多数语句都是读操作。
• 表的语句结合读写，其中写操作是对某行的更新或删除，可以用一个键读取。
• SELECT 与并发 INSERT 语句结合使用，很少有 UPDATE 或 DELETE 语句。
• 对整个表执行许多扫描或分组操作，但没有写操作。

2. 表锁优于行级锁的情况：

对于更高级别的锁，可以通过支持不同类型的锁来更轻松地优化应用程序，因为开销小于行锁。

总结：

表级锁：开销小，加锁快，不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发量最低
行级锁：开销大，加锁慢，会出现死锁。锁力度小，发生锁冲突的概率小，并发度最高

3 日志（没太懂，后续更新）

日志类型	写入日志的信息
错误日志	启动，运行或停止mysqld遇到的问题
通用查询日志	建立的客户连接和从客户端接收的语句
二进制日志	更改数据的语句（也用于复制）
中继日志	从复制主服务器收到的数据更改（仅在从属复制服务器上使用，以保存来自主服务器的数据更改）
慢查询日志	long_query_time执行耗时超过几秒钟的查询
DDL日志（元数据日志）	DDL语句执行的元数据操作