作为在LAMP堆栈上运行的Web应用程序的自动部署过程的一部分,我们删除所有触发器和存储过程,并从源代码控制重新创建它们.事实证明,我们没有想过这种方法存在隐患.

几天前,在以下一系列事件发生之后,我们设法最终得到了我们的网络应用程序停留在一个可怕的挂起状态的数据库(暂存版本)：

>我从办公室连接到远程数据库(通过Python的MySQLdb,当它发生时)并在Foo表上运行一些SELECT查询.
>我打开连接,因为我很懒.
>我的老板在他的笔记本电脑上进行了一些更改,推送到网络服务器上的远程仓库,然后去看午餐而不看输出
> Web服务器上的部署挂钩尝试更新数据库中的触发器和存储过程,但是甚至无法对第一个触发器进行DROP,因为触发器涉及Foo表,我当前正在休眠的连接之前已经完成了一些SELECT .
>现在没有人可以从Foo表中进行SELECT,因为尝试DROP触发器的连接已经取消了对Foo表的锁定,阻止任何其他连接以任何方式访问Foo表 – 即使它仍在等待睡觉连接在它实际可以做任何事情之前要关闭.
>依赖Foo表的关键业务流程停滞不前,警报响起,我们的网络应用程序停止为客户提供服务.我的老板愤怒地宣称,如果找不到并解决了问题的原因,头部会滚动,这样就不会再发生这种情况了. (开个玩笑,这只是我们的登台服务器而且我的老板非常友好.)

有趣的是,这种情况不是由任何形式的僵局引起的;它是由一个睡眠连接隐式地持有一种阻止DROP TRIGGER语句执行的锁定引起的,这只是因为之前在同一个表上完成了一个SELECT. MySQL的任何反死锁功能都不会自动杀死一个进程并保存这种情况,因为一旦我的原始进程 – 那个只执行了SELECT的空闲进程 – 被杀死,一切都可以继续.默认情况下,MySQL锁以这种方式表现的事实对我来说是不正常的,但这不是重点.我正试图找出一种方法来确保上述灾难情况不会再发生(特别是在我们的实时服务器上).你怎么建议我这样做？

我们在办公室谈到了这个问题,我们看到了几个假设的解决方案：

>在某处更改某些配置设置,以便睡眠进程在默认情况下在10秒后超时,以便睡眠进程永远不会坐在锁上.更好的是,让他们在10秒后释放所有锁,这样我仍然可以去吃午餐并打开我的MySQL shell,或者我的Python窗口打开并激活MySQLdb连接,然后回来使用它,而不用担心破坏任何东西.

>尝试手动运行查询时,这可能会非常恼人,尤其是那些需要分组到事务中的查询.

>对尝试替换触发器和存储过程的查询进行一些魔术,以便获取相关DROP和CREATE所需的锁定为原子操作 – 如果查询无法获取所有锁定它需要立即按顺序,然后释放它们并定期再次尝试直到它工作.

>这可能只是让我们的部署过程永远不会完成,但是,如果数据库太忙而无法一次性获取所有锁.

>大大减少我们所做的模式修改查询的频率(似乎只能通过仅完成SELECT的连接来阻止这些查询),例如通过让我们的部署脚本检查源中的存储过程或触发器在DROPping之前,控制已从数据库中的版本更改,并在数据库上重新创建一个版本.

>这只能缓解问题,实际上并没有消除它.

我们不确定我们考虑的前两个解决方案中的任何一个是否可能在MySQL中,或者如果我们错过了更好的解决方案(我们是开发人员,而不是DBA,这超出了我们的舒适区域).你会推荐什么？

解决方法:

这可能是因为默认情况下自动提交是关闭的,如PEP 249所指定的.这似乎导致任何SELECT锁定元数据表.您可以打开自动提交(只要基于您的应用程序代码是安全的),这将立即关闭与SELECT关联的隐式事务.或者,使用显式事务.