MySQL优化系列（八）--锁机制超详细解析（锁分类、事务并发、引擎并发控制）

一、MySQL锁机制概述：
（一）什么是锁，以及为什么使用锁和锁的运作？
锁是计算机协调多个进程或纯线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所在有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁来解决。
锁的运作？
事务T在对某个数据对象（表或记录等）操作之前，先对系统发出请求，对其枷锁，加锁后事务T就对数据库对象有一定的控制，在事务T释放它的锁之前，其他事务不能更新此数据对象。
（二）锁定机制分类？
锁定机制就是数据库为了保证数据的一致性而使各种共享资源在被并发访问访问变得有序所设计的一种规则。MySQL数据库由于其自身架构的特点，存在多种数据存储引擎，每种存储引擎所针对的应用场景特点都不太一样，为了满足各自特定应用场景的需求，每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计，所以各存储引擎的锁定机制也有较大区别。

按封锁类型分类：（数据对象可以是表可以是记录）
1）排他锁：（又称写锁，X锁）

一句总结：会阻塞其他事务读和写。

若事务T对数据对象A加上X锁，则只允许T读取和修改A，其他任何事务都不能再对加任何类型的锁，知道T释放A上的锁。这就保证了其他事务在T释放A上的锁之前不能再读取和修改A。

2）共享锁：（又称读取，S锁）

一句总结：会阻塞其他事务修改表数据。

若事务T对数据对象A加上S锁，则其他事务只能再对A加S锁，而不能X锁，直到T释放A上的锁。这就保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

X锁和S锁都是加载某一个数据对象上的。也就是数据的粒度。

按封锁的数据粒度分类如下：
1）行级锁定（row-level）：

一句总结：行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

**详细：**行级锁定最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。

**缺陷：**由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。

2）表级锁定（table-level）：

一句总结：表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

**详细：**和行级锁定相反，表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。

**缺陷：**锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并发度大打折扣。

3）页级锁定（page-level）：（MySQL特有）

一句总结：页级锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

**详细：**页级锁定是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。

**缺陷：**页级锁定和行级锁定一样，会发生死锁。

从这里我们应该引申去思考行锁更多的缺点：（因为我们执行sql主要依赖行锁来提高并发度）
1- 比表级锁、页级锁消耗更多内存
2- 如果你在大部分数据上经常进行GROUP BY操作或者必须经常扫描整个表，比其它锁定明显慢很多。
3- 更容易发生死锁。

其次，我们应该思考什么情况下用表锁、行锁
（因为我们主要使用引擎默认是这两个，MyISAM是表级锁；InnoDb是行级锁，当然也支持表级锁）
（四）活锁与死锁的探究：
采用封锁的方法可以有效防止数据的不一致性，单封锁本身会引起麻烦，就是死锁和活锁。

1）活锁：
定义：
如果事务T1封锁了数据对象R后，事务T2也请求封锁R，于是T2等待，接着T3也请求封锁R。当T1释放了加载R上的锁后，系统首先批准T3的请求，T2只能继续等待。接着T4也请求封锁R，T3释放R上的锁后，系统又批转了T4的请求。这样的一直循环下去，事务T2就只能永远等待了，这样情况叫活锁。

解决方法：
采用先来先服务的队列策略。队列式申请。

2）死锁：
定义：
当两个事务分别锁定了两个单独的对象，这时每一个事务都要求在另一个事务锁定的对象上获得一个锁，因此每一个事务都必须等待另一个事务释放占有的锁。这就发生了死锁了。

例子：两个事务，事务都有两个操作。当同时发生时，事务A锁定first表，事务B锁定second表，导致了死锁。

解决方法：
理论上预防死锁的发生就是要破坏产生死锁的条件。

一次封锁法。
一次封锁法要求每个事务必须一次将所有要使用的数据全部加锁，否则就不能继续执行。
此方法存在的问题：（一）一次将以后要用到的全部数据加锁，加大封锁范围，降低系统的并发度。（二）数据库中数据是不断变化的，原来不要求封锁的数据，在执行过程中可能会变成封锁对象，所以很难事先精确确定每个事务要封锁的数据对象，为此只能扩大封锁范围，将事务在执行过程中可能要封锁的数据对象全部加锁，这就更降低了并发度。

