spark 操作数据库发生死锁 MySQLTransactionRollbackException: Deadlock found when trying to get lock;

程程程程 发布于 2017/04/10 14:13
阅读 796
收藏 0

在spark运行作业,多个线程批量对数据库进行插入或更新,使用的是,

jdbcTemplate.batchUpdate

但是数据操作的过程中有时会发生死锁。代码中只有一条SQL语句,

insert into status_code_analyse(count_time,type,uri,status_code,count,response_time) values(?,?,?,?,?,?) on duplicate key update `count`=count+?,`response_time`= response_time+?

通过show engine innodb status查看死锁的内容如下:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-04-07 06:06:05 7f06cb3ff700
*** (1) TRANSACTION:
TRANSACTION 24749851, ACTIVE 0.002 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1184, 6 row lock(s), undo log entries 5
LOCK BLOCKING MySQL thread id: 906508281 block 889751652
MySQL thread id 889751652, OS thread handle 0x7f06f43be700, query id 1007386525 192.168.1.92 log_analyse_user update
insert into status_code_analyse(count_time,type,uri,status_code,count,response_time) values('2017-04-07 06:06','2','v2/LimitTimeOffer/getInfo','200','2','0.65') on duplicate key update `count`=count+'2',`response_time`= response_time+'0.65'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 63 page no 200 n bits 336 index `time_uri_status_code` of table `log_analyse`.`status_code_analyse` trx id 24749851 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 244 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 58e6bbc8; asc X   ;;
 1: len 30; hex 76322f4f726465722f6765744f726465725368697070696e674465746169; asc v2/Order/getOrderShippingDetai; (total 31 bytes);
 2: len 3; hex 323030; asc 200;;
 3: len 8; hex 8000000000a0263f; asc       &?;;

*** (2) TRANSACTION:
TRANSACTION 24749850, ACTIVE 0.003 sec inserting
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1184, 5 row lock(s), undo log entries 6
MySQL thread id 906508281, OS thread handle 0x7f06cb3ff700, query id 1007386526 192.168.1.89 log_analyse_user update
insert into status_code_analyse(count_time,type,uri,status_code,count,response_time) values('2017-04-07 06:06','2','v5/Account/login','200','1','0.486') on duplicate key update `count`=count+'1',`response_time`= response_time+'0.486'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 63 page no 200 n bits 328 index `time_uri_status_code` of table `log_analyse`.`status_code_analyse` trx id 24749850 lock_mode X
Record lock, heap no 40 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 58e6bb8c; asc X   ;;
 1: len 15; hex 76322f536f612f6a53657276696365; asc v2/Soa/jService;;
 2: len 3; hex 323030; asc 200;;
 3: len 8; hex 8000000000a01813; asc         ;;

Record lock, heap no 244 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 58e6bbc8; asc X   ;;
 1: len 30; hex 76322f4f726465722f6765744f726465725368697070696e674465746169; asc v2/Order/getOrderShippingDetai; (total 31 bytes);
 2: len 3; hex 323030; asc 200;;
 3: len 8; hex 8000000000a0263f; asc       &?;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 63 page no 200 n bits 336 index `time_uri_status_code` of table `log_analyse`.`status_code_analyse` trx id 24749850 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 262 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 58e6bbc8; asc X   ;;
 1: len 13; hex 76352f436972636c652f746970; asc v5/Circle/tip;;
 2: len 3; hex 323030; asc 200;;
 3: len 8; hex 8000000000a02656; asc       &V;;

*** WE ROLL BACK TRANSACTION (1)

请问这种情况应该从数据库方面还是从业务代码方面解决,业务代码只有如上的一条SQL,并调用jdbcTemplate批量操作

加载中
返回顶部
顶部