MySQL数据库—事务和锁(2)

上篇文章我大概讲到了数据库的索引相关知识，有兴趣的朋友可以看看：

数据库索引原理及SQL优化

这篇会讲讲数据库的事务和锁，有不对的地方欢迎指正。

一、InnoDB事务的实现

事务的实现主要是针对事务的四个特性——ACID，分别为隔离性、一致性、原子性、持久性。而其中，数据的一致性是事务的最基本的要求也是最终的目标。在InnoDB中，利用日志恢复技术保证了事务的原子性和持久性，利用并发控制技术，保证了事务之间的隔离性。从而实现了事务的一致性特性。

1、事务的隔离级别

• 读未提交(READ UNCOMMITTED)
• 读已提交(READ COMMITTED)
• 可重复读(REPEATABLE READ)
• 串行化(SERIALIZABLE)

事务的隔离级别越低,可能出现的并发异常越多,但是通常而言系统能提供的并发能力越强。

二、并发控制——锁

锁是网络数据库中的一个非常重要的概念，当多个用户同时对数据库并发操作时，会带来数据不一致的问题，所以，锁主要用于多用户环境下保证数据库完整性和一致性。而数据库锁要解决的问题就是：处理并发

• 按功能分：
  • 共享锁（读锁、S锁）
  • 排它锁（写锁、X锁）
• 按锁粒度分：
  • 页锁
  • 表锁
  • 行锁

MySQL数据库由于其自身架构的特点，存在多种数据存储引擎，每种存储引擎所针对的应用场景特点都不太一样，为了满足各自特定应用场景的需求，每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计，所以各存储引擎的锁定机制也有较大区别。MySQL各存储引擎使用了三种类型（级别）的锁定机制：表级锁定，行级锁定和页级锁定。

1.表级锁（table-level）

使用表级锁定的主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎。表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制：实现逻辑简单、系统负面影响最小、获取锁和释放锁的速度快。由于表级锁一次会将整个表锁定，可以避免死锁问题。锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并发度大打折扣。

2.行级锁（row-level）

使用行级锁定的主要是InnoDB存储引擎。行级锁就是锁定对象的颗粒度很小，也是锁定颗粒度最小的。由于锁定颗粒度小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。行级锁定的弊端是：由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。

3.页级锁定（page-level）

使用页级锁定的主要是BerkeleyDB存储引擎。页级锁定是MySQL中比较独特的一种锁定级别，不太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。
在数据库实现资源锁定的过程中，随着锁定资源颗粒度的减小，锁定相同数据量的数据所需要消耗的内存数量是越来越多的，实现算法也会越来越复杂。不过，随着锁定资源颗粒度的减小，应用程序的访问请求遇到锁等待的可能性也会随之降低，系统整体并发度也随之提升。

总结就是：

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；    
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

二、表锁

MyISAM的两种表级锁模式

MyISAM存储引擎的表级锁定有两种锁模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。对MyISAM表的读操作与写操作之间，以及写操作之间是串行的，不阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。

如何加表锁

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。

MyISAM表锁优化

在优化MyISAM存储引擎锁定问题的时候，最关键的就是如何让其提高并发度。由于锁定级别是不可能改变的了，所以我们首先需要尽可能让锁定的时间变短，然后就是让可能并发进行的操作尽可能的并发。

优化：（1）查询表级锁争用情况（2）缩短锁定时间（3）分离能并行的操作

三、行锁

InnoDB存储引擎，以及MySQL的分布式存储引擎NDBCluster等都是实现了行级锁定。我们就主要分析一下InnoDB的锁定特性。

InnoDB的两种行级锁模式

InnoDB的行级锁定同样分为两种类型，共享锁和排他锁，而在锁定机制的实现过程中为了让行级锁定和表级锁定共存，InnoDB也同样使用了意向锁（表级锁定）的概念，也就有了意向共享锁和意向排他锁这两种。当事务给需要的资源加锁的时，遇到一个共享锁正锁定着需要的资源，可以再加一个共享锁，不过不能加排他锁。如果遇到自己需要锁定的资源已经被一个排他锁占有，只能等待该锁定释放资源之后才能获取锁定资源并添加自己的锁定。而意向锁的作用就是当一个事务在需要获取资源锁定的时候，如果遇到自己需要的资源已经被排他锁占用的时候，该事务可以需要锁定行的表上面添加一个合适的意向锁。如果自己需要一个共享锁，那么就在表上面添加一个意向共享锁。而如果自己需要的是某行（或者某些行）上面添加一个排他锁的话，则先在表上面添加一个意向排他锁。意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在。

