Mysql Error:1205错误诊断

作者:天士梦  来源:IT165收集  发布日期:2014-03-07 22:18:55

      前两天遇到一个1205(ER_LOCK_WAIT_TIMEOUT)的错误,弄了半天终于找到原因,掌握原理+细心才能找到罪归祸首。下面我给大家分享下这个问题的分析处理过程,希望对大家有所帮助。接到slave error告警后,看到现场是这样的:slave重做binlog因为锁超时中断,报HA_ERR_LOCK_WAIT_TIMEOUT错误。

     超时,easy啊,心想估计是有大事务长期持有锁,导致其他事务超时等待。但是这个库是只读的备库,不可能有写事务,通过show processlist命令也确实没有发现写事务,倒是有一个大查询任务。当时觉得MVCC查询不上锁啊,直接无视。我尝试重新start slave,发现没过几秒钟,错误依然出现,并且Exec_Master_Log_Pos没有变化,这说明同样的事务尝试写错误,依然被堵住,导致锁超时等待了。这一定是事务持有锁导致锁超时,但机器上除了查询,啥也木有。隔离级别,确认下隔离级别,虽然生产环境中机器都是RC(读提交)模式,但也不排除这种可能。但结果再次让我失望,事务隔离级别是读提交。

会不会是存储引擎的问题,我又验证了一把,表是innodb存储引擎,读不存在说是上表锁的情况。无语了,难道innodb的MVCC,读在某些情况下也上锁?这岂不是与读不上锁上违背吗?继续排查问题,查看锁等待情况:

select * from information_schema.innodb_lock_waits;

这说明确实有事务堵住了更新。继续,

SELECT r.trx_id waiting_trx_id,

       r.trx_query waiting_query,

       b.trx_id blocking_trx_id,

       b.trx_query blocking_query,

       b.trx_mysql_thread_id blocking_thread,

       b.trx_started,

       b.trx_wait_started

FROM information_schema.innodb_lock_waits w

       INNER JOIN information_schema.innodb_trx b

               ON b.trx_id = w.blocking_trx_id

       INNER JOIN information_schema.innodb_trx r

               ON r.trx_id = w.requesting_trx_id 

从图中可以看到,blocking_query确实是select语句,靠,难道真是它上锁了,上的什么锁呢?

select * from information_schema.innodb_locks;

可以看到一个读锁和一个写锁,这说明了,查询的确是上了记录的读锁,锁应该都是在innodb层面加的。到底为啥会上读锁呢?

select trx_id,trx_state,trx_isolation_level from information_schema.innodb_trx;

答案揭晓了,可以看到RUNNING的事务隔离级别是SERIALIZABLE,串行化隔离级别导致读上锁,进而阻塞复制无法进行下去。

     这个例子其实很简单,通过这个例子可以看到,information_schema下面的几张表太重要了,暴露了很多信息,方便我们排查问题。同时排查问题时,一定要坚信原理,并且细心,问题总会水落石出。

Tag标签: Mysql   Error:1205错误诊断  
  • 专题推荐

About IT165 - 广告服务 - 隐私声明 - 版权申明 - 免责条款 - 网站地图 - 网友投稿 - 联系方式
本站内容来自于互联网,仅供用于网络技术学习,学习中请遵循相关法律法规