MySQL数据库之MySQL数据库性能优化之避免数据死锁的发生-职坐标

MySQL数据库之MySQL数据库性能优化之避免数据死锁的发生

小标 2018-11-13 来源：阅读 759 评论 0

摘要：本文主要向大家介绍了MySQL数据库之MySQL数据库性能优化之避免数据死锁的发生，通过具体的内容向大家展现，希望对大家学习MySQL数据库有所帮助。

本文主要向大家介绍了MySQL数据库之MySQL数据库性能优化之避免数据死锁的发生，通过具体的内容向大家展现，希望对大家学习MySQL数据库有所帮助。

总结之前的内容，MySQL数据库性能优化的途径有以下几个方式

提高硬件方面的配置，比如SSD固态硬盘等。选择合适的存储引擎，一般使用Innodb（支持事务处理等优势）。适当的创建索引和进行Query cache配置相结合。尽量避免MySQL中出现死锁（数据库中事务锁定的其实是表的索引）

来学一下一下第四点知识：mysql中的锁的使用。

4-3、InnoDB中的锁

虽然锁机制是InnoDB引擎中为了保证事务性而自然存在的，在索引、表结构、配置参数一定的前提下，InnoDB引擎加锁过程是一样的，所以理论上来说也就不存在“锁机制能够提升性能”这样的说法。但如果技术人员不理解InnoDB中的锁机制或者混乱、错误的索引定义和同样混乱的SQL写操作语句共同作用，那么导致死锁出现的可能性就越大，需要InnoDB进行死锁检测的情况就越多，最终导致不必要的性能浪费甚至事务执行失败。所以理解InnoDB引擎中的锁机制可以帮助我们在高并发系统中尽可能不让锁和死锁成为数据库服务的一个性能瓶颈。

4-3-1、InnoDB中的锁类型

本文讲解的锁机制主要依据MySQL Version 5.6以及之前的版本（这是目前线上环境使用最多的版本），在MySQL Version 5.7以及最新的MySQL 8.0中InnoDB引擎的锁类型发生了一些变化（后文会提及），但基本思路没有变化。InnoDB引擎中的锁类型按照独占形式可以分为共享锁和排它锁（还有意向性共享锁和意向性排它锁）；按照锁定数据的范围可以分为行级锁（其它引擎中还有页级锁的定义）、间隙锁、间隙复合锁？？和表锁；为了保证锁的粒度能够至上而下传递，InnoDB中还设计有不能被用户干预的意向共享锁和意向排它锁。

共享锁（S锁）

由于InnoDB引擎支持事务，所以需要锁机制在多个事务同时工作时保证每个事务的ACID特性。共享锁的特性是多个事务可以同时为某个资源加锁后进行读操作，并且这些事务间不会出现相互等待的现象。

排它锁（X锁）

排它锁又被称为独占锁，一旦某个事务对资源加排它锁，其它事务就不能再为这个资源加共享锁或者排它锁了。一直要等待到当前的独占锁从资源上解除后，才能继续对资源进行操作。排它锁只会影响其他事务的加锁操作，也就是说如果其它事务只是使用简单的SELECT查询语句检索资源，就不会受到影响，因为这些SELECT查询语句不会试图为资源加任何锁，也就不会受资源上已有的排它锁的影响。我们可以用一张表表示排它锁和共享锁的互斥关系：

锁类型

共享锁S

排它锁X

共享锁S 不互斥：多个共享锁不会相互影响相互等待互斥：如果某个资源要加共享锁，则需要等待到资源上的排它锁配解除后，才能进行这个操作

排它锁X 互斥：如果资源要加排它锁，则需要等待到资源上所有共享锁都被解除后，才能进行这个操作互斥：如果某个资源要加排它锁，则需要等待到资源上的排它锁配解除后，才能进行这个操作

排它锁和共享锁的互斥关系

行级锁（Record lock）

行级锁是InnoDB引擎中对锁的最小支持粒度，即是指这个锁可以锁定数据表中某一个具体的数据行，锁的类型可以是排它锁也可以是共享锁。例如读者可以在两个独立事务中同时使用以下语句查询指定的行，但是两个事务并不会相互等待：

# lock in share mode 是为满足查询条件的数据行加共享锁

# 注意它和直接使用select语句的不同特性

select * from myuser where id = 6 lock in share mode;

间隙锁（GAP锁）

间隙锁只有在特定事务级别下才会使用，具体来说是“可重复读”（Repeatable Read ）这样的事务级别，这也是InnoDB引擎默认的事务级别，它的大致解释是无论在这个事务中执行多少次相同语句的当前读操作，其每次读取的记录内容都是一样的，并不受外部事务操作的影响。间隙锁主要为了防止多个事务在交叉工作的情况下，特别是同时进行数据插入的情况下出现幻读。举一个简单的例子，事务A中的操作正在执行以下update语句的操作：

