一个MySQL死锁问题的解决

liyihongcug

一个MySQL死锁问题的解决
最近在项目开发过程中，碰到了数据库的死锁问题，在解决问题的过程中，加深了对MySQL InnoDB引擎锁机制的理解。

我们使用Show innodb status检查引擎状态时，发现了死锁问题：

*** (1) TRANSACTION:
TRANSACTION 0 677833455, ACTIVE 0 sec, process no 11393, OS thread id 278546 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 320
MySQL thread id 83, query id 162348740 dcnet03 dcnet Searching rows for update
update TSK_TASK set STATUS_ID=1064,UPDATE_TIME=now () where STATUS_ID=1061 and MON_TIME<date_sub(now(), INTERVAL 30 minute)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY` of table `dcnet_db/TSK_TASK` trx id 0 677833455 lock_mode X locks rec but not gap waiting
Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
0: len 8; hex 800000000097629c; asc      b ;; 1: len 6; hex 00002866eaee; asc  (f  ;; 2: len 7; hex 00000d40040110; asc    @  ;; 3: len 8; hex 80000000000050b2; asc      P ;; 4: len 8; hex 800000000000502a; asc      P*;; 5: len 8; hex 8000000000005426; asc      T&;; 6: len 8; hex 800012412c66d29c; asc    A,f  ;; 7: len 23; hex 75706c6f6164666972652e636f6d2f6 8616e642e706870; asc xxx.com/;; 8: len 8; hex 800000000000042b; asc        +;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len 8; hex 8000000000004e24; asc      N$;;

*** (2) TRANSACTION:
TRANSACTION 0 677833454, ACTIVE 0 sec, process no 11397, OS thread id 344086 updating or deleting, thread declared inside InnoDB 499
mysql tables in use 1, locked 1
3 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 84, query id 162348739 dcnet03 dcnet Updating
update TSK_TASK set STATUS_ID=1067,UPDATE_TIME=now () where ID in (9921180)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY` of table `dcnet_db/TSK_TASK` trx id 0 677833454 lock_mode X locks rec but not gap
Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
0: len 8; hex 800000000097629c; asc      b ;; 1: len 6; hex 00002866eaee; asc  (f  ;; 2: len 7; hex 00000d40040110; asc    @  ;; 3: len 8; hex 80000000000050b2; asc      P ;; 4: len 8; hex 800000000000502a; asc      P*;; 5: len 8; hex 8000000000005426; asc      T&;; 6: len 8; hex 800012412c66d29c; asc    A,f  ;; 7: len 23; hex 75706c6f6164666972652e636f6d2f6 8616e642e706870; asc uploadfire.com/hand.php;; 8: len 8; hex 800000000000042b; asc        +;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len 8; hex 8000000000004e24; asc      N$;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 843102 n bits 600 index `KEY_TSKTASK_MONTIME2` of table `dcnet_db/TSK_TASK` trx id 0 677833454 lock_mode X locks rec but not gap waiting
Record lock, heap no 395 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 8; hex 8000000000000425; asc        %;; 1: len 8; hex 800012412c66d29c; asc    A,f  ;; 2: len 8; hex 800000000097629c; asc      b ;;

*** WE ROLL BACK TRANSACTION (1)

该死锁问题涉及TSK_TASK表，该表用于保存系统监测任务，相关字段及索引如下：


ID：主键；

MON_TIME：监测时间；

STATUS_ID：任务状态；

索引：KEY_TSKTASK_MONTIME2 (STATUS_ID, MON_TIME)。


经分析，涉及的两条语句应该不会涉及相同的TSK_TASK记录，那为什么会造成死锁呢？

查询MySQL官网文档，发现这跟MySQL的索引机制有关。MySQL的InnoDB引擎是行级锁，我原来的理解是直接对记录进行锁定，实际上不是这样的，其要点如下：


不是对记录进行锁定，而是对索引进行锁定；

在UPDATE、DELETE操作时，MySQL不仅锁定WHERE条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的next-key locking；
如语句UPDATE TSK_TASK SET UPDATE_TIME = NOW() WHERE ID > 10000会锁定所有主键大于等于1000的所有记录，在该语句完成之前，你就不能对主键等于10000的记录进行操作；

当非簇索引(non-cluster index)记录被锁定时，相关的簇索引(cluster index)记录也需要被锁定才能完成相应的操作。

再分析一下发生问题的两条SQL语句，就不难找到问题所在了：

当“update TSK_TASK set STATUS_ID=1064,UPDATE_TIME=now () where STATUS_ID=1061 and MON_TIME<date_sub(now(), INTERVAL 30 minute)”执行时，MySQL会使用KEY_TSKTASK_MONTIME2索引，因此首先锁定相关的索引记录，因为 KEY_TSKTASK_MONTIME2是非簇索引，为执行该语句，MySQL还会锁定簇索引（主键索引）。

假设“update TSK_TASK set STATUS_ID=1067,UPDATE_TIME=now () where ID in (9921180)”几乎同时执行时，本语句首先锁定簇索引(主键)，由于需要更新STATUS_ID的值，所以还需要锁定 KEY_TSKTASK_MONTIME2的某些索引记录。

这样第一条语句锁定了KEY_TSKTASK_MONTIME2的记录，等待主键索引，而第二条语句则锁定了主键索引记录，而等待KEY_TSKTASK_MONTIME2的记录，这样死锁就产生了。

我们通过拆分第一条语句解决了死锁问题：即先查出符合条件的ID：select ID from TSK_TASK where STATUS_ID=1061 and MON_TIME < date_sub(now(), INTERVAL 30 minute)；然后再更新状态：update TSK_TASK set STATUS_ID=1064 where ID in (….)

这样就不会产生索引的竞争问题，死锁问题就解决了。

liyihongcug

一个数据库操作需要半个小时，并且在一个transaction 内，导致其它的操作因为lock 而timeout 。



为了解决这个问题，找了DBA 了解了数据锁的知识：



1. 两种类型的锁： locks:

     共享的(S) 锁允许一个事务去读一行（tuple ）。

     独占的锁(X) 允许一个事务更新或删除一行。



2. 表锁定，页锁定，行锁定？



看看mysql 使用的是什么存储引擎，以区分对锁定的支持：SHOW TABLE STATUS FROM database_name 。ok ，是InnoDB ，它实现标准行级锁定。



锁的有效期决定于transaction 或者for update 这类语句在何时结束或者roll back 。



如果有select * from table for update 此类语句，那么就是表锁定。

如果有select * from table where id between 1 and 10000  for update 此类语句，那么就是页级锁定。

如果有select * from table where id= 1 for update 此类语句，那么就是行级锁定。



还有，索引也会影响到锁的多少。



锁直到transaction 结束后释放。



3. 乐观锁和悲观锁 ：悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。乐观锁机制往往基于系统中的数据存储逻辑，因此也具备一定的局限性。可以参见Hibernate 的乐观锁与悲观锁  



4. 事务隔离级别：InnoDB默认是可重复读的（REPEATABLE READ）。



如何解决此问题？



思路两种：

a. 优化查询、更新的sql 语句，减少页锁定、表锁定以降低冲突的可能性。

b. 增加transaction 粒度。





还有，如何解决死锁？



·         用Use SHOW INNODB STATUS来确定最后一个死锁的原因。这样可以帮助你调节应用程序来避免死锁。

·         总是准备着重新发出事务，如果它因为死锁而失败了。死锁不危险，再试一次。

·         经常提交你的事务。小事务更少地倾向于冲突。

·         如果你正使用锁定读，（SELECT ... FOR UPDATE或 ... LOCK IN SHARE MODE），试着用更低的隔离级别，比如READ COMMITTED。

·         以固定的顺序访问你的表和行。则事务形成良好定义的查询并且没有死锁。

http://www.javaeye.com/topic/236271

liyihongcug

查看mysql的隔离级别:select @@tx_isolation 级别有以下4种
1.Read Uncommitted 未提交读(hibernate在未提交事物之前数据库可以看到记录,也就是所谓的脏读)
2.Read committed 提交读(hibernate在未提交事物之前数据库不可以看到记录)
3.Repeatable Read 重复读(如果某条数据在处于读的状态时候不允许修改,但是数据又有添加则可以读出来)
4.serializalbe 序列化读(以上状态都不允许,只能被一个人读,在这个状态中不允许修改添加)

liyihongcug

数据库设计的一些有效经验
作者：肥猴

以下是针对事务型数据库：
1.是否使用联合主键？个人倾向于少采用联合主键。因为这样会降低索引的效率，联合主键一般都要用到至少一个业务字段，往往是字符串型的，而且理论上多字段的索引比单字段的索引要慢些。看上去似乎也不那么清爽。
在实际的设计中，我尽量避免使用联合主键，有些时候“不得不”使用联合主键。

2.PK采用无意义的字段（逻辑主键）还是有意义的字段（业务主键）？个人倾向于“逻辑主键”，理由是这样设计出的数据库模型结构清晰、关系脉络清楚，往往更符合“第三范式”（虽然不是故意的，呵呵）。而且更容易避开“联合主键”，而且可以使用索引效率高的字段类型，比如int、long、number。缺点是用无意义的字段建立表间的关系，使跨表查询增多，效率下降。（矛盾无处不在，前面刚说完可以提高效率，这里马上又降低效率）。“业务主键”可以提升查询编码的简洁度和效率。
个人使用实际状况，总体来说“逻辑主键”比“业务主键”执行效率低，但不会低到无法满足需求。采用“逻辑主键”比采用“业务主键”更利于数据库模型的结构、关系清晰，也更便于维护。
对于分析型数据库，如数据仓库，千万不要这样做。

3.不要使用多对多关系？个人倾向于少使用多对多关系。这个问题其实不是数据库设计的问题了，在数据库设计中，多对多关系也仅仅存在于概念模型（E-R）阶段，物理模型不在有多对多关系，实际数据库中也不会有“多对多”关系。这是使用ORM时的问题，比如使用Hibernate，多对多关系有时会使编码看起来灵活一些，代价是效率的明显降低。
个人实际使用中，设计时基本不考虑多对多关系，但编码时总会有小组成员使用一些多对多关系，自己建立多对多的ORM，使自己编码方便些，用在数据量小的地方，影响不大。大数据量，则“禁止使用”。

4.为每个表增加一个state字段？我习惯在设计时给每个表设一个state字段，取值0或1，默认值为1，具体业务意义或操作上的意义可以自定义。可以作为一个状态控制字段，如查询、更新、删除条件，单据是否有效（业务单据对应的表会有业务意义上的“有/无效”或“状态”字段，这种情况下，我还是会再加一个state字段），甚至仅仅是控制一条数据是否“有效”（有效的意义你自己定）。在数据迁移（如转入分析用的数据库）时也可能会发挥作用。

5.为每个表设置一些备用字段？没办法，我总是设计不出“完美”的数据表，给每个表加几个备用字段（我一般用字符串型，随你）可以应付“不时之需”，尤其是需要长期维护的、业务可能有临时性变动的系统。

6.尽量不要在一个表中存入其关联表的字段？建议不存！这样做确实可以提高查询效率，但在一个有很多表，并且关联表多的情况下，很难保持数据的一致性！数据库结构也比较糟糕。而且不存，也不会使效率十分低下。

7.不要去直接修改数据库？个人认为这点很重要，当需要修改时，应该先去修改模型，然后同步物理数据库，尤其是团队开发，否则要多做更多的事情来搞定，也可能会引入更多的错误




mysql 索引拍脑袋设计
设计联系人表的时候，设想用uid和cid作联合索引，把uid放在前面，提高用uid查询的速度(clustered index)，结果在实际查询中发现:
1，建表的时候根本不能把`uid`放在key的前面，必须放在后面 PRIMARY key(｀cid`,`uid`)
如果这样的话，把Uid作为联合主键一点意义没有，最初的“为了查询而优化”的设计全是拍脑袋的决定！！

mysql> explain select * from contact where  uid=1234567890;
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | contact | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

做方案还是需要对每一个点进行测试，避免提前“思考”优化。



Java代码

   1. DROP TABLE IF  EXISTS `contact`;  
   2.   
   3. CREATE TABLE `contact` (  
   4.     `cid` bigint AUTO_INCREMENT NOT NULL,  
   5.   
   6.     `uid` bigint NOT NULL,  
   7.   
   8.     `email` varchar(12 NOT NULL,  
   9.   
  10.     `name` varchar(64) NOT NULL,  
  11.   
  12.     `mobile` varchar(16)  NULL,  
  13.   
  14.     `atime` timestamp NULL,  
  15.   
  16.     `type` enum('BLACK','WHITE','NORMAL') NOT NULl default 'NORMAL',  
  17.   
  18.     `info` text NULL,  
  19.   
  20.     `memo` varchar(1024)  NULL,  
  21.   
  22.      PRIMARY key(｀uid`,`cid`)  
  23.   
  24. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT = 100;  
  25.   
  26.   
  27.   
  28. ALTER TABLE `contact` ADD UNIQUE INDEX uniq_uid_email(`uid`,`email`);  

DROP TABLE IF  EXISTS `contact`;

CREATE TABLE `contact` (
    `cid` bigint AUTO_INCREMENT NOT NULL,

    `uid` bigint NOT NULL,

    `email` varchar(12 NOT NULL,

    `name` varchar(64) NOT NULL,

    `mobile` varchar(16)  NULL,

    `atime` timestamp NULL,

    `type` enum('BLACK','WHITE','NORMAL') NOT NULl default 'NORMAL',

    `info` text NULL,

    `memo` varchar(1024)  NULL,

     PRIMARY key(｀uid`,`cid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT = 100;



ALTER TABLE `contact` ADD UNIQUE INDEX uniq_uid_email(`uid`,`email`);




Java代码

   1. Clustered Index 是与物理数据混在一起并对物理数据进重排，就像使用拼音查字典；Unclustered Index 是与物理数据完全分离的，利用额外空间对关键字进行重排，就像使用部首查字典。   
   2.   
   3.   
   4. 1.直接创建索引和间接创建索引  
   5.   
   6. 直接创建索引： CREATE INDEX mycolumn_index ON mytable (myclumn)  
   7.   
   8. 间接创建索引：定义主键约束或者唯一性键约束，可以间接创建索引  
   9.   
  10. 2.普通索引和唯一性索引  
  11.   
  12. 普通索引：CREATE INDEX mycolumn_index ON mytable (myclumn)  
  13.   
  14. 唯一性索引：保证在索引列中的全部数据是唯一的，对聚簇索引和非聚簇索引都可以使用  
  15.   
  16. CREATE UNIQUE COUSTERED INDEX myclumn_cindex ON mytable(mycolumn)  
  17.   
  18. 3.单个索引和复合索引  
  19.   
  20. 单个索引：即非复合索引  
  21.   
  22. 复合索引：又叫组合索引，在索引建立语句中同时包含多个字段名，最多16个字段  
  23.   
  24. CREATE INDEX name_index ON username(firstname,lastname)  
  25.   
  26. 4.聚簇索引和非聚簇索引(聚集索引，群集索引)  
  27.   
  28. 聚簇索引：物理索引，与基表的物理顺序相同，数据值的顺序总是按照顺序排列  
  29.   
  30. CREATE CLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn) WITH  
  31.   
  32. ALLOW_DUP_ROW(允许有重复记录的聚簇索引)  
  33.   
  34. 非聚簇索引：CREATE UNCLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn  
  35.   
  36.   
  37. 索引的使用  
  38.   
  39. 1.当字段数据更新频率较低，查询使用频率较高并且存在大量重复值是建议使用聚簇索引  
  40. 2.经常同时存取多列，且每列都含有重复值可考虑建立组合索引  
  41. 3.复合索引的前导列一定好控制好，否则无法起到索引的效果。如果查询时前导列不在查询条件中则该复合索引不会被使用。前导列一定是使用最频繁的列  
  42. 4.多表操作在被实际执行前，查询优化器会  


关于每一个数据库表都应该有一个单一的字段作为主键的讨论
Computer System Add comments

2010年5月6日更新: 只有真正懂得了这个道理的人, 才算真正理解了关系数据库. 如何才算懂得了这个道理? – 即使你有一百个理由要用关联主键, 你也能找到这唯一的一个理由放弃, 改而使用单一字段做主键.

——

在数据库设计中, 每一个表都应该有一个字段作为主键. 这个字段一般是自增整数字段, 或者某些数据库支持的自动产生不重复字符串的字段, 也可以是程序自己产生的唯一标识. 总之, 每一个数据库表都应该有一个单一的字段作为主键.

使用单一的字段作为表的主键, 有许多优点. 最重要的一条是可以通过一个原子属性(如整数, 字符串)来标识一行记录. 一旦可以方便地标识一行记录, 那么数据的查询, 更新, 删除也都非常简单.

如果一个表没有主键, 那么必须通过一行记录本身才能标识一行记录. 也就是, 你必须知道一行记录的每一个字段的值, 才能在 SQL 语句中操作这条记录.

数据库表支持联合主键, 也就是使用表的至少2个字段作为主键. 使用联合主键的缺点和不使用主键的缺点有很大的相似性, 因为当联合主键所使用的字段越多, 联合主键的缺点就越来越趋近于不使用主键, 直到表的所有字段作为主键时, 和不使用主键几乎是完全相同的 – 除了前者不能存在重复行,

作为主键的字段一般使用自增整数字段, 因为绝大多数关系型数据库管理系统都支持这种字段类型, 而且速度快, 占用空间少. 一个害怕使用自增整数字段作为主键的理由是自增整数会回绕从而导致重复. 但这种担心是没有必要的. 对于最普通的32位无符号整数, 其最大值是在 Mysql 中是 4294967295(43亿) , 如果数据库表以每秒1条的速度保存记录, 那么一天也只能 86400 条(约10万条)记录, 需要 49710 天(约136年)之后才能发生重复. 幸运的是, 绝大多数系统产生数据的速度比这个假设慢得多了. 如果系统产生数据的速度确实足够快速, 则可以使用 64 位的长整型整数.

如果确实需要联合主键, 则可以使用 UNIQUE 索引来代替. 任何不得不使用联合主键的需求, 都是基于对关系数据库的错误理解而产生的. 并不是要用"单一主键"来覆盖"复合主键"的需求, 而是"在表设计中, 单一主键是必须的, 用联合唯一索引来覆盖’复合主键’的需求".

一个例子:

表1: 使用联合主键(只是作为反面例子出现, 它应该被联合唯一索引代替)

table_a{
    field_a: PK;
    field_b: PK;
    field_…: PK
    field_c;
}

表2: 不使用联合主键

table_b{
    id: PK;
    field_a: UNIQUE;
    field_b: UNIQUE;
    field_…: UNIQUE
    field_c;
}

* 联合主键导致数据操作复杂化

这两种表定义都能反映相同的业务模型. 使用联合主键对保存数据的程序代码影响不大, 情况1通过联合主键保证不会产生重复数据, 但情况2通过联合唯一索引保存不会产生重复数据. 但对查找操作有不良影响, 同时影响到使用了查找功能的更新和删除操作. 考虑查找特定几条记录的情况:

表1: 查找 (field_a, filed_b, …) = [(1, 2, ...), (1, 3, ...), (2, 4, ...), ...] 这几条记录.
表2: 查找 id = [1, 2, 3, ...] 这几条记录.

显然, 表2的描述更简洁, 从而使程序代码和 SQL 语句更简洁. 如果把所有的字段都作为主键, 那么表1就不存在查找特定几条记录的功能了, "因为已经知道, 所以不必再找". 这是一种最极端的联合主键的副作用, 联合主键的字段越多, 就越趋向于这种极端.

* 联合主键不利于表示"关系"

联合主键不利于表示"关系", 是因为它会造成不必要的冗余. 考虑有另外两个表, 需要分别关联 table_a 和 table_b:

table_ac{
    id;
    table_a_field_a;
    table_b_field_b;
    …
}

table_bc{
    id;
    table_b_id;
}

显然, 不使用联合主键可以减少冗余. 如果使用所有字段作为联合主键, 将变成把 table_a 的数据保存两份, 一份在 table_a, 另一份包含在 table_ac. 个人认为, 联合主键是是关系数据库理论一种概念, 在实际中如果使用是"反关系数据库模型"的, 实际使用应该用联合唯一索引替代.
http://www.ideawu.net/blog/archives/366.html