mysql数据库管理-锁管理以及体系结构

本文对 MySQL 数据库中有关锁、事务及并发控制的知识及其原理做了系统化的介绍和总结，希望帮助读者能更加深刻地理解 MySQL 中的锁和事务，从而在业务系统开发过程中可以更好地优化与数据库的交互。

1、MySQL 服务器逻辑架构

 

每个连接都会在 MySQL 服务端产生一个线程（内部通过线程池管理线程），比如一个 select 语句进入，MySQL 首先会在查询缓存中查找是否缓存了这个 select 的结果集，如果没有则继续执行解析、优化、执行的过程；否则会之间从缓存中获取结果集

Connectors：用来与客户端应用程序建立连接的数据库接口。

Management Services & Utilities：系统管理和服务控制相关的辅助工具。

Connection Pool：负责处理与用户访问有关的各种用户登录、线程处理、内存和进程缓存需求。

Sql Interface：提供从用户接受命令并把结果返回给用户的机制。

Parser：对SQL语句进行语法分析和解析，构造一个月来执行查询的数据结构。

Optimizer：优化查询语句，以保证数据检索动作的效率达到或者非常接近最最优。使用一种“选取-投影-联结”策略来处理查询，即先根据有关的限制条件进行选取(Select 操作)以减少将要处理的元组个数，再进行投影以减少被选取元组力的属性字段的个数，最后根据连接条件生产最终的查询结果。

Caches & Buffers：保证使用频率最高的数据或结构能够以最有效率的方式被访问，缓存的类型有：表缓存、记录缓存、键缓存、权限缓存、主机名缓存等。

查询流程介绍

检查查询缓存是否打开，检查是否命中缓存中的数据（通过对大小写敏感的HASH查找实现的），若不命中则进行下一阶段的处理。若命中查询缓存，检查用户权限，若权限没问题，则直接把缓存数据返回给客户端。

词法/语法解析器：将会进行语法规则的验证和解析查询（对语法解析），生成语法分析树。

 

预处理器：根据MySQL规则进一步检查语法分析树是否合法。例如检查表或列是否存在，解析名字和别名有没有歧义。下一步预处理器会验证权限。

优化器的作用就是找到最好的执行计划。MySQL使用CBO优化器。MySQL使用很多优化策略生成最优的执行计划，可以分为两类：静态优化（编译时优化）、动态优化（运行时优化）。

MySQL只是简单的根据执行计划给出的指令逐步执行。调用存储引擎实现的接口来完成执行计划。优化器根据接口可以获取表的相关信息，包括表的所有列名、索引统计信息等。将结果返回给客户端，或者返回这个查询的一些信息，如查询影响到的行数。如果查询可以被缓存，那么MySQL会将结果存放到查询缓存中。

 

2、MySQL 锁

它们都是标准的行级锁。

• 共享锁（S） 共享锁也称为读锁，读锁允许多个连接可以同一时刻并发的读取同一资源,互不干扰；

• 排他锁（X） 排他锁也称为写锁，一个写锁会阻塞其他的写锁或读锁，保证同一时刻只有一个连接可以写入数据，同时防止其他用户对这个数据的读写。

show variables like "autocommit";

set autocommit=0; //0表示AutoCommit关闭
set autocommit=1; //1表示AutoCommit开启

 

6、MySQL 死锁问题

 

死锁，就是产生了循环等待链条，我等待你的资源，你却等待我的资源，我们都相互等待，谁也不释放自己占有的资源，导致无线等待下去。 比如：

//Session A
START TRANSACTION;
UPDATE account SET p_money=p_money-100 WHERE p_name="tim";
UPDATE account SET p_money=p_money+100 WHERE p_name="bill";
COMMIT;
//Thread B
START TRANSACTION;
UPDATE account SET p_money=p_money+100 WHERE p_name="bill";
UPDATE account SET p_money=p_money-100 WHERE p_name="tim";
COMMIT;

当线程A执行到第一条语句UPDATE account SET p_money=p_money-100 WHERE p_name="tim";锁定了p_name="tim" 的行数据；并且试图获取 p_name="bill" 的数据；

此时，恰好，线程B也执行到第一条语句：UPDATE account SET p_money=p_money+100 WHERE p_name="bill";锁定了 p_name="bill" 的数据，同时试图获取 p_name="tim" 的数据；

此时，两个线程就进入了死锁，谁也无法获取自己想要获取的资源，进入无线等待中，直到超时！

innodb_lock_wait_timeout  等待锁超时回滚事务：

直观方法是在两个事务相互等待时，当一个等待时间超过设置的某一阀值时，对其中一个事务进行回滚，另一个事务就能继续执行。

 

这种方法简单有效，在i nnodb 中，参数 innodb_lock_wait_timeout 用来设置超时时间。

 

wait-for graph 算法来主动进行死锁检测：innodb 还提供了 wait-for graph 算法来主动进行死锁检测，每当加锁请求无法立即满足需要并进入等待时，wait-for graph 算法都会被触发。

 

6.1、如何尽可能避免死锁

• 以固定的顺序访问表和行。比如两个更新数据的事务，事务A 更新数据的顺序 为1，2；事务B更新数据的顺序为2，1。这样更可能会造成死锁；

• 大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小；

• 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率；

•  降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。（我司 MySQL 规范做法）；

• 为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大。