......

# 事务A正在执行一个范围内数据的更新操作

# 大意是说将用户会员卡号序列大于10的所有记录中user_name字段全部更新为一个新的值

update myuser set user_name = '用户11' where user_number >= 10;

......

其中user_number带有一个索引（后续我们将讨论这个索引类型对间隙锁策略的影响），这样的检索条件很显然会涉及到一个范围的数据都将被更新（例如user_number==10、13、15、17、19、21……），于此同时有另一个事务B正在执行以下语句：

......

# 事务B正在执行一个插入操作

insert into myuser(.........,'user_number') values (.........,11);

# 插入一个卡号为11的新会员，然后提交事务B

......

如果InnoDB只锁住user_number值为10的非聚簇索引和相应的聚簇索引，显然就会造成一个问题：在A事务处理过程中，突然多出了一条满足更新条件的记录。事务A会很纠结的，很尴尬的。如果读者是InnoDB引擎的开发者，您会怎么做呢？正确的做法是为满足事务A所执行检索条件的整个范围加锁，这个锁不是加在某个或某几个具体的记录上，因为那样做还是无法限制类似插入“一个卡号为11的新纪录”这样的情况，而是加在到具体索引和下一个索引之间，告诉这个索引B+树的其它使用者，包括这个索引在内的之后区域都不允许使用。这样的锁机制称为间隙锁（GAP锁）。

间隙锁和行级锁组合起来称为Next-Key Lock，实际上这两种锁一般情况下都是组合工作的。

表级锁：没有可以检索的索引，就无法使用InnoDB特定的锁。另外，索引失效InnoDB也会为整个数据表加锁。如果表级锁的性质是排它锁（实际上大多数情况是这样的锁），那么所有试图为这张数据表中任何资源加共享锁或者排它锁的事务都必须等待在数据表上的排它锁被解除后，才能继续工作。表级锁可以看作基于InnoDB引擎工作的数据表的最悲观锁，它是InnoDB引擎为了保持事务特性的一场豪赌。例如我们有如下的数据表结构：

uid（PK） varchar

user_name varchar

user_sex int

这张数据表中只有一个由uid字段构成的主索引。接着两个事务同时执行以下语句：

begin;

select * from t_user where uid = 2 lock in share mode;

#都先不执行commit，以便观察现象

#commit;

这里的select查询虽然使用的检索依据是uid，但是设置检索条件时uid的varchar类型却被错误的使用成了int类型。那么数据表将不再使用索引进行检索，转而进行全表扫秒。这是一种典型的索引失效情况，最终读者观察到的现象是，在执行以上同一查询语句的两个事务中，有一个返回了查询结果，但是另外一个一直为等待状态。以上的小例子也可以让读者看到，科学管理索引在InnoDB引擎中是何等重要。本文后续部分将向读者介绍表级锁的实质结构。

意向共享锁（IS锁）和意向排它锁（IX锁）

为了在某一个具体索引上加共享锁，事务需要首先为涉及到的数据表加意向共享锁（IS锁）；为了在某一个具体所以上加排它锁，事务需要首先为涉及到的数据表加意向排它锁（IX锁）。这样InnoDB可以整体把握在并发的若干个事务中，让哪些事务优先执行更能产生好的执行效果。意向共享锁是InnoDB引擎自动控制的，开发人员无法人工干预，也不需要干预。

4-3-2、加锁过程实例

InnoDB引擎中的锁机制基于索引才能工作。对数据进行锁定时并不是真的锁定数据本身，而是对数据涉及的聚集索引和非聚集索引进行锁定。在之前的文章中我们已经介绍到，InnoDB引擎中的索引按照B+树的结构进行组织，那么加锁的过程很明显就是在对应的B+树上进行加锁位置检索和进行标记的过程。并且InnoDB引擎中的非聚簇索引最终都要依靠聚簇索引才能找到具体的数据记录位置，所以加锁的过程都涉及到对聚簇索引进行操作。

SELECT关键字的查询操作一般情况下都不会涉及到锁的问题（这种类型的读操作称为快照读），但并不是所有的查询操作都不涉及到锁机制。只要SELECT属于某种写操作的前置子查询/检索或者开发人员显式为SELECT加锁，这些SELECT语句就涉及到锁机制——这种读操作称为当前读。而执行Update、Delete、Insert操作时，InnoDB会根据会根据操作中where检索条件所涉及的一条或者多条数据加排它锁。

为了进一步详细说明各种典型的加锁过程，本小节为读者准备了几个实例场景，并使用图文混合的方式从索引逻辑层面上进行说明。后续的几种实例场景都将以以下数据表和数据作为讲解依据：