如果一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。
意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁；事务可以通过以下语句显示给记录集加共享锁或排他锁

如何加行锁

InnoDB行锁是通过给索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁
在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。不通过索引条件查询的时候或者对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁。

间隙锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。InnoDB使用间隙锁的目的：（1）防止幻读，以满足相关隔离级别的要求。（2）为了满足其恢复和复制的需要。

##InnoDB不仅会对符合条件的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。
select * from emp where empid > 100 for update;

死锁

MyISAM表锁是deadlock free的，这是因为MyISAM总是一次获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。但在InnoDB中，除单个SQL组成的事务外，锁是逐步获得的，当两个事务都需要获得对方持有的排他锁才能继续完成事务，这种循环锁等待就是典型的死锁。产生死锁之后的很短时间内，InnoDB就会检测到系统中产生了死锁，InnoDB会通过相应的判断来选这产生死锁的两个事务中较小的事务来回滚，而让另外一个较大的事务成功完成。

避免死锁的方法

1. 在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表。

2. 以批量方式处理数据的时，如果事先对数据排序，保证每个线程按固定的顺序来处理记录。

3. 在事务中，更新应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁，更新时再申请排他锁，因为当用户申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。

4. 将隔离级别从REPEATABLE-READ改成READ COMMITTED，防止在无符合该条件记录情况下，两个线程同时对相同条件记录用SELECT...FOR UPDATE加排他锁

三、日志恢复——日志恢复技术

事务的执行过程可以简化如下:

1、系统会为每个事务开辟一个私有工作区
2、事务读操作将从磁盘中拷贝数据项到工作区中,在执行写操作前所有的更新都作用于工作区中的拷贝.
3、事务的写操作将把数据输出到内存的缓冲区中,等到合适的时间再由缓冲区管理器将数据写入到磁盘。

redo log(重做日志)

重做日志有重做日志缓存（redo log buffer）和重做日志文件（redo file）即磁盘中的ib_logfile0和ib_logfile1文件。其记录的是每一个数据页中的更改。

文件格式

redo log buffer 记录的是页的修改，以块为单位，每一块固定512byte格式如下

redo log file也是以块为单位存储，但在第一个file中，有一个2KB的头信息，其余的file留有2KB空闲，但不存信息

落盘：redo log buffer默认2M内存大小，重复使用。在以下情况下落盘：

（1）事务提交时（2）Master Thread每1秒执行落盘（3）rede log buffer剩余空间小于1/2时刷新

LSN：Log Sequence Number——日志序列号，代表写入重做日志的字节数，可以看做是一个全局的参数。

• Log sequence number ：redo log buffer的LSN（最新的LSN）

• Log flushed up to ：redo log file的LSN（最近一次重做缓冲落盘的LSN）

• Last checkpoint at：Checkpoint的LSN（最近一次落盘的脏页的LSN）

恢复：数据库宕机重启时，会由重做日志恢复LSN在Last checkpoint at到Log flushed up to之间的数据。

undo log：

undo log是数据库中的一个段（回滚段），对应的页类型为undo page，默认存于共享表空间ibdata1中。

作用：undo log有两个作用，一个实现事务的回滚，一个实现MVCC

分类：

• insert undo：用完就删，因为其他事务不会使用新插入的数据

• update undo：用完放入undo log链表 中，由purge线程中判断是否删除

申请：每个事务开始时，会申请一个新的undo log页或者使用列表中可以重用的页（使用空间小于3/4的页）

删除：每个事务结束后，会将undo log放入列表中，由purge线程判断是否需要删除。因为有的事务可能还需要用到这个页，比如快照读，需要读之前版本的数据。

恢复：逆操作删除