mysql数据库加死锁方法_MySQL的知识点总结（并发，锁，事务）

说到数据库，就会聊到许多方面的东西。最近自己也是准备再一次系统的学习MySQL数据库。

用博客记录自己的学习历程，希望在两个月的闭关后能达到自己目标。

目录

1.引入

1.一条sql语句的执行流程

2.事务与并发问题

1.什么是事务

2.RollBack的实现

3.事务的四大特性(ACID)

4.事务并发的三个问题(脏读，不可重复读，幻读)

5.事务的隔离级别

3.MySQL锁和MVCC

1.通过锁解决并发问题

2.锁的粒度与并发效率

3.意向锁(Intention Locks)

4.索引行锁(Record Locks )

5.间隙锁(Gap locks)

6.多版本并发控制(MVCC)

3.死锁

1.死锁的产生

2.MySQL解决死锁的方式

3.有效避免数据库死锁的方法

1.引入

1.一条sql语句的执行流程

数据库中一切的操作都源于SQL语句的执行。在命令行输入一条SQL语句，MySQL最上层的服务器会建立合适的连接，进行一些线程处理。接着解析器会解析这条语句，创建解析树。如果这条SQL语句是查询语句，那就先查看缓存，如果有匹配的结果就直接返回。如果没有或者SQL语句是更新语句，那么优化器和存储引擎就开始进行sql语句优化。优化是非常大的一块内容，之后会单独写博客记录。优化完毕后根据优化后的结果进行操作，返回结果。

这篇博客，就围绕着SQL语句执行时遇到的各类知识点展开。

2.事务与并发问题

MySQL默认开启自动提交，每执行一条sql语句就自动提交事务。在MySQL中使用事务首先要关闭自动提交，使用手动提交的方式原子执行多个sql语句。当多个事务执行时，并发问题也就出现了。

1.什么是事务

事物是MySQL最小的执行单元，一个事务的执行就是多条SQL语句的执行，事务中所有SQL语句执行完毕后执行commit操作，提交该事务中所有的结果数据到数据库中，如果发生异常，则事务通过RollBack操作回滚到最初的状态。

2.RollBack的实现

在事务执行过程中如果发生异常会回滚该事务。RollBack是通过事务日志来实现的。在事务执行中，每个SQL语句的执行都有详细的记录，记录在事务日志中，如果发生异常，就按照事务日志中的信息来回滚数据。

3.事务的四大特性(ACID)

原子性(atomicity)

一个事务的执行具有原子性，事务中包含的sql或全部执行成功，或全部失败。

一致性(consistency)

数据库在执行事务时，总时从一个一致性状态转到另一个一致性状态。例如银行系统中总金额为100元，A账户有50元，B账户有50元。这是一个一致性状态。执行一个事务，A向B转账20元。事务执行完毕后，银行总金额还是100元，虽然每个账户的金额不同，但是一致性的状态是不会变的。如果在事务执行后银行的总金额变为120元或者90元了，这就破坏了一致性。

隔离性(isolation)

多个线程执行事务是相互隔离的，一个事务对于其他同时执行的事务来说通常是不可见的(这取决于隔离级别)。

持久性(durability)

一旦事务提交，那么事务对数据库的改变是持久性的，绝对不会再改变(但也不绝对，否则还要备份干嘛呢)。

4.事务并发的三个问题(脏读，不可重复读，幻读)

脏读

一个正在执行的事务可能会读取到另外一个事务操作过程的产生的脏数据。

举例：账户A有100元钱。事务TA要获取账户A的余额。与事务TA同时执行的事务TB的操作是账户A转账给账户B20元，然后账户C转账给账户A20元钱。首先事务TB执行，A转账给B20元。然后事务TB线程CPU时间片耗尽，事务TA执行。TA获取账户A的余额，此时账户A余额为80，于是事务TA返回结果为80。然后TB继续执行，账户C又转给账户A20元，这时事务TB执行完毕，账户A的余额有100元。TA拿到的结果是事务TB操作过程中脏数据，是错误的。

不可重复读

一个正在执行的事务两次相同的查询得到不同的结果

举例：账户A有100元钱。事务TA有两次相同的查询操作，都是查询账户A的余额。事务TB则是账户B向账户A转账20元。事务TA第一次查询得到的结果是账户A有100元，之后TB执行，账户A余额变为120。于是TA第二次查询操作得到的结果就是账户A有120元，前后矛盾。

幻读

一个正在执行的事务两次读取范围内记录数得到不同的结果。

举例：银行系统共有账户100个。事务TA进行两次相同的查询操作，查询总的账户数量。事务TB的操作是新增一个账户。TA第一次查询得到的结果是100，然后TB执行，增加一个账户，TA第二次查询得到的结果是101。两次相同的数量查询得到了不同的结果，凭空多出一条记录，仿佛产生了幻觉，这就是幻读。

5.事务的隔离级别

事务的隔离性其实就是数据库典型的并发特性。

事务的隔离级别表示在多个事务并发执行他们相互的可见性以及相互影响的能力。

隔离级别分为4个等级，对应的解决了3个并发问题。

读未提交(READ UNCOMMITTED)

该隔离级别的等级最低，并发执行的事务几乎没有隔离，会发生脏读，不可重复读，幻读的问题。效率最高。

读已提交(READ COMMITTED)

每个事务只能读取到其他事务提交的数据，解决脏读问题，会发生不可重复读，幻读问题。效率很高。是Oracle的默认级别。

可重复读(REPETABLE READ)

该级别保证一个事务同一个读取操作得到的结果是统一的，解决不可重复读问题，但是还是会发生幻读问题。效率不错，是MySQL的默认级别。

