PHP实际应用中mysql出现“锁等待”实际问题分析研究

在PHP实际应用中，有的时候会出现mysql数据库“锁等待”的错误。出现这个问题的原因，基本是这样的：

当前session mysql开启事务，要对某些数据加锁，正好被其他session的事务占用着，其他session一直不释放锁，超过设定的超时时间，就会报“ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction”——“锁等待超时”。

1）先说说遇到这样的问题怎么解决？

①查看当前数据库的线程情况

SHOW FULL PROCESSLIST;

显示全部的线程，需要有 SUPER 权限，否则，只能看到自己发起的线程。PROCESSLIST这里指用户连接，不是进程列表。

官方文档的描述如下：

SHOW PROCESSLIST shows you which threads are running. You can also get this information from the
INFORMATION_SCHEMA PROCESSLIST table or the mysqladmin processlist command. If you have
the PROCESS privilege, you can see all threads. Otherwise, you can see only your own threads (that is,
threads associated with the MySQL account that you are using). If you do not use the FULL keyword, only
the first 100 characters of each statement are shown in the Info field.

意思就是说上述指令是用来查看那些线程正在运行，你也可以得到这些信息，从INFORMATION_SCHEMA PROCESSLIST这个表，或者通过mysqladmin processlist指令。如果你有PROCESS权限，你可以查看所有的线程。否则，你只能查看你自己当前账户的线程。如果你没有使用FULL关键字，你只能查看每个记录中Info字段里面的前100个字符。

mysql服务端是使用线程来解决并发请求的。也就是有一个session请求mysql，mysql服务端就生成一个线程来处理。如果一PHPweb应用作为客户端请求mysql，有n个线程同时存在，说明当前至少有n个人同时请求PHP网站。

注意：Time字段，代表这个线程的存活时间（执行时间），单位为秒。数值越大，越有问题。正常来说，PHP脚本运行时间最长为30秒，超过30秒强制终止。如果PHP程序中没有写延长脚本运行时间的代码，那基本上是这样的情况：

某一PHP脚本终止了，但是却没有主动请求关闭mysql连接，mysql服务端却一直保留着线程（mysql服务端线程一直处于sleep状态，它一直等着PHP脚本传SQL语句过来）。

如果mysql的线程一直等不到PHP脚本传SQL语句过来，它会主动关闭吗？

show global variables like 'wait_timeout';

答案：会的。看下“wait_timeout”这个mysql变量，28800秒/3600秒 = 8小时。如果一个线程空闲时间超过了“wait_timeout”值，就会自动关闭。

mysql默认“wait_timeout”为28800秒，显然这个值对于PHP来说太长，根据自己程序的需要，尽量把这个值修改为600秒以内。允许PHP脚本运行时间最大值为多少，就把“wait_timeout”设成这个值。

②查看哪个线程里开启了事务

在mysql系统数据库information_schema中，有个INNODB_TRX表。

这个表主要记录当前开启事务的一些线程信息。

SELECT * FROM information_schema.INNODB_TRX;

看是否有正在加锁的事务线程

有一个线程开启了事务，字段“trx_mysql_thread_id”是线程ID号。

通过线程ID号，看下这个线程是否是show full processlist里面的sleep状态的线程，如果是，就证明这个sleep的线程事务一直没有commit或者rollback而是卡住了，这极大可能就是造成“锁等待”问题的根本原因。

③杀死问题线程

KILL 616694;

使用kill命令可杀死问题线程。

这样就把问题解决了。

2）PHP易出现不能及时关闭mysql连接的场景

PHP操作mysql，现在主要使用PDO对象。一次web请求，开始运行PHP脚本，为了避免多次连接mysql，会使用单例模式，只做一次实例化PDO对象。

PHP脚本结束，回收所有的PHP对象，包括回收PDO对象，mysql连接正常关闭。

了解了这个原理，反问：如果PHP脚本没有运行完，就强制关闭网页了，那是不是已经打开的mysql连接就不能主动关闭呢？

带着这个问题我本地做下演示：

环境：phpnanshen.cn仅限我一人电脑可访问。

使用TP框架，写了一个Process控制器类：

当前线程列表：

线程闲时超时时间：

①请求执行index方法

Db::startTrans();//开启事务
$info = model('User')->where(['id'=>1])->lock(true)->select();
sleep(10000);

开启事务，加锁select，脚本睡眠10000秒。

在脚本运行结束前，关闭网页。

②看下当前线程列表

虽然我脚本页面已关闭，但mysql服务端的多了一个sleep线程。

Id：34的线程其实就是刚才index方法连接mysql，mysql生成的处理线程。由于index方法没有在脚本运行完，就强制关闭网页了，所以没有主动关闭mysql连接。

③访问另一方法，看下有没有出现“锁等待”错误。

