Mysql数据库锁与事务

Mysql数据库锁与事务

 

锁的类型：

对数据的操作只有两种，读和写。数据库层面在实现锁时，也会对这两种操作使用不同的锁。

InnoDB实现了标准的行级锁，即共享锁（Shared Lock）和互斥锁(Exclusive Lock)。

 

共享锁：可以理解为读锁，允许事务读数据。

排他锁：可以理解为写锁，允许事务删除或更新一行数据。

从名字上看，共享锁是可以共同拥有的，而排他锁，顾名思义只能一个拥有锁，排斥其他者争夺锁。

 

锁的粒度：

锁根据粒度主要分为表锁、页锁和行锁。不同的存储引擎拥有锁的粒度不一样，最常见的InnoDB就支持表锁和行锁，InnoDB默认使用行级锁。

 

表级锁：是MySQL各存储引擎中最大粒度的锁定机制，该锁机制实现逻辑简单，获取和释放锁的速度很快。表级锁一次性将整个表锁定，能够很好的避免了死锁的问题。但有个不好的地方是锁的粒度大，出现争夺锁定资源的概率也高，会使并发度大打折扣。

 

表锁的语法：

# 获取表锁 LOCK TABLES     tbl_name [[AS] alias] lock_type     [, tbl_name [[AS] alias] lock_type] ...   lock_type:     READ [LOCAL] | [LOW_PRIORITY] WRITE # 释放表锁 UNLOCK TABLES

 

行锁：最小的锁定粒度，在数据行级加锁。由于锁定粒度小，发生锁定资源争用的概率小，所以能够给与应用程序尽可能大的并发处理能力，提高并发性能。但行级锁因此带来了弊端，锁的粒度小，获取锁和释放锁的操作更加频繁，带来的消耗就更大，此外，行级锁也最容易发生死锁。

 

意向锁（Intention Lock）：是InnoDB为支持多粒度锁而引入的。

意向锁也分两种：

意向共享锁：事务想要获得表中某些记录的共享锁，需要在表上先加意向共享锁。

意向互斥锁：事务想要获得表中某些记录的互斥锁，需要在表上先加意向互斥锁。

为什么要引入意向锁？可以思考下，当已有事务使用行级锁对表中某一行进行修改时，如果另一个事务要对全表进行修改，那么就需要对所有的行是否被锁定进行扫描，这样效率非常低。如果引入意向锁，当有人使用行锁对表中某一行进行修改时，会先为表添加意向互斥锁（IX），再为行记录添加互斥锁（X）,在这时如果有人尝试对全表进行修改就不需要判断表中每一行数据是否被加锁了，只需要等待意向互斥锁被释放就可以了。

 

这时锁类型的兼容性如下：

可以看出，意向锁是为了表示是否有人请求锁定表中某一行数据，它并不会阻塞全表扫描之外的任何请求。

 

InnoDB行锁的算法：

Record Lock：单个行记录上的锁。

Gap Lock: 间隙锁，锁定一个范围，但不包含记录本身

Next-Key Lock: 就是Gap Lock+Record Lock, 锁定一个范围，并锁定记录本身。InnoDB对于行的查询都是采用这种锁定算法，不过如果查询列是唯一索引（包含主键索引）的情况下，Next-key Lock会降级为Record Lock。

（为什么要采用Next-Key Lock呢？是为了解决幻读问题，阻止多个事务将记录插入到同一个范围内）

（InnoDB行锁是通过索引上的索引项来实现的，这一点ＭySQL与Oracle不同，后者是通过在数据中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味者：只有通过索引条件检索数据，InnoDB才会使用行级锁，否则，InnoDB将使用表锁！）

 

举例说明下：

