第三弹：MySQL事务和锁

事务特点：ACID

原子性、一致性、隔离性、持久性  

一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚。

数据库总是从一个一致性状态转换到另一个一致状态。

一个事务所做的修改在最终提交以前，对其他事务是不可见的。

一旦事务提交，则其所做的修改就会永久保存到数据库中。

未提交读：事务A可以读取到事务B已修改但未提交的数据
读已提交RC：等其它事务把变更提交到db，才能读取到
可重复读RR：同一个事务中，多次读取某一行记录，始终是一样的值，不管在此期间，其它事务有没有修改过该数据(不论是否提交)。该级别解决了RC不可重复读的问题，但是存在幻读问题，商品订单容易形成超卖现象。
串行化：一个事务在修改其它数据时，如果有其它事务也想改，必须等前面的事务提交或回滚后，才能继续。最严格的级别，但是性能最低

幻读和不可重复读的区别
不可重复读的重点是修改：在同一事务中，同样的条件，第一次读的数据和第二次读的数据不一样。（因为中间有其他事务提交了修改）
幻读的重点在于新增或者删除：在同一事务中，同样的条件,第一次和第二次读出来的记录数不一样。（因为中间有其他事务提交了插入/删除）

解决方法：

1、加锁：在读取数据前，对其加锁，阻止其他事务对数据进行修改。
2、多版本并发控制，也称为多版本数据库：不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照并用这个快照来提供一定级别（语句级或事务级）的一致性读取。

事务日志

包括：重做日志redo和回滚日志undo
redo log（重做日志） 实现持久化和原子性
undo log（回滚日志） 实现一致性

数据库日志种类

• 错误日志：记录出错信息，也记录一些警告信息或者正确的信息。
• 查询日志：记录所有对数据库请求的信息，不论这些请求是否得到了正确的执行。
• 慢查询日志：设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中。
• 二进制日志：记录对数据库执行更改的所有操作。
• 中继日志：中继日志也是二进制日志，用来给slave 库恢复
• 事务日志：重做日志redo和回滚日志undo

锁的分类

从对数据操作的类型分类：

• 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行，不会互相影响
• 写锁（排他锁）：当前写操作没有完成前，它会阻断其他写锁和读锁

从对数据操作的粒度分类：

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低（MyISAM 和 MEMORY 存储引擎采用的是表级锁）；

• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高（InnoDB 存储引擎既支持行级锁也支持表级锁，但默认情况下是采用行级锁）；

• 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

• MyISAM 表锁

• InnoDB 行锁 索引失效会导致行锁变表锁

加锁机制
乐观锁与悲观锁是两种并发控制的思想，可用于解决丢失更新问题

deadlock.png

死锁

死锁产生：
死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环.
当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁.
锁的行为和顺序和存储引擎相关。以同样的顺序执行语句，有些存储引擎会产生死锁有些不会——死锁有双重原因：真正的数据冲突；存储引擎的实现方式。
检测死锁：数据库系统实现了各种死锁检测和死锁超时的机制。InnoDB存储引擎能检测到死锁的循环依赖并立即返回一个错误。

死锁恢复：死锁发生以后，只有部分或完全回滚其中一个事务，才能打破死锁，InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。所以事务型应用程序在设计时必须考虑如何处理死锁，多数情况下只需要重新执行因死锁回滚的事务即可。

外部锁的死锁检测：发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁， 这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决

死锁影响性能：死锁会影响性能而不是会产生严重错误，因为InnoDB会自动检测死锁状况并回滚其中一个受影响的事务。在高并发系统上，当许多线程等待同一个锁时，死锁检测可能导致速度变慢。有时当发生死锁时，禁用死锁检测（使用innodb_deadlock_detect配置选项）可能会更有效，这时可以依赖innodb_lock_wait_timeout设置进行事务回滚。

MyISAM避免死锁：

在自动加锁的情况下，MyISAM 总是一次获得 SQL 语句所需要的全部锁，所以 MyISAM 表不会出现死锁。

InnoDB避免死锁：

为了在单个InnoDB表上执行多个并发写入操作时避免死锁，可以在事务开始时通过为预期要修改的每个元祖（行）使用SELECT ... FOR UPDATE语句来获取必要的锁，即使这些行的更改语句是在之后才执行的。
在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁、更新时再申请排他锁，因为这时候当用户再申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。
如果事务需要修改或锁定多个表，则应在每个事务中以相同的顺序使用加锁语句。在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。
通过SELECT ... LOCK IN SHARE MODE获取行的读锁后，如果当前事务再需要对该记录进行更新操作，则很有可能造成死锁。

改变事务隔离级别 如果出现死锁，可以用 show engine innodb status; 命令来确定最后一个死锁产生的原因。
返回结果中包括死锁相关事务的详细信息，如引发死锁的 SQL 语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。

image