顺序封锁法：
预先对数据对象规定一个封锁熟悉怒，所有事务都按这个顺序实行封锁。如：在B树结构的索引中，规定封锁的顺序必须从根结点开始，然后是下一级的子女结点，逐级封锁。
此方法存在的问题：（一）数据库系统中封锁的数据对象极多，随着数据的插入、删除等操作而不断变化，要维护这样的资源的封锁顺序很难，成本高。（二）事务的封锁请求可随着事务的执行而动态地决定，很难事先确定每一个事务要封锁哪些对象，因此很难按规定的顺序去加锁。比如：规定数据对象的封锁顺序：A、B、C、D、E。事务T3起初要求封锁数据对象B、C、E，但当它封锁了B、C后，才发现需要封锁A。

以上就是策略就是操作系统中广为采用的预防死锁的策略，但并不适合数据库。所以数据库系统一般采用诊断并解除死锁的方法。

死锁的诊断与解除：
数据库系统中诊断死锁的方法与操作系统类似，一般是用超时法或事务等待图法。

超时法：
指的是如果一个事务的等待时间超过了规定的时限，就认为发送死锁。
不足：（一）有可能误判死锁，事务因为其他原因使等待时机超过时限。（二）时限若设置得太长，死锁发生后不能及时发现。
等待图法：
指的是用事务等待图动态反应所有事务的等待情况。
事务等待图是一个有向图G=(T,U)，其中T为结点的集合，每个结点表示正在运行的事务。U为边的集合，每条边表示事务等待的情况。若T1等待T2，则T1、T2之间划一条有向边，从T1指向T2。事务等待图动态地反映了所有事务的等待情况。并发控制子系统周期性地检测事务等待图，如果发现图中存在回路，则表示系统中出现了死锁。
以上就是死锁的诊断与解除了**。而且DBMS并发控制子系统一旦检测到系统中存在死锁，就会设法解除。通常是选择一个处理死锁代价最小的事务，将其撤销，释放此事务持有的所有的锁，使其他事务能继续运行下去。（而且要对撤销的事务所执行的数据修改操作进行恢复）
二、MySQL各种锁详解（并针对MyISAM和InnoDB引擎）：
意向锁：解决表级锁和行级锁之间的冲突
事务A锁住了表中的一行，让这一行只能读，不能写。之后，事务B申请整个表的写锁。如果事务B申请成功，那么理论上它就能修改表中的任意一行，这与A持有的行锁是冲突的。数据库需要避免这种冲突，就是说要让B的申请被阻塞，直到A释放了行锁。数据库要怎么判断这个冲突呢？

普通认为两步：step1：判断表是否已被其他事务用表锁锁表。step2：判断表中的每一行是否已被行锁锁住。但是这样的方法效率很低，因为要遍历整个表。

所以解决方案是：意向锁。

在意向锁存在的情况下，事务A必须先申请表的意向共享锁，成功后再申请一行的行锁。

在意向锁存在的情况下，两步骤为：step1：判断表是否已被其他事务用表锁锁表。step2：发现表上有意向共享锁，说明表中有些行被共享行锁锁住了，因此，事务B申请表的写锁会被阻塞。

注意：
申请意向锁的动作是数据库完成的，就是说，事务A申请一行的行锁的时候，数据库会自动先开始申请表的意向锁，不需要我们程序员使用代码来申请。
意向锁是一种表级锁，锁的粒度是整张表。结合共享与排他锁使用，分为意向共享锁（IS）和意向排他锁（IX）。意向锁为了方便检测表级锁和行级锁之间的冲突，故在给一行记录加锁前，首先给该表加意向锁。也就是同时加意向锁和行级锁。
2）刚刚MySQL锁机制总述：
MySQL中不同的存储引擎支持不同的锁机制。比如MyISAM和MEMORY存储引擎采用的表级锁，BDB采用的是页面锁，也支持表级锁，InnoDB存储引擎既支持行级锁，也支持表级锁，默认情况下采用行级锁。

三类： 行级、表级、页级
仅从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

（3）MyISAM引擎的锁机制：
MyISAM只有表锁，其中又分为读锁和写锁。
前面得知：mysql的表锁有两种模式：表共享读锁（table read lock）和表独占写锁（table write lock）。（意向锁是解决行锁与表锁冲突，不在此引擎中）。

所以对于MyISAM引擎的锁兼容用一个常规图描述：

表阅读姿势：先确定当前锁模式，思考另一用户请求，就去看请求锁模式，思考是否兼容。