id为34的线程如果是index方法连接mysql时生成的处理线程，那此时线程中的事务依然存在，user表id为1的数据依然被锁着，其他session请求锁肯定报“锁等待”错误，我们看下是不是这样：

出现了锁等待超时的错误。

备注：

show global VARIABLES like 'innodb_lock_wait_timeout';

mysql变量“innodb_lock_wait_timeout”是innodb表加锁等待超时时间

TP框架右下角的脚本运行时间也正好是51秒左右。

结论：

从上面的测试来看，看似是 PHP脚本在没有运行完就强制关闭网页，会造成mysql连接不能主动关闭。可如果把脚本睡眠10000秒改成10秒，就会发现在10秒内快速关闭网页，mysql的线程在10秒后也自动关闭了。

分析以上原因，这是因为PHP脚本 并不会因为客户提前关闭网页 而 停止运行后面的代码。PHP脚本睡眠10000秒，mysql连接也会在10000秒后关闭。

程序员不主动通过代码关闭mysql连接，那mysql连接会随着脚本的结束而关闭。PHP脚本运行时间越长，mysql连接时间也就越长。

3）造成PHP脚本无法短时间完成的因素有哪些？

造成PHP不能及时关闭mysql连接的主要原因是脚本运行时间超长。那我们分析下容易造成PHP脚本运行时间变长的因素有哪些？

前提：开发者把PHP脚本默认执行最大时间30秒，设置的更大，或者不限制。

①代码中误写了sleep函数

sleep函数多数用于调试程序用，在真正的web应用中，很少会写sleep这种主动影响运行效率的函数。

②代码有bug，出现了死循环

这就是代码的质量问题了，也许代码写得不够严谨，没有做好各种检查判断。

③从数据库查询数据，返回结果慢

一定要做好数据库优化，这是影响网站效率的很大一因素。

④请求第三方API接口无响应★

我发现第三方API，是最容易造成脚本运行时间超长的一大因素。因为请求第三方，很容易受网络因素，第三方的返回时间，请求后无响应。

举个例子：

我在开发用户登录这块时，用户验证成功，往登录日志表插入一条数据，数据中有两个字段ip、ip_info。原先是这样的流程：

这是一个同步的过程。后来经常有用户反映，说登录无反应。我以为是程序错误了，测试后发现，问题出现在②。就是因为淘宝ip接口有时候一直无响应，我的PHP脚本处于卡住的状态，造成了我的用户无法正常登录。

后来就改成这样了：

在用户登录主页后，使用ajax请求IP接口，更新登录日志ipinfo字段。

（后期楠神接触了laravel框架，这个地方使用任务队列完成也是不错的思路）

4）如何去预防不能及时关闭mysql连接的问题？

①避免出现PHP脚本超长运行

需要仔细优化代码、优化数据库

②在可能造成PHP脚本运行时间变长的代码之前，把数据库连接提前关闭或提前提交事务。

尤其是这样的场景下：

需要从数据库加锁查询数据，然后通过数据请求第三方API接口，最好这样设计：

或

虽然会造成有不能及时关闭的mysql sleep线程产生，但是线程里没有对锁的占用，造成危害不大。

最好不要这样：

假如第三方API不返回，mysql服务端就又会多了一个sleep状态的加锁的线程。

这也是我以前犯的一个错误。

记住：不要把请求第三方API写到事务中，受到网络不畅的原因，很容易造成事务无法进行下去却也关闭不了。

拓展：

在请求第三方API之前，mysql最好只做查询，不做修改。等到第三方API返回正确结果以后再对mysql数据做增删改操作。

尤其一些重要的第三方API，都会分成同步返回与异步返回，所以涉及到的增删改操作都最好写到异步返回中。

我以前这样写过：

①中第三方API请求也返回结果了，可能因为锁的缘故，提交事务时，怎么都提交不成功。

而第三方API异步返回②执行的SQL语句都成功了。

那这样就造成①中的数据修改失败，②中的数据修改成功，整个功能执行的都是错误的。

总结：

PHP不能及时关闭mysql连接不可怕，可怕的是不能及时关闭的mysql线程中有加锁的事务，这样就会造成其他session需要加锁的语句执行失败。

拓展：

PHP脚本没有运行完mysql连接不会自动关闭，当PHP脚本出现超时报错、致命错误等，mysql连接会立马自动关闭吗？

我额外做了下测试，出现脚本报错的情况，mysql连接（PDO）可以自动关闭。

注意：

看网上说，关闭mysql连接是在PDO对象的析构方法里执行的。

PHP对象在PHP出现错误时，是不执行析构方法的。也就是说脚本中途出错，PDO对象的析构方法并没有执行。

暂时得出结论，断掉mysql连接不是写在PDO对象的析构方法里。