mysql事务和锁

1、ACID

A:atomicity,原子性，一个事务中的操作要么全执行，要么全不执行。

C:consistency,一致性，事务开始之前和事务结束之后，数据库的完整性不被破坏（从一个正确的状态到另一个正确的状态），即写入的资料符合预设的规则。

I：isolation,隔离性，允许多个事务的同时的并行，且保证数据的一致性。

D:durability,持久性，事务运行之后对数据的修改是永久的。

2、  事务隔离级别及多个事务同时执行时存在的问题

脏读：一个事务读取到另一个事务没有提交的数据

不可重复读（对其他事务的update操作的可见）：对一条数据，在一个事务中，多次读取获得的值不一样

幻读（对其他事务Insert操作的可见）：在一个事务中，两次读取数据，在第二次读取时读取到了第一次不存在的行（即读取到了其他事务新插入的行）

	脏读	不可重复读	幻读
读未提交(ru)	有	有	有
读提交（rc）	无	有	有
可重复读（rr）	无	无	有
串行化	无	无	无

读未提交(ru):修改了的数据都可见（不管事务是否提交）

读提交（rc）：查询语句执行前其他事务提交的数据可见，修改但事务未提交的数据不可见

可重复读（rr）:事务启动前其他事务提交的数据可见，修改但事务未提交的数据不可见

3、事务隔离的实现简诉

通过MVCC（多版本并发控制）实现事务的

（1）前提：当执行了一条更新语句（而不需要等到事务提交），就直接修改了索引组织表中该条数据的值

（2）读未提交，直接读取索引组织表中的数据，因此会读到未提交的脏数据（未提交的事务修改得到的数据）

（2）在读提交和可重复读隔离级别下存在一个一致性视图的概念，返回的数据是将索引组织表中该条数据的值通过利用undolog 日志、高水位、低水位等计算得到的数据

（3）在读提交隔离级别下，一致性视图是在每个sql运行之前创建的，因此他能读取到其他事务已提交的数据，但读取不到其他事务未提交的数据，也就解决了脏读的问题，但是并没有解决不可重复读的问题（一个事务中的多个查询语句对应的是多个视图，一条数据可能会不一致）。

（4）在可重复读隔离级别下，视图实在事务启动前创建的，因此他能保证在整个过程中一条数据（从视图中读取）是不会受到其他事务的影响的因此解决的不可重复读的问题

4、  锁的分类

（1）全局锁：flush table with read lock;全局锁主要同于数据的备份，可通过unlock tables来释放锁。

（2）表级锁:分为两种：表锁和元数据锁（meta data lock）;表锁：lock tables [表名] read/write，通过unlock tables释放表锁。Meta data lock 为元数据锁，不是用户显示使用的锁，该锁的主要作用是用于维护数据库表结构的一致性，如在增删查改时会给表加上元数据的读锁，多个增删改查操作之间不会阻塞（对于表层面而言，而不是具体行的数据），表结构的修改语句（增加删除字段或索引）会在表上增加元数据的写锁，在一个线程运行修改表结构时会阻塞其他线程的表结构修改和增删查改语句。

实例:修改字段可能会导致数据库系统的崩溃（已测试）

Session间通过排队获取锁，SessionC的阻塞会造成后续排队Session的阻塞

SessionA 启动事务查询表T时会在表T上加一个MDL读锁,且只有当事务提交之后才会把锁释放；

SessionB能也是加的读锁能够正常的运行；

SessionC 在给表T加MDL写锁时会阻塞等待写锁的释放；

SessionD 在运行前会在阻塞队列中排在SessionC之后等待；

因此当存在大量的T表的查询操作时，且存在自动重试设置时，当查询 语句超时（SessionD及以后的查询）会一直存在大量的重试消耗数据库 的资源。

（3）行级锁

5、    行锁（二阶段锁协议，死锁和死锁的检测）

（1）实现的层面，全局锁和表级锁是在server层实现的，而行级锁是在引擎层实现了，MyISAM未实现行锁而InnoDB实现了这是其被取代的主要原因

（2）二阶段锁协议：在InnoDB事务中,行锁是在需要的时候加上，但不是不需要了就立即释放，而是在事务提交之后才释放。这样的优点是在对同一条数据进行多次操作的时候不需要多次的上锁操作。

二阶段锁协议的实践意义是在一个事务中如果存在多个上锁的操作，我们要尽量的将可能造成锁冲突的操作向后放。

