mysql 一个死锁的分析

╰+哭是因爲堅強的太久メ 2022-07-13 09:24 207阅读 0赞

死锁信息如下：

\*\*\* (1) TRANSACTION:

TRANSACTION 4363766192, ACTIVE 0 sec

mysql tables in use 2, locked 2

LOCK WAIT 9 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 6

MySQL thread id 8822753, OS thread handle 0x7fca3025b700, query id 2302320886 \*.\*.\*.\* cashcoupon\_oper Sending data

update keap\_cash\_coup\_type a,(select sum(freezed\_amount) freezedAmount,cash\_coupon\_type\_id from keap\_cash\_transcation where transcation\_id = 10000001415322882 group by cash\_coupon\_type\_id)b set a.amount = a.amount-b.freezedAmount,a.locked\_amount=a.locked\_amount+b.freezedAmount where a.cash\_coupon\_type\_id=b.cash\_coupon\_type\_id

\*\*\* (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 2280 page no 3 n bits 176 index \`PRIMARY\` of table \`keap\_ticket\_cash\`.\`keap\_cash\_transcation\` trx id 4363766192 lock mode S locks rec but not gap waiting

\*\*\* (2) TRANSACTION:

TRANSACTION 4363766191, ACTIVE 0 sec fetching rows, thread declared inside InnoDB 4999

mysql tables in use 2, locked 2

9 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 6

MySQL thread id 8822751, OS thread handle 0x7fc8718a1700, query id 2302320895 \*.\*.\*.\* cashcoupon\_oper Sending data

update keap\_cash\_coup\_type a,(select sum(freezed\_amount) freezedAmount,cash\_coupon\_type\_id from keap\_cash\_transcation where transcation\_id = 10000001415322879 group by cash\_coupon\_type\_id)b set a.amount = a.amount-b.freezedAmount,a.locked\_amount=a.locked\_amount+b.freezedAmount where a.cash\_coupon\_type\_id=b.cash\_coupon\_type\_id

\*\*\* (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 2280 page no 3 n bits 176 index \`PRIMARY\` of table \`keap\_ticket\_cash\`.\`keap\_cash\_transcation\` trx id 4363766191 lock\_mode X locks rec but not gap

\*\*\* (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 2280 page no 3 n bits 176 index \`PRIMARY\` of table \`keap\_ticket\_cash\`.\`keap\_cash\_transcation\` trx id 4363766191 lock mode S locks rec but not gap waiting

Record lock, heap no 103 PHYSICAL RECORD: n\_fields 12; compact format; info bits 0

信息显示两个多表链接update的事务，事务一在等待表keap\_cash\_transcation表主键索引的S锁，位置在第3页的176字节处，事务二拿到了对应位置的锁，而又在等待该位置S锁，这种锁等待看起来有点奇怪，明明已经拿到该位置的X锁为什么还要去获取S锁，都知道mysql在对唯一索引做update和insert时是会先获取S锁再获取X锁，这感觉有点像，一步一步排查分析吧

首先查询隔离级别好判断加锁粒度：

mysql> show global variables like "%iso%";

\+---------------+----------------+

| Variable\_name | Value          |

\+---------------+----------------+

| tx\_isolation  | READ-COMMITTED |

\+---------------+----------------+

1 row in set (0.00 sec)

是RC提交读隔离级别，知道了无gap锁，只有针对行加锁的情况，再仔细看看两个事务的sql发现等待锁的表keap\_cash\_transcation只是作为关联条件并未更新字段，查看表结构都只有主键，transcation\_id无索引，建两个只有主键的零时表进行测试：

结构：

CREATE TABLE \`t1\`/\`t2\` (

\`id\` int(11) DEFAULT NULL,

\`name\` varchar(10) DEFAULT NULL,

\`id\_1\` int(11) NOT NULL AUTO\_INCREMENT,

PRIMARY KEY (\`id\_1\`)

) ENGINE=InnoDB AUTO\_INCREMENT=7

测试：

<table style="padding:0px; margin:0px auto 10px; font-size:12px; border-collapse:collapse; color:rgb(44,44,44); font-family:宋体,&quot;Arial Narrow&quot;,arial,serif"> 
 <tbody style="padding:0px; margin:0px"> 
 <tr style="padding:0px; margin:0px"> 
 <td style="padding:5px 10px; margin:0px; border:1px solid rgb(221,221,221)"> session 1:</td> 
 <td style="padding:5px 10px; margin:0px; border:1px solid rgb(221,221,221)"> session 2:</td> 
 </tr> 
 <tr style="padding:0px; margin:0px"> 
 <td style="padding:5px 10px; margin:0px; border:1px solid rgb(221,221,221)"> select * from t1 for update;</td> 
 <td style="padding:5px 10px; margin:0px; border:1px solid rgb(221,221,221)"> </td> 
 </tr> 
 <tr style="padding:0px; margin:0px"> 
 <td style="padding:5px 10px; margin:0px; border:1px solid rgb(221,221,221)"> </td> 
 <td style="padding:5px 10px; margin:0px; border:1px solid rgb(221,221,221)"> update t2 join t1 on t1.id=t2.id set t2.age=10; 发生等待 </td> 
 </tr> 
 <tr style="padding:0px; margin:0px"> 
 <td style="padding:5px 10px; margin:0px; border:1px solid rgb(221,221,221)"> </td> 
 <td style="padding:5px 10px; margin:0px; border:1px solid rgb(221,221,221)"> mysql&gt; select a.lock_trx_id,b.trx_mysql_thread_id,b.trx_query,a.lock_mode,a.lock_type from INNODB_LOCKs a join INNODB_trx b on a.lock_trx_id=b.trx_id\G; *************************** 1. row *************************** lock_trx_id: 18944085 trx_mysql_thread_id: 33762 &nbsp;&nbsp;trx_query: update t2 join t1 on t1.id=t2.id set t2.age=10 &nbsp;&nbsp;lock_mode: S &nbsp;&nbsp;lock_type: RECORD &nbsp;&nbsp;lock_page: 3 &nbsp;lock_table: `test`.`t1` *************************** 2. row *************************** lock_trx_id: 18944084 trx_mysql_thread_id: 33761 &nbsp;&nbsp;trx_query: NULL &nbsp;&nbsp;lock_mode: X &nbsp;&nbsp;lock_type: RECORD &nbsp;&nbsp;lock_page: 3 &nbsp;lock_table: `test`.`t1` 2 rows in set (0.00 sec) </td> 
 </tr> 
 </tbody> 
</table>

看出事务二的update在获取t1表的S锁，但是这条语句只对t1表做查询匹配操作，两个事务执行的语句调个顺序看看结果

<table style="padding:0px; margin:0px auto 10px; font-size:12px; border-collapse:collapse; color:rgb(44,44,44); font-family:宋体,&quot;Arial Narrow&quot;,arial,serif"> 
 <tbody style="padding:0px; margin:0px"> 
 <tr style="padding:0px; margin:0px"> 
 <td style="padding:5px 10px; margin:0px; border:1px solid rgb(221,221,221)"> session 1</td> 
 <td style="padding:5px 10px; margin:0px; border:1px solid rgb(221,221,221)"> session 2</td> 
 </tr> 
 <tr style="padding:0px; margin:0px"> 
 <td style="padding:5px 10px; margin:0px; border:1px solid rgb(221,221,221)"> update t2 join t1 on t1.id=t2.id set t2.age=10;</td> 
 <td style="padding:5px 10px; margin:0px; border:1px solid rgb(221,221,221)"> </td> 
 </tr> 
 <tr style="padding:0px; margin:0px"> 
 <td style="padding:5px 10px; margin:0px; border:1px solid rgb(221,221,221)"> </td> 
 <td style="padding:5px 10px; margin:0px; border:1px solid rgb(221,221,221)"> select * from t1 for update; 发生等待 </td> 
 </tr> 
 <tr style="padding:0px; margin:0px"> 
 <td style="padding:5px 10px; margin:0px; border:1px solid rgb(221,221,221)"> </td> 
 <td style="padding:5px 10px; margin:0px; border:1px solid rgb(221,221,221)"> *************************** 1. row *************************** lock_trx_id: 18944086 trx_mysql_thread_id: 33761 &nbsp;&nbsp;trx_query: select * from t1 for update &nbsp;&nbsp;lock_mode: X &nbsp;&nbsp;lock_type: RECORD &nbsp;&nbsp;lock_page: 3 &nbsp;lock_table: `test`.`t1` *************************** 2. row *************************** lock_trx_id: 18944085 trx_mysql_thread_id: 33762 &nbsp;&nbsp;trx_query: NULL &nbsp;&nbsp;lock_mode: S &nbsp;&nbsp;lock_type: RECORD &nbsp;&nbsp;lock_page: 3 &nbsp;lock_table: `test`.`t1` 2 rows in set (0.01 sec) </td> 
 </tr> 
 </tbody> 
</table>

事务二这时是在获取X锁，注意死锁显示都在获取同一个位置的锁，并且update拿到有X锁，事务一的语句显然是首先从t1表获取S锁，再获取X锁，最终获取S锁

加上索引再进行测试：

总结：

通过上面的测试基本已经清楚mysql在关联update时，只是作为关联查询的表如果没有对应索引会对满足条件的行数据会进行加锁操作，在t1表进行数据查询时满足id=4条件的所有数据都会加S锁，和t2表关联对数据进行判断并做更新时对应的行会请求X锁，当对数据更新完成后会释放X锁并请求S锁，整个流程为 S-> X->S,如果未给t1表指明条件又以它作为驱动表的话就会造成t1表的记录都会加锁，在对条件字段id加了索引过后t1表不会产生阻塞，在生产环境中有这种关联更新的语句需要注意索引的问题。