（一）MyISAM表的读操作，不会阻塞其他用户对同一个表的读请求，但会阻塞对同一表的写请求；
（二）MyISAM表的写操作，会阻塞其他用户对同一个表的读和写操作
（三）MyISAM表的读、写操作之间以及写操作之间是串行的。。
当一个线程获得对一个表的写锁后，只有持有锁线程可以对表进行更新操作，其他线程的读、写操作都会等待，直到锁被释放为止。
MyISAM引擎的锁表：
mysql认为写请求一般比读请求重要；
myisam在执行查询语句前，会自动给涉及的所有表加读锁，在执行更新操作前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此用户一般不需要直接用lock table命令给myisam表显示枷锁。
（三）另外，MyISAM的锁调度就是让我们更好地去让MySQL适应市场的需求，解决刚刚首要明确的问题。
通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。
虽然上面方式都挺极端的。但是MySQL也提供了一种折中的办法来调节读写冲突，即给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL变暂时将写请求的优先级降低，给读进程一定获得锁的机会。
（四）MyISAM并发插入问题：
MyISAM存储引擎有一个系统变量，concurrent_insert，专门用来控制并发插入行为的，值可以为0，1，2.

concurrent_insert为0时候，不允许插入

concurrent_insert为1时候，如果mysql没有空洞（中间没有被删除的行），myISAM运行一个进程读表的时候，另一个进程从表尾插入记录，这也是mysql默认设置。

concurrent_insert为2时候，无论MyISAM表中有没有空洞，都允许在表尾并行的插入。
（4）InnoDB引擎的锁机制：
（一）与MyISAM不同，InnoDB有两大不同点：

1）支持事务
2）采用行级锁
（二）查看InnoDB行锁争用情况：
show status like ‘innodb_row_lock’
（三）Innodb行锁模式以及加锁方法：
Innodb锁有三类：共享锁、意向锁、排它锁。其中意向锁分为意向共享锁和意向排他锁。
表阅读姿势：先确定当前锁模式，思考另一用户请求，就去看请求锁模式，思考是否兼容。

注意：
如果一个事务请求的锁模式与当前锁模式兼容，innodb九江请求的锁授予该事务；反之，不兼容，该事务就等待锁释放。意向锁是innodb自动加的，不需要用户干预。
对更新操作的，innodb会自动给涉及的数据加排他锁，对于select 语句，innodb不会加任何锁。
（四）InnoDB查看锁语句：

//显示共享锁（S） ：
 SELECT * FROM table_name WHERE .... LOCK IN SHARE MODE
 //显示排他锁（X）：
 SELECT * FROM table_name WHERE .... FOR UPDATE.

