阮胜昌的技术记录站-LinuxMySQL数据库运维

有奖调研 | 1TB硬盘等你拿 AI+区块链的发展趋势及应用调研

1、背景

对于数据库系统来说在多用户并发条件下提高并发性的同时又要保证数据的一致性一直是数据库系统追求的目标，既要满足大量并发访问的需求又必须保证在此条件下数据的安全，为了满足这一目标大多数数据库通过锁和事务机制来实现，MySQL数据库也不例外。尽管如此我们仍然会在业务开发过程中遇到各种各样的疑难问题，本文将以案例的方式演示常见的并发问题并分析解决思路。

2、表锁导致的慢查询的问题

首先我们看一个简单案例，根据ID查询一条用户信息

mysql> select * from user where id=6; 

这个表的记录总数为3条，但却执行了13秒。

出现这种问题我们首先想到的是看看当前MySQL进程状态：

从进程上可以看出select语句是在等待一个表锁，那么这个表锁又是什么查询产生的呢？这个结果中并没有显示直接的关联关系，但我们可以推测多半是那条update语句产生的（因为进程中没有其他可疑的SQL），为了印证我们的猜测，先检查一下user表结构：

果然user表使用了MyISAM存储引擎，MyISAM在执行操作前会产生表锁，操作完成再自动解锁。如果操作是写操作，则表锁类型为写锁，如果操作是读操作则表锁类型为读锁。正如和你理解的一样写锁将阻塞其他操作(包括读和写)，这使得所有操作变为串行；而读锁情况下读-读操作可以并行，但读-写操作仍然是串行。以下示例演示了显式指定了表锁（读锁），读-读并行，读-写串行的情况。

显式开启/关闭表锁，使用lock table user read/write; unlock tables;

session1：

session2：

可以看到会话1启用表锁（读锁）执行读操作，这时会话2可以并行执行读操作，但写操作被阻塞。接着看：

session1：

session2：

当session1执行解锁后，seesion2则立刻开始执行写操作，即读-写串行。

总结：

到此我们把问题的原因基本分析清楚，总结一下——MyISAM存储引擎执行操作时会产生表锁，将影响其他用户对该表的操作，如果表锁是写锁，则会导致其他用户操作串行，如果是读锁则其他用户的读操作可以并行。所以有时我们遇到某个简单的查询花了很长时间，看看是不是这种情况。

解决办法：

1）尽量不用MyISAM存储引擎，在MySQL8.0版本中已经去掉了所有的MyISAM存储引擎的表，推荐使用InnoDB存储引擎。

2）如果一定要用MyISAM存储引擎，减少写操作的时间；

3、线上修改表结构有哪些风险？

如果有一天业务系统需要增大一个字段长度，能否在线上直接修改呢？在回答这个问题前，我们先来看一个案例：

以上语句尝试修改user表的name字段长度，语句被阻塞。按照惯例，我们检查一下当前进程：

从进程可以看出alter语句在等待一个元数据锁，而这个元数据锁很可能是上面这条select语句引起的，事实正是如此。在执行DML（select、update、delete、insert）操作时，会对表增加一个元数据锁，这个元数据锁是为了保证在查询期间表结构不会被修改，因此上面的alter语句会被阻塞。那么如果执行顺序相反，先执行alter语句，再执行DML语句呢？DML语句会被阻塞吗？例如我正在线上环境修改表结构，线上的DML语句会被阻塞吗？答案是：不确定。

在MySQL5.6开始提供了online ddl功能，允许一些DDL语句和DML语句并发，在当前5.7版本对online ddl又有了增强，这使得大部分DDL操作可以在线进行。详见：https://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html

所以对于特定场景执行DDL过程中，DML是否会被阻塞需要视场景而定。

总结：通过这个例子我们对元数据锁和online ddl有了一个基本的认识，如果我们在业务开发过程中有在线修改表结构的需求，可以参考以下方案：

1. 尽量在业务量小的时间段进行；

2. 查看官方文档，确认要做的表修改可以和DML并发，不会阻塞线上业务；

3. 推荐使用percona公司的pt-online-schema-change工具，该工具被官方的online ddl更为强大，它的基本原理是：通过insert… select…语句进行一次全量拷贝，通过触发器记录表结构变更过程中产生的增量，从而达到表结构变更的目的。

例如要对A表进行变更，主要步骤为：

创建目的表结构的空表，A_new;

在A表上创建触发器，包括增、删、改触发器;

通过insert…select…limit N 语句分片拷贝数据到目的表

Copy完成后，将A_new表rename到A表。

4、一个死锁问题的分析

在线上环境下死锁的问题偶有发生，死锁是因为两个或多个事务相互等待对方释放锁，导致事务永远无法终止的情况。为了分析问题，我们下面将模拟一个简单死锁的情况，然后从中总结出一些分析思路。

演示环境：MySQL5.7.20 事务隔离级别：RR

表user：

CREATE TABLE `user` (  
`id` int(11) NOT NULL AUTO_INCREMENT,  
`name` varchar(300) DEFAULT NULL,  
`age` int(11) DEFAULT NULL,  
PRIMARY KEY (`id`)  
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 

下面演示事务1、事务2工作的情况： 

这是一个简单的死锁场景，事务1、事务2彼此等待对方释放锁，InnoDB存储引擎检测到死锁发生，让事务2回滚，这使得事务1不再等待事务B的锁，从而能够继续执行。那么InnoDB存储引擎是如何检测到死锁的呢？为了弄明白这个问题，我们先检查此时InnoDB的状态：

show engine innodb status\G   
------------------------  
LATEST DETECTED DEADLOCK  
------------------------  
2018-01-14 12:17:13 0x70000f1cc000  
*** (1) TRANSACTION:  
TRANSACTION 5120, ACTIVE 17 sec starting index read  
mysql tables in use 1, locked 1  
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)  
MySQL thread id 10, OS thread handle 123145556967424, query id 2764 localhost root updating  
update user set name='haha' where id=4  
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:  
RECORD LOCKS space id 94 page no 3 n bits 80 index PRIMARY of table `test`.`user` trx id 5120 lock_mode X locks rec but not gap waiting  
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0  
0: len 4; hex 80000004; asc ;;  
1: len 6; hex 0000000013fa; asc ;;  
2: len 7; hex 520000060129a6; asc R ) ;;  
3: len 4; hex 68616861; asc haha;;