（3）死锁：不同的线程之间存在资源的循环依赖关系

例如:存在两个线程1,2;线程以持有锁A且要等待锁B才能运行完成提交事务，而线程2持有锁B，要等待锁A才能运行完成提交事务，此时线程1和2就是处于死锁的状态

（4）死锁的两种解决方案：一种直接等待超时，可通过show variables like ‘innodb_lock_wait_timeout’来查询锁等待超时时间，修改等待超时指令为SET innodb_lock_wait_timeout=30;不能通过减小锁等待超时时间来解决死锁的问题，这可能会影响到正常的锁等待。

另外一种则是通过开启死锁自动检测，在一个事务被锁的时候检测是否因为自己的加入导致了死锁，如果存在就将死锁链条中的一个事务回滚。

热点行的更新问题：假设存在1000个线程同时更新同一行数据时，死锁的检测时（1000*1000）100万量级的，即使没有检测到死锁，但检测过程中也消耗了大量的资源。解决的方案是对相同行的更新在进入引擎层之前进行排队，如设置中间件让数据库一次对同一行数据的更新最多只有10行

6、MVCC的实现：

主要分为几个部分：当前版本数据,undo log日志（计算历史版本数据）,

                                 一致性视图（用于判断一个事务对当前事务或操作是否可见）

（1）当前版本数据指的就是当前索引组织表中的数据，每次修改操作执行（不需要等到事务提交后）就会生成新的当前版本的数据和undo log。

（2）事务和行数据及他们之间的关系：每个事务存在一个事务ID（transaction id）,事务id是严格按顺序递增的，每一行的数据是存在多个版本的（在一个时间段内可能存在多个版本，在某个时刻在物理结构上只有当前的版本，但可以通过undo log日志计算出之前版本的数据），每个版本都存在一个row trx_id，即将上个版本的数据修改为此版本的事务的id。

（2）Undo log日志：在物理上每行数据都只存在当前的版本，但是可以通过undo log日志计算出之前的版本；undo log日志是在事务进行数据的更新操作时生成的

（3）事务中数据的可见性分析：当启动一个事务时，数据库中所有的事务的情况如下：图中从左向右事务id递增。 

低水位：活跃事务中最小的事务ID

高水位：当前启动的事务中的最大事务ID+1，高水位对应的事务可能是已提交的事务也可能是活跃的事务。

事务主要包含以下的状态：

已提交的部分：图中的绿色部分，小于低水位的全部，和低水位高水位之间的部分。

活跃的事务：在当前事务启动的瞬间，此时处于已启动但还未提交的事务，在高水位与低水位之间的红色部分

还未开始的事务：大于高水位的黄色部分

（4）事务的一致性视图的结构及其使用：

事务的一致性视图是由：高水位和一致性视图创建时的活跃事务ID有序数组（数组的第一个值就为低水位）组成的

判断一行数据对该事务是否可见的原理：获取改行数据的row trx_id，若这个值小于低水位，数据可见；若值大于高水位，该值不可见；若处于低水位与高水位之间，判断该值是否处于活跃事务Id有序数组中，若是则数据不可见，若不是则数据可见；若等于当前事务id则该行数据可见。对于不可见的数据，通过undo log日志计算出上个版本的数据，重复以上操作，直至获取可见的数据。

7、快照读和当前读

（1）快照读：在可重复读隔离和读提交隔离级别下普通的查询语句为可重复读，是通过一致性视图来获取数据的，只不过在可重复读隔离级别中，一致性视图在事务创建的时候就会生产；而读提交隔离级别中，每次查询时都会重新的创建一致性视图

（2）当前读：读到的是当前版本的数据。

通过select  *  from  T  where  id=1  for update;(写锁，X锁，排他锁)

Select  *  from  T  where  id=1  lock in share mode;(读锁，S锁，共享锁)

可以进行当前读。

8、sql语句运行慢的分析

(1)Server层：通过show processlist判断是不是上了全局锁或者是表锁

当运行一个线程flush TABLE with read LOCK,另一个线程做删数据操作时；

运行show processlist,结果如下

当一个线程运行lock tables t write;另一个线程做删数据操作时；

运行show processlist;

(2)innodb引擎层，通过select * from sys.innodb_wait_locks查询行锁的阻塞情况，其中属性lock_table表示锁着的表，waiting_pid表示在等待的线程，假设线程为4，可通过kill query 4 杀死阻塞的线程。