CREATE TABLE z (            a INT,            b INT,            PRIMARY KEY(a),    // a是主键索引            KEY(b)    // b是普通索引        );        INSERT INTO z select 1, 1;        INSERT INTO z select 3, 1;        INSERT INTO z select 5, 3; .      INSERT INTO z select 7, 6;        INSERT INTO z select 10, 8;

这时候在会话A中执行 SELECT * FROM z WHERE b = 3 FOR UPDATE ，索引锁定如下：

这时候会话B执行的语句落在锁定范围内的都会进行waiting。

关闭Gap Lock:

将事务的隔离级别设置为 READ COMMITED

将参数Innodb_locks_unsafe_for_binlog设置为1。

 

乐观锁与悲观锁是另一种对锁的分类：

乐观锁：

从名字上看，就是想法很乐观，默认不会产生冲突。对数据库进行操作时，不进行任何其他的处理（包括加锁），只有在更新操作时，再判断是否有冲突，所以不需要依赖数据库底层的锁机制。典型的实现就是MVCC(多版本控制)。

具体实现：在数据库表中添加字段version，每次更新前先查询出version值，然后在更新时判断之前查询出来的version值是否等于当前表中的version值，若相等，则表示在此期间没有其他程序对该条数据进行更改，则可以执行更新操作，同时将version值加1。若不相等，则说明在此期间有程序对这条数据进行操作，则不进行更新操作。

 

悲观锁：

想法很悲观，认为每次操作时，都会出现数据冲突，所以每次操作数据时，都需要加锁来保证数据的一致性。悲观锁往往利用到数据库的锁机制（可想而知，只有数据库层提供的锁机制才能真正保证数据访问的排他性，如果是在系统层面加锁的话，则会导致集群环境中多服务器操作导致数据的不一致）。我们所说的行锁、表锁等都是悲观锁。

死锁：

死锁是指两个或两个以上的事务在执行过程中，因争夺资源而造成的一种互相等待的现象。

死锁报错现象：

Deadlock found when trying to get lock

InnoDB会主动探知到死锁，并回滚某一苦苦等待的事务。那么这里就有两个问题：

InnoDB是怎么探知死锁？以及根据怎么的方案选择事务回滚？

 

探知死锁：

我们将每个事务看为一个节点，当节点1需要等待节点2的资源时，就生成一条有向边指向节点2，最后形成一个有向图。我们只要检测这个有向图是否出现环路即可，出现环路就是死锁！这就是wait-for graph算法。

 

回滚方案：

直观方法是在两个事务相互等待时，当一个等待时间超过设置的某一阀值时，对其中一个事务进行回滚，另一个事务就能继续执行。这种方法简单有效，在innodb中，参数innodb_lock_wait_timeout用来设置超时时间。选择回滚事务的依据：看哪个事务的权重最小，事务权重的计算方法：事务加的锁最少；事务写的日志最少；事务开启的时间最晚。

 

事务：

事务的ACID原则（原子性（atomicity）、一致性（consistency）、隔离性（isolation）、持久性（durability）

原子性：标识事务是否完全执行。一个事务完全执行，如果出错，事务不能完成它的全部任务，则返回到事务的开始前或保存点的状态。

一致性：事务执行前后系统处于一致状态，事务出错回滚后，系统也是处在一致状态。

隔离性：如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。有时也称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。

持久性：一旦事务执行成功，在系统中产生的所有变化将是永久的。

而我们常说的隔离性有对应的隔离级别，Mysql规定的隔离级别有4种：

READ UNCOMMITTED（未提交读）：在此级别，事务的修改，即使没有提交，对其他事务都是可见的。事务可以读取未提交的数据，也就是会产生脏读。

 

READ COMMINTTED（读已提交）：大多数数据库系统的默认级别就是它，但是Mysql不是。事务能够在事务开始期间读到其他事务提交的结果，会导致一个事务里对同一条数据的多次查询可能会得到不同的结果，也就是会产生不可重复读的问题。

 

 

REPEATABLE READ（可重复读）：Mysql默认级别，它能够解决不可重复读的问题，但是在一个事务里对一段数据的多次读取可能会导致不同的结果，也就是幻读的问题（主要是其他事务插入数据，导致多次读取结果不一样）。但是InnoDB完美解决的这个幻读的问题，主要采用Next-key Lock算法，对间隙进行加锁。

 

SERIALIZABLE（可串行化）：强制事务串行执行，避免了所有的问题，但是无并发。

为什么InnoDB能够保证原子性？用什么方式？

在事务里任何对数据的修改都会写一个Undo log，然后进行数据的修改，如果出现错误或者用户需要回滚的时候可以利用undo log的备份数据恢复到事务开始之前的状态。

为什么InnoDB能够保证持久性？用什么方式？

在一个事务中的每一次SQL操作之后都会写入一个redo log到buffer中，在最后COMMIT的时候，必须先将该事务的所有日志写入到redo log file进行持久化（这里的写入是顺序写的），待事务的COMMIT操作完成才算完成。即使COMMIT后数据库有任何的问题，在下次重启后依然能够通过redo log的checkpoint进行恢复。

为什么InnoDB能够保证一致性？用的什么方式？

在事务处理的ACID属性中，一致性是最基本的属性，其它的三个属性都为了保证一致性而存在的。

为了保证并发情况下的一致性，引入了隔离性，即保证每一个事务能够看到的数据总是一致的，就好象其它并发事务并不存在一样。用术语来说，就是多个事务并发执行后的状态，和它们串行执行后的状态是等价的。

为什么RU级别会发生脏读，而其他的隔离级别能够避免？

RU级别的操作其实就是对事务内的每一条更新语句对应的行记录加上读写锁来操作，而不把一个事务当成一个整体来加锁，所以会导致脏读。但是RC和RR能够通过MVCC来保证记录只有在最后COMMIT后才会让别的事务看到。

为什么RC级别不能重复读，而RR级别能够避免？

通过MVCC机制来做的，在RC事务隔离级别下,每次语句执行都关闭数据快照,然后重新创建一份数据快照。而在RR下,事务开始后第一个读操作创建数据快照,一直到事务结束关闭。

为什么InnoDB的RR级别能够防止幻读？

这个是因为RR隔离级别使用了Next-key Lock这么个东东，也就是Gap Lock+Record Lock的方式来进行间隙锁定，也就是锁定了一个范围，避免了幻读。