《无法解释的死锁》的解释

今天继续跟踪deadlock问题,昨天在问开发同学时,说确实没有设置事务隔离级别,让我绕了比较大圈子,索性要求开发同学的代码拿过来看一下,发现代码取数据的时候使用DbInputFormat类,这个类有个问题,默认是使用SERIALIZABLE来创建连接,所有的疑问都没有了。

这个deadlock没有重现,让发DbInputFormat类中默认配置改成RR后,slave上不再报deadlock.

Session 1:
lijie@test 04:12:10>show index from test_deadlock;
+---------------+------------+-------------+--------------+
| Table         | Non_unique | Key_name    | Column_name  |
+---------------+------------+-------------+--------------+
| test_deadlock |          0 | PRIMARY     | id           |
| test_deadlock |          1 | idx_gmt_mod | gmt_modified |
+---------------+------------+-------------+--------------+
lijie@test 04:07:46>SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Query OK, 0 rows affected (0.00 sec)
lijie@test 04:09:22>show variables like '%iso%';
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| tx_isolation  | SERIALIZABLE |
+---------------+--------------+
1 row in set (0.00 sec)
 
lijie@test 04:09:28>set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
 
lijie@test 04:10:38>SELECT *
    ->   from test_deadlock
    ->  where gmt_modified >= from_unixtime(1353484868)
    ->   AND gmt_modified < from_unixtime(1353484877) LIMIT 1 OFFSET 0;
 
这个select会加S锁到PRIMARY和idx_gmt_mod  这两个索引的range(innodb是逐行加锁)
Session 2的sql:
update test_deadlock set gmt_modified = now() where id = 1

Session 1 锁定idx_gmt_mod的index entra, –S锁
Session 2 锁定cluster index中id一个index entra -X锁
Session 1 申请锁定cluster index中id index entra -S锁(等待)
Session 2 申请锁定idx_gmt_mod的index entra -X锁(死锁发生)

参考:http://venublog.com/2010/07/19/mapreduce-dbinputformat-serilization-on-readers/


One Response to “《无法解释的死锁》的解释”

  1. dbathink说道:

    交换下连接 http://www.dbathink.com/
    另外,你的微博插件是什么?

Post a Comment