串行化(SERIALIZABLE)

通过锁表的方法来变并行为串行，可以解决一切并发问题(已经没有并发了，自然也就没有并发问题了)。效率最低。

3.MySQL锁和MVCC

1.通过锁解决并发问题

MySQL中会使用锁来解决并发问题。锁大体分为排他锁和共享锁，具体来说就是写锁和读锁。使用方法就是当一个SQL语句进行写操作时加一个排他锁，防止其他线程操作数据库，保证写操作的安全。当执行一条SQL语句进行读操作时就加一个共享锁，被加上了共享锁的数据可以被其他线程线程进行读操作(有些存储引擎也可以进行写操作)，顾名思义就是共享这块数据，一起进行读操作。

使用读写锁，解决了读写同时进行产生的读脏等并发问题，而且共享锁还增大了多线程读操作的并发量。

2.锁的粒度与并发效率

通常来说，当一个数据被上了锁，那么其他线程只能等待数据的锁释放才能操作该数据。等待的时间越长，并发效率越差。

为了减小等待的时间，我们可以缩小锁的粒度。

例如老版本MySQL中使用的MyISAM引擎只支持表锁，不管线程操作那一个行的数据，都给这张表加锁，可能其他线程只是操作对该行无任何影响也不能同时进行，只能等待获取锁。而新版本中的InnoDB引擎减小了锁的粒度，支持行锁，使用哪些行的数据就给对应行上锁，只要其他线程的操作与上锁行无关即可同时操作，增大了并发效率。

3.意向锁(Intention Locks)

InnoDB实现锁表是通过意向锁(Intention Locks)实现的，通俗来讲就是加锁时先提前声明一个意向，并获取表级别的意向锁，如果获取成功，则稍后将要或正在(才被允许)，对该表的某些行加锁了。

4.索引行锁(Record Locks )

使用行所比起使用表锁能获得更大的性能提升，InnoDB使用索引行锁(Record Locks )来实现行所，它是一种基于索引的行锁。

例子：执行一条SQL语句 SELECT sid FROM table_student WHERE sid = 123;

如果sid存在索引(InnoDB会自动根据主键创建一个聚簇索引)，则根据索引锁定相应的行，防止在检索过程中其他线程对该行进行修改。如果该字段没有索引，则会锁定整个表。

5.间隙锁(Gap locks)

MySQL默认情况下使用是REPETABLE READ作为默认事物隔离级别的，该隔离级别会发生幻读问题。

因此InnoDB为了防止幻读问题使用了间隙锁(Gap locks)。间隙锁通俗来讲就是查询一个范围内的数据时给范围内字段的间隙加锁，防止幻影行的插入。

间隙锁也是索引的，而且可以跨越多个索引进行加锁。

间隙锁配合行锁就相当于一个小型的表锁，上文也说过加锁数据面积越大并发效率越低，因此间隙锁是一种效率和安全的折中方案。

6.多版本并发控制(MVCC)

多版本并发控制可以说是行锁的变体实现。

在InnoDB引擎中具体来说就是给表加两个列，分别记录该行数据的创建(更新)版本号和删除版本号。每进行一次事务系统的版本号都加1。

查询操作：操作的数据行更新版本号必须早于当前版本号，说明该事务在执行时这条数据是已经被创建的。而且删除版本号必须大于当前版本号或未定义，说明该事务在执行时该条数据还并未被删除。

插入操作：把当前版本号作为新插入的数据的创建版本号。

删除操作：把当前版本号作为删除数据的删除版本号。

更新操作：新增一条数据，内容是修改后的最新值，把当前版本号作为新数据的创建版本号，并把原数据的删除版本号修改为当前版本号。

更新操作并不是在元数据上修改，而是新创建了包含最新数据的字段，并把早期数据作为快照。这种快照+版本号的设计减少了很多加锁操作，提升了并发能力。

3.死锁

1.死锁的产生

发生死锁要满足四个条件

互斥条件：共享资源在同一时刻只能由一个操作单元持有，其他操作单元只能等该操作单元释放资源才可以获取。

不可剥夺条件：在一个操作单元持有共享资源时，其他操作单元不能剥夺，只能等待该操作单元自行释放。

请求与保持条件：一个操作单元在不释放当前持有的共享资源的情况下去请求其他的互斥共享资源。

循环等待条件：多个操作单元形成 持有互斥共享资源并请求其他互斥共享资源 的环状结构

例子：有三个互斥资源RA，RB，RC。有三个操作线程T1，T2，T3。T1获取了RA，T2获取了RB，T3获取了RC，并且T1还想获得RB，T2还想获得RC，T3还想获得RA，三个线程陷入了长时间等待之中，发生了死锁。

2.MySQL解决死锁的方式

MySQL实现了各种检测死锁的机制，每个存储引擎都有自己的解决死锁的算法，越高级的存储引擎检测死锁的能力越强。

InnoDB引擎在检测到死锁时会抛出一个错误，并回滚持有排他锁数量最少的那个事物，破坏死锁环。之后重新执行该事务。

3.有效避免数据库死锁的方法

避免死锁的根本方法就是破坏产生死锁的四个条件。

一次性获取资源

让线程一次性拿到执行过程中所需的所有共享资源，避免再次获取资源发生死锁。

使用更低的隔离级别

使用READ COMMITED隔离级别，减少加锁，减少互斥资源。

拆分事物

把需要获取多个互斥资源的事物拆分为多个小事务，避免多次获取互斥资源。

使用更强大的存储引擎并添加合适的索引

使用InnoDB引擎，添加合适的索引。化表锁为行锁，减小锁的粒度。