CREATE TABLE `myuser` (

`Id` int(11) NOT NULL AUTO_INCREMENT,

`user_name` varchar(255) NOT NULL DEFAULT '',

`usersex` int(9) NOT NULL DEFAULT '0',

`user_number` int(11) NOT NULL DEFAULT '0',

PRIMARY KEY (`Id`),

UNIQUE KEY `number_index` (`user_number`),

KEY `name_index` (`user_name`)

)

这张表中有三个索引，一个是以id字段为依据的聚簇索引，一个是以user_name字段为依据的非唯一键非聚簇索引，最后一个是以user_number字段为依据的唯一键非聚簇索引。我们将在实例场景中观察唯一键索引和非唯一键索引在加锁，特别是加GAP锁的情况的不同点。这张数据表中的数据情况如下图所示：

4-3-2-1、行锁加锁过程

首先我们演示一个工作在InnoDB引擎下的数据表只加行锁的情况。

begin;

update myuser set user_name = '用户11' where id = 10;

commit;

以上事务中只有一条更新操作，它直接使用聚簇索引作为检索条件。聚簇索引肯定是一个唯一键索引，所以InnoDB得出的加锁条件也就不需要考虑类似“insert into myuser(id,………) values(10,………)”这样的字段重复情况。因为如果有事务执行了这样的语句，就会直接报错退出。那么最终的加锁结果就是：只需要在聚簇索引上加X锁。

（额~~~你要问我为什么树结构会是连续遍历的？请重读B+树的介绍）

其它事务依然可以对聚簇索引上的其它节点进行操作，例如使用update语句更新id为14的数据：

begin;

update myuser set user_name = '用户1414' where id = 14;

commit;

当然，由于这样的执行过程没有在X锁临近的边界加GAP锁，所以开发人员也可以使用insert语句插入一条id为11的数据：

begin;

insert into myuser(id,user_name,usersex,user_number) values (11,'用户1_1',1,'110110110');

commit;

4-3-2-2、间隙锁加锁过程

工作在InnoDB引擎下的数据表，更多的操作过程都涉及到加间隙锁（GAP）的情况，这是因为毕竟大多数情况下我们定义和使用的索引都不是唯一键索引，都在“可重复读”的事务级别下存在“幻读”风险。请看如下事务执行过程：

begin;

update myuser set usersex = 0 where user_name = '用户8'

commit;

这个事务操作过程中的update语句，使用非唯一键非聚簇索引’name_index’进行检索。InnoDB引擎进行分析后发现存在幻读风险，例如可能有一个事务在同时执行以下操作：

begin;

insert into myuser(id,user_name,usersex,user_number) values (11,'用户8',1,'110110110');

# 或者执行以下插入

# insert into myuser(id,user_name,usersex,user_number) values (11,'用户88',1,'110110110');

commit;

所以InnoDB需要在X锁临近的位置加GAP锁，避免幻读：

以上示意图有一个注意点，在许多技术文章中对GAP锁的讲解都是以int字段类型为基准，但是这里讲解所使用的类型是varchar。所以在加GAP锁的时候，看似’用户8’和’用户9’这两个索引节点没有中间值了。但是字符串也是可以排序的，所以’用户8’和’用户9’这两个字符串之间实际上是可以放置很多中间值的，例如’用户88’、’用户888’、’用户8888’等。

这就是为什么另外的事务执行类似”insert into myuser(id,user_name,usersex,user_number) values (11,’用户88’,1,’110110110’);”这样的语句，同样会进入等待状态：因为有GAP锁进行独占控制。

4-3-2-3、表锁加锁过程

上文已经提到，索引一旦失效InnoDB也会为整个数据表加锁。那么“为整个数据表加锁”这个动作怎么理解呢？很多技术文章在这里一般都概括为一句话“在XXX数据表上加锁”。要弄清楚表锁的加载位置，我们就需要进行实践验证。首先，为了更好的查看InnoDB引擎的工作状态和加锁状态，我们需要打开InnoDB引擎的监控功能：

# 使用以下语句开启锁监控

set GLOBAL innodb_status_output=ON;

set GLOBAL innodb_status_output_locks=ON;

接下来我们就可以使用myuser数据表中没有键立索引的“usersex”字段进行加锁验证：

begin;

update myuser set user_name = '用户1414' where usersex = 1;

# 先不忙使用commit，以便观察锁状态

#commit;

在执行以上事务之前，myuser数据表中最新的记录情况如下图所示：

可以看到myuser数据表中一共有13条记录，其中满足“usersex = 1”的数据一共有9条记录。那么按照InnoDB引擎行锁机制来说，就应该只有这9条记录被锁定，那么是否如此呢？我们通过执行InnoDB引擎的状态监控功能来进行验证：

show engine innodb status;

# 以下是执行结果（省略了一部分不相关信息）