(3)通过explain查询语句的执行情况，通过select_type查看查询的类型，key查看所走的索引，

如果不存在索引则进行添加，如果存在则优化sql语句解决索引失效的问题。

9、幻读

（1）定义：在一个事务中，两次相同的查询，第二次查询读取到了第一次没有读取到的数据。特别说明的是这个查询是当前读，而快照读（一致性读）两次读取的数据一定是一致的，不会出现幻读。

（2）幻读造成的问题：一方面是语义被破坏，例如：有个表T（id,c,d),其中id为主键，只包含一条数据（1，1，1）,对于语句select * from t where c=1 for update，这个语句想表达的是锁住所有C=1的行，但是他只能锁住此时C对应的Id为1的行，而无法锁住新插入的C=1的行，如插入（2,1,1），后续修改这条数据不会被阻塞。另一方面，可能造成binlog日志与数据不一致。

10、next-key lock 和间隙锁（gap lock）

（1）引入间隙锁是为了解决可重复读隔离级别下幻读的问题

（2）间隙锁是在索引（可以是主键索引也可以是普通索引）上两条相邻的数据之间的间隙上上的锁。

（3）Next-key lock是逻辑上的锁是一个前开后闭的区间，实际上Next-key lock在上锁过程中是分为两个部分即间隙锁上锁和行锁上锁。

（4）间隙锁和间隙锁之间不存在冲突，而是间隙锁和插入操作存在冲突

（5）间隙锁（next-key lock）造成的问题是：可能锁住更大的空间，降低查询效率，同时更容易造成死锁。例如：以下的逻辑，如果表T（id,c,d）中不存在id=9，就插入数据（9，9，9）；执行过程如下：

假设当前数据库中的数据为（3，3，3），（7，7，7），（11，11，11），（13，13，13），在运行Session A运行 select * from t where id=9 for update时，会加上间隙锁（7，11），Session B也会加上间隙锁（7，11），然后两个插入操作被阻塞造成死锁。

11、加锁的规则

（1）加锁受到几大因素的影响:走的索引是唯一性索引还是非唯一性索引或是走的全表扫描；查询的类型是等值查询或是范围查询；

排列组合的情况：

    （a）不走索引即全表扫描：在整个表加上行锁和间隙锁

      (b)唯一性索引and等值查询：命中条件时结束退化为行锁，

       未命中条件在遇到第一个比这个值大的数时结束并退化成间隙锁 ，即next-key lock为（）形式

      (c)唯一性索引and范围查询：命中条件时不会结束继续查询到不符合条件的行

                                                       即next-key lock为（）形式

      (d)非唯一性索引and等值查询：命中条件时不会结束继续查询到不符合条件的行

                                                        即next-key lock为（）形式

      (e)非唯一性索引and范围查询：命中条件时不会结束继续查询到不符合条件的行

                                                        即next-key lock为（）形式

（2）逻辑上都是加的前开后闭的next-key lock,但可能在一定的条件下退化成行锁或间隙锁；

（3）退化成行锁的条件是：等值查询中走唯一性的索引且数据命中

（4）退化成间隙锁的条件：最后的数据不符合查询条件。例如：select * from t where id=9;其中id为唯一索引，且表中只包含数据（7，7，7）和（11，11，11），此时的next-key lock (7,11]会由于11不符合查询的条件退化成（7，11）;(特别说明：在较低版本中这种退化只发生在等值查询中，而范围查询中不会退化)

（5）查找过程中访问到的对象才会加锁，共享锁（lock in share mode）可以只在非主键索引上上锁，排他锁（for update）一定会在主键索引上上锁；也就是说如果通过lock in share mode 并发生索引覆盖可能只在非主键索引上上锁，而不在主键索引上上锁；

12、 binlog_format

（1）show binary logs;  #获取binlog文件列表

show master status; #查看当前正在写入的binlog文件

show BINLOG EVENTS in 'binlog.000001';#查看一个日志文件的内容

（2）格式的比较

13、使用读提交隔离级别加 binlog_format=row 的组合的原因

（1）读提交隔离级别下间隙锁不会生效，不使用间隙锁能够防止锁住过多空间造成效率低下

（2）binlog_format=row能够解决幻读造成的binlog日志和数据不一致的问题