使用select … in share mode获取共享锁，主要用在需要数据一寸关系时，确定某行记录是否存在，并确保没有人对这个记录进行update或者delete
（五）InnoDB行锁实现方式与验证：（可能会遇到所有事务并发问题–InnoDB是事务引擎）
innodb行锁是通过给索引上的索引项加锁来实现的，这点和oracle不同，后者是通过在数据块中，对相应数据行加锁来实现的。innodb这种行锁实现特别意味着：只有通过索引条件检索数据，innodb才使用行级锁，否则innodb将使用表锁。
**（六）补充：基于InnoDB对索引加锁的间隙锁
定义：**当我们用范围条件来检索数据时，并请求共享或者排它锁时，innodb会给符合条件的已有数据的索引项加锁，对于键值在条件范围内但并不存在的记录，叫做“间隙”，innodb会对这个”间隙“加锁，这种加锁机制就是间隙锁。
间隙锁目的：为了防止幻读，以满足相关隔离级别的要求。另一方面，为了满足其回复和复制的需要
实际开发中，在使用范围条件检索并锁定记录时，innodb这种加锁机制会阻塞符合条件范围内键值的并发插入，会造成严重的所等待。因此，在实际开发中，尤其是并发插入比较多的应用，要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。
我们也应该从这里开始深入：思考InnoDB的行锁算法。
InnoDB是一个支持行锁的存储引擎，锁的类型有：共享锁（S）、排他锁（X）、意向共享（IS）、意向排他（IX）。
也就是我们的LBCC，基于锁的并发控制。
InnoDB有三种行锁的算法：
1）Record Lock：单个记录上的锁
2）gap lock：间隙锁，锁定一个范围，但不包括记录本身。防止同一事务的两次当前读，出现幻读的情况。
next-key lock:1+2,锁定一个范围，并锁定数据本身。对于行的查询，都采用这个方法，以解决幻读的问题。
LBCC这样的加锁方式，并不是真正的并发，或者说它只能实现并发的读，因为它最终实现的是读写串行化，降低了数据库的读写性能。
基于这样，MySQL在后续设计中引入MVCC这一基于多版本的并发控制协议。主要是解决读写的冲突！！！
MVCC只工作在RC与RR级别下，当然最主要还是为RR级别设计。（MVCC的博客解析太多了，我就不多说了）。
然后我们要思考一个问题，MVCC解决读写冲突，那写写冲突如何解决？
那就是一个乐观锁的设计了。（这个博客也很多，也不多说了）
（七）补充：InnoDB引擎什么时候使用表锁？？
对于InnoDB表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们之所以选择InnoDB表的理由。但在个另特殊事务中，也可以考虑使用表级锁。
1.事务需要更新大部分或全部数据，表又比较大时，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和索冲突，这种情况可以考虑用表锁来提高该事务的执行速度。
2.事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。
在innodb下，使用表锁需要注意：
1）使用lock table虽然可以给innodb加表级锁，但是，表锁不是由innodb存储引擎层管理的，而是由mysql server负责的。近当autocommit=0 innodb_table_lock=1（默认设置）时，innodb才知道mysql加的表锁，mysql server 才能感知innodb加的行锁，这种情况下，innodb才能自动识别设计表级锁的死锁；否则，innodb将无法自动检测并处理这种死锁。
2）在用lock table 对innodb锁时注意，要将autocommit设为0，否则mysql不会给表加锁；事务结束前，不要用unlock table释放表锁，因为unlock tables隐含的提交事务。commit或rollback不能释放lock tables 加的表级锁，必须用unlock tables释放锁。
三、项目中锁的设计方式
1）两个引擎的死锁对比
ＭyISAM表锁是无死锁的，这是因为ＭyISAM总是一次性获取所需的全部所，要么全部满足，要么等待，因此不会出现死锁。但是在innodb中，除单个sql组成的事务外，锁时逐步获得的，所以会发生死锁。
发生死锁后，innodb一般都自动检测到，并使一个事务释放锁并退回，另一个事务获得所，继续完成实物。但在涉及外部锁或的情况下，innodb并不能完全自动检测到死锁，这需要通过设置锁等待超时参数innodb_lock_wait_timeout来解决。这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法以及获取所需的锁而挂起时，会占用大量计算机资源，造成验证性能问题，甚至拖垮数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。
一般，死锁都是应用设计的问题，通过调整业务流程、数据库对象设计、事务大小、以及访问数据库的sql语句等，绝大部分可以避免。
2）常用的避免和解决死锁问题的方法
1、应用中，如果不同的程序会并发存储多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。如果两个session访问两个表的顺序不同，发生死锁的机会就非常高，但以相同的顺序来访问，死锁就可能避免。
2、在程序以批量方式处理数据时，如果事先对数据排序，来保证每个线程按固定顺序来处理记录，也可以大大降低死锁的可能。
3、在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应该什么情况共享锁，更新时再申请排他锁，
4、在REPEATEABLE-READ个立即被下，如果两个线程同时对相同记录用SELECT…ROR UPDATE加排他锁，在没有符合记录的前提下，两个线程都会加锁成功。程序发现记录山不存在，就视图插入一条新纪录，如果两个线程都这样做，就会出现死锁。这种情况下，将隔离界别改成READ COMMITTED，就可以避免问题。
5、当隔离级别为READ COMMITTED，如果两个线程都先执行SELECT…FOR UPDATE，判断是否存在符合条件的记录，如果没有，就插入记录。此时，只有一个线程能插入成功，另一个线程会出现锁等待，当第１个线程提交后，第２个线程会因主键重出错，但虽然这个线程出错了，却会获得一个排他锁！这时如果有第３个线程又来申请排他锁，也会出现死锁。对于这种情况，可以直接做插入操作，然后再捕获主键重异常，或者在遇到主键重错误时，总是执行ROLLBACK释放获得的排他锁。
尽管通过上面的设计和优化等措施，可以大减少死锁，但死锁很难完全避免。因此，在程序设计中总是捕获并处理死锁异常是一个很好的编程习惯。
3）如何查看死锁
如果出现死锁，可以用SHOW INNODB STATUS命令来确定最后一个死锁产生的原因和改进措施。