mysql死锁排查

来源：互联网发布：linux如何安装hadoop 编辑：程序博客网时间：2024/05/30 05:28

死锁

show engine innodb status;显示引擎参数,查死锁sql
show engine innodb mutex;

LATEST DETECTED DEADLOCK------------------------2017-01-21 23:00:32 2b4ad8705700*** (1) TRANSACTION:TRANSACTION 74247262250, ACTIVE 0 sec starting index readmysql tables in use 2, locked 2LOCK WAIT 8 lock struct(s), heap size 1184, 7 row lock(s)MySQL thread id 4808396, OS thread handle 0x2b4a72923700, query id 77966678624 10.150.135.191 cbu_cms Sending dataupdate   pool_offer t1, pool_offer_batch t2 set t1.score=t2.score where   t1.pool_id=t2.pool_id and t1.offer_id=t2.offer_id and   t2.batch_id='ichoose-221556405-1484989224443'*** (1) HOLDS THE LOCK(S):RECORD LOCKS space id 566 page no 107635 n bits 464 index `ind_pidoid` of table `cbu_cms`.`pool_offer` trx id 74247262250 lock_mode X locks rec but not gapRecord lock, heap no 220 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 0000000000004acf; asc       J ;; 1: len 8; hex 0000000039f5e41c; asc     9   ;; 2: len 8; hex 0000000003f74e7c; asc       N|;;*** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 566 page no 107635 n bits 464 index `ind_pidoid` of table `cbu_cms`.`pool_offer` trx id 74247262250 lock_mode X locks rec but not gap waitingRecord lock, heap no 337 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 0000000000004acf; asc       J ;; 1: len 8; hex 000000003d236748; asc     =#gH;; 2: len 8; hex 0000000003fabe1d; asc         ;;*** (2) TRANSACTION:TRANSACTION 74247262209, ACTIVE 0 sec inserting, thread declared inside InnoDB 4685mysql tables in use 2, locked 145 lock struct(s), heap size 6544, 159 row lock(s), undo log entries 80MySQL thread id 4808449, OS thread handle 0x2b4ad8705700, query id 77966678347 11.136.33.169 cbu_cms Creating sort indexinsert into pool_offer   (enter_way,gmt_create,gmt_modified,pool_id,offer_id,member_id,category_id,category1_id,category2_id,category3_id,bu_id,expired_time,offerstatus,sort,gmt_sort,status,ds_tag,location)   select 'dsimport' as   enter_way,gmt_create,gmt_modified,pool_id,offer_id,member_id,category_id,category1_id,category2_id,category3_id,bu_id,expired_time,offerstatus,sort,gmt_modified,'NORMAL'   as status,ds_tag,location from pool_offer_batch   where batch_id='import-offer-25767666-1484989291643' and   pool_id=19151 order by id   on duplicate key   update   gmt_modified=now(),gmt_sort=values(gmt_modified),sort=values(sort),   expired_time=values(expired_time),offerstatus=values(offerstatus),ds_tag=values(ds_tag),   location = values(location),   category_id = values(category_id),   category1_id = values(category1_id),   category2_id = values(category2_id),   category3_id = values(cate*** (2) HOLDS THE LOCK(S):RECORD LOCKS space id 566 page no 107635 n bits 464 index `ind_pidoid` of table `cbu_cms`.`pool_offer` trx id 74247262209 lock_mode XRecord lock, heap no 30 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 0000000000004acf; asc       J ;; 1: len 8; hex 0000000a39f8a858; asc     9  X;; 2: len 8; hex 0000000004086d36; asc       m6;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 566 page no 107635 n bits 464 index `ind_pidoid` of table `cbu_cms`.`pool_offer` trx id 74247262209 lock_mode X waitingRecord lock, heap no 220 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 0000000000004acf; asc       J ;; 1: len 8; hex 0000000039f5e41c; asc     9   ;; 2: len 8; hex 0000000003f74e7c; asc       N|;;

表结构

| pool_offer | CREATE TABLE `pool_offer` (  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '??',  `gmt_create` datetime NOT NULL COMMENT '????',  `gmt_modified` datetime NOT NULL COMMENT '????',  `pool_id` bigint(20) unsigned DEFAULT NULL COMMENT '??ID',  `offer_id` bigint(20) unsigned DEFAULT NULL COMMENT '??ID',  `member_id` varchar(128) DEFAULT NULL COMMENT '??id',  `status` varchar(64) DEFAULT NULL COMMENT '??',  `del_operator` varchar(64) DEFAULT NULL COMMENT '?????',  `creator` varchar(64) DEFAULT NULL COMMENT '?????',  `modifier` varchar(64) DEFAULT NULL COMMENT '?????',  `gmt_deleted` date DEFAULT NULL COMMENT '????',  `enter_way` varchar(64) DEFAULT NULL COMMENT '??????',  `sort` bigint(20) DEFAULT NULL COMMENT '??',  `gmt_sort` datetime DEFAULT NULL COMMENT '????',  `category_id` varchar(128) DEFAULT NULL COMMENT '????id',  `category1_id` varchar(128) DEFAULT NULL COMMENT '????ID',  `category2_id` varchar(128) DEFAULT NULL COMMENT '????ID',  `category3_id` varchar(128) DEFAULT NULL COMMENT '????ID',  `bu_id` varchar(128) DEFAULT NULL COMMENT 'BU ID',  `expired_time` datetime DEFAULT NULL COMMENT '????',  `offerstatus` varchar(30) DEFAULT 'published' COMMENT 'offer??(PUBLISHED???????offerdubbo??)',  `cheat_flag` int(11) DEFAULT '0' COMMENT '????, 0???5????9???',  `score` bigint(20) DEFAULT '0' COMMENT '?????????????????????*1000000??',  `view_sort` bigint(20) DEFAULT NULL COMMENT 'offer??????',  `conf_sort` bigint(20) DEFAULT NULL COMMENT '???????',  `ds_tag` varchar(256) DEFAULT NULL COMMENT 'offer??????tag',  `enroll_id` bigint(20) unsigned DEFAULT NULL COMMENT '??ID',  `location` varchar(20000) DEFAULT NULL COMMENT '??????????????',  `hpstatus` varchar(32) DEFAULT NULL COMMENT 'offer?????',  PRIMARY KEY (`id`),  UNIQUE KEY `ind_pidoid` (`pool_id`,`offer_id`),  KEY `ind_memberid` (`member_id`),  KEY `ind_gmtcreate` (`gmt_create`,`gmt_modified`),  KEY `ind_gmtsort` (`gmt_sort`),  KEY `idx_pool_offer_cat` (`pool_id`,`category_id`,`category1_id`,`category2_id`,`category3_id`,`member_id`,`offerstatus`),  KEY `ind_offerid_status` (`offer_id`,`status`),  KEY `ind_offer_pool_status` (`offer_id`,`pool_id`,`status`),  KEY `ind_pidstatus` (`pool_id`,`status`,`ds_tag`(255)),  KEY `ind_sort` (`pool_id`,`status`,`offerstatus`,`view_sort`,`score`)) ENGINE=InnoDB AUTO_INCREMENT=67735804 DEFAULT CHARSET=utf8 COMMENT='???offer??' |

锁超时

报错
java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
select * from information_schema.innodb_trx\G 找到当前运行的所有事物
select * from information_schema.processlist where id =xxx 在processlist里查看这个id的线程
kill掉占用锁的线程
例子

记得以前，当出现：ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction，要解决是一件麻烦的事情；特别是当一个SQL执行完了，但未COMMIT，后面的SQL想要执行就是被锁，超时结束，DBA光从数据库无法着手找出源头是哪个SQL锁住了；有时候看看 show engine innodb status，并结合 show full processlist 能暂时解决问题，但一直不能精确定位。

在5.5中，information_schema 库中增加了三个关于锁的表（MEMORY引擎）：
- innodb_trx ## 当前运行的所有事务
- innodb_locks ## 当前出现的锁
- innodb_lock_waits ## 锁等待的对应关系
  看到这个就非常激动，这可是解决了一个大麻烦，先来看一下表结构：

root@127.0.0.1 : information_schema 13:28:38> desc innodb_locks;+————-+———————+——+—–+———+——-+| Field       | Type                | Null | Key | Default | Extra |+————-+———————+——+—–+———+——-+| lock_id     | varchar(81)         | NO   |     |         |       |#锁ID| lock_trx_id | varchar(18)         | NO   |     |         |       |#拥有锁的事务ID| lock_mode   | varchar(32)         | NO   |     |         |       |#锁模式| lock_type   | varchar(32)         | NO   |     |         |       |#锁类型| lock_table  | varchar(1024)       | NO   |     |         |       |#被锁的表| lock_index  | varchar(1024)       | YES  |     | NULL    |       |#被锁的索引| lock_space  | bigint(21) unsigned | YES  |     | NULL    |       |#被锁的表空间号| lock_page   | bigint(21) unsigned | YES  |     | NULL    |       |#被锁的页号| lock_rec    | bigint(21) unsigned | YES  |     | NULL    |       |#被锁的记录号| lock_data   | varchar(8192)       | YES  |     | NULL    |       |#被锁的数据+————-+———————+——+—–+———+——-+10 rows in set (0.00 sec)root@127.0.0.1 : information_schema 13:28:56> desc innodb_lock_waits;+——————-+————-+——+—–+———+——-+| Field             | Type        | Null | Key | Default | Extra |+——————-+————-+——+—–+———+——-+| requesting_trx_id | varchar(18) | NO   |     |         |       |#请求锁的事务ID| requested_lock_id | varchar(81) | NO   |     |         |       |#请求锁的锁ID| blocking_trx_id   | varchar(18) | NO   |     |         |       |#当前拥有锁的事务ID| blocking_lock_id  | varchar(81) | NO   |     |         |       |#当前拥有锁的锁ID+——————-+————-+——+—–+———+——-+4 rows in set (0.00 sec)root@127.0.0.1 : information_schema 13:29:05> desc innodb_trx ;+—————————-+———————+——+—–+———————+——-+| Field                      | Type                | Null | Key | Default             | Extra |+—————————-+———————+——+—–+———————+——-+| trx_id                     | varchar(18)         | NO   |     |                     |       |#事务ID| trx_state                  | varchar(13)         | NO   |     |                     |       |#事务状态：| trx_started                | datetime            | NO   |     | 0000-00-00 00:00:00 |       |#事务开始时间；| trx_requested_lock_id      | varchar(81)         | YES  |     | NULL                |       |#innodb_locks.lock_id| trx_wait_started           | datetime            | YES  |     | NULL                |       |#事务开始等待的时间| trx_weight                 | bigint(21) unsigned | NO   |     | 0                   |       |#| trx_mysql_thread_id        | bigint(21) unsigned | NO   |     | 0                   |       |#事务线程ID| trx_query                  | varchar(1024)       | YES  |     | NULL                |       |#具体SQL语句| trx_operation_state        | varchar(64)         | YES  |     | NULL                |       |#事务当前操作状态| trx_tables_in_use          | bigint(21) unsigned | NO   |     | 0                   |       |#事务中有多少个表被使用| trx_tables_locked          | bigint(21) unsigned | NO   |     | 0                   |       |#事务拥有多少个锁| trx_lock_structs           | bigint(21) unsigned | NO   |     | 0                   |       |#| trx_lock_memory_bytes      | bigint(21) unsigned | NO   |     | 0                   |       |#事务锁住的内存大小（B）| trx_rows_locked            | bigint(21) unsigned | NO   |     | 0                   |       |#事务锁住的行数| trx_rows_modified          | bigint(21) unsigned | NO   |     | 0                   |       |#事务更改的行数| trx_concurrency_tickets    | bigint(21) unsigned | NO   |     | 0                   |       |#事务并发票数| trx_isolation_level        | varchar(16)         | NO   |     |                     |       |#事务隔离级别| trx_unique_checks          | int(1)              | NO   |     | 0                   |       |#是否唯一性检查| trx_foreign_key_checks     | int(1)              | NO   |     | 0                   |       |#是否外键检查| trx_last_foreign_key_error | varchar(256)        | YES  |     | NULL                |       |#最后的外键错误| trx_adaptive_hash_latched  | int(1)              | NO   |     | 0                   |       |#| trx_adaptive_hash_timeout  | bigint(21) unsigned | NO   |     | 0                   |       |#+—————————-+———————+——+—–+———————+——-+22 rows in set (0.01 sec)

下面我们来动手看看数据吧：

建立测试数据：

use test;create table tx1(id int primary key ,c1 varchar(20),c2 varchar(30))engine=innodb default charset = utf8 ;insert into tx1 values(1,’aaaa’,'aaaaa2′),(2,’bbbb’,'bbbbb2′),(3,’cccc’,'ccccc2′);commit;

产生事务

### Session1start transaction;update tx1 set c1=’heyf’,c2=’heyf’ where id = 3;

产生事务，在 innodb_trx 就有数据

root@127.0.0.1 : information_schema 13:38:21> select * from innodb_trx \G*************************** 1. row ***************************                    trx_id: 3669D82                 trx_state: RUNNING               trx_started: 2010-12-24 13:38:06     trx_requested_lock_id: NULL          trx_wait_started: NULL                trx_weight: 3       trx_mysql_thread_id: 2344                 trx_query: NULL       trx_operation_state: NULL         trx_tables_in_use: 0         trx_tables_locked: 0          trx_lock_structs: 2     trx_lock_memory_bytes: 376           trx_rows_locked: 1         trx_rows_modified: 1   trx_concurrency_tickets: 0       trx_isolation_level: REPEATABLE READ         trx_unique_checks: 1    trx_foreign_key_checks: 1trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 100001 row in set (0.00 sec)### 由于没有产生锁等待，下面两个表没有数据root@127.0.0.1 : information_schema 13:38:31> select * from innodb_lock_waits \GEmpty set (0.00 sec)root@127.0.0.1 : information_schema 13:38:57> select * from innodb_locks \GEmpty set (0.00 sec)

产生锁等待

#### session 2start transaction;update tx1 set c1=’heyfffff’,c2=’heyffffff’ where id =3 ;root@127.0.0.1 : information_schema 13:39:01> select * from innodb_trx \G*************************** 1. row ***************************                    trx_id: 3669D83   ##第2个事务                 trx_state: LOCK WAIT   ## 处于等待状态               trx_started: 2010-12-24 13:40:07     trx_requested_lock_id: 3669D83:49:3:4  ##请求的锁ID          trx_wait_started: 2010-12-24 13:40:07                trx_weight: 2       trx_mysql_thread_id: 2346       ##线程 ID                 trx_query: update tx1 set c1=’heyfffff’,c2=’heyffffff’ where id =3       trx_operation_state: starting index read         trx_tables_in_use: 1      ##需要用到1个表         trx_tables_locked: 1      ##有1个表被锁          trx_lock_structs: 2     trx_lock_memory_bytes: 376           trx_rows_locked: 1         trx_rows_modified: 0   trx_concurrency_tickets: 0       trx_isolation_level: REPEATABLE READ         trx_unique_checks: 1    trx_foreign_key_checks: 1trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 10000*************************** 2. row ***************************                    trx_id: 3669D82 ##第1个事务                 trx_state: RUNNING               trx_started: 2010-12-24 13:38:06     trx_requested_lock_id: NULL          trx_wait_started: NULL                trx_weight: 3       trx_mysql_thread_id: 2344                 trx_query: NULL       trx_operation_state: NULL         trx_tables_in_use: 0         trx_tables_locked: 0          trx_lock_structs: 2     trx_lock_memory_bytes: 376           trx_rows_locked: 1         trx_rows_modified: 1   trx_concurrency_tickets: 0       trx_isolation_level: REPEATABLE READ         trx_unique_checks: 1    trx_foreign_key_checks: 1trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 100002 rows in set (0.00 sec)root@127.0.0.1 : information_schema 13:40:12> select * from innodb_locks \G*************************** 1. row ***************************    lock_id: 3669D83:49:3:4      ## 第2个事务需要的锁lock_trx_id: 3669D83  lock_mode: X  lock_type: RECORD lock_table: `test`.`tx1` lock_index: `PRIMARY` lock_space: 49  lock_page: 3   lock_rec: 4  lock_data: 3*************************** 2. row ***************************    lock_id: 3669D82:49:3:4     ## 第1个事务需要的锁lock_trx_id: 3669D82  lock_mode: X  lock_type: RECORD lock_table: `test`.`tx1` lock_index: `PRIMARY` lock_space: 49  lock_page: 3   lock_rec: 4  lock_data: 32 rows in set (0.00 sec)root@127.0.0.1 : information_schema 13:40:15> select * from innodb_lock_waits \G*************************** 1. row ***************************requesting_trx_id: 3669D83         ## 请求锁的事务requested_lock_id: 3669D83:49:3:4  ## 请求锁的锁ID  blocking_trx_id: 3669D82         ## 拥有锁的事务 blocking_lock_id: 3669D82:49:3:4  ## 拥有锁的锁ID1 row in set (0.00 sec)

参考:
MySQL 加锁处理分析
一个最不可思议的MySQL死锁分析

阅读全文

0 0