When i study on a deadlock caused by two update query. there is some point, I cannot understand. 1. the order of row lock setting? 2. the order of lock setting on Primary and Secondary Index, when update execute? 3. SHOW INNODB STATUS show WAITING x lock structs, is it required at the same time or one is required after other granted? 4. if one lock struct want lock some rows, is the process atomic?
here is my deadlock: Table:
CREATE TABLE `study_update_deadlock` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`u_key` bigint(20) DEFAULT NULL,
`nu_key` bigint(20) DEFAULT NULL,
`version` int(11) DEFAULT NULL,
`quantity` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `nu_key` (`nu_key`),
KEY `u_key` (`u_key`)
) ENGINE=InnoDB AUTO_INCREMENT=5000001 DEFAULT CHARSET=latin1 COMMENT='根据非唯一主键更新是发生死锁'
Update Query 1:
update study_update_deadlock set version=version+1 where u_key=1663577608119220637 and nu_key=12498159
Update Query 2:
update study_update_deadlock set quantity=quantity+1 where u_key=1470344318505049187 and nu_key=12498159
Some Row In DB ExampleRows
Key Of the scenario:
- Update According the nu_key index
- two query update different row but two row have same nu_key index
- TRX1 LOCK WAIT 5 lock struct(s)? is all lock require added to lock graph
- TRX2 HOLDS PrimaryKey, And wait lock on nu_key index. but according what i know index lock set first, is the lock require is not atomic?
- Isolation Level:RR
DeadLock SHOW INNODB STATUS
2016-06-29 18:58:32 700001f3b000
*** (1) TRANSACTION:
TRANSACTION 76027, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 5 lock struct(s), heap size 1184, 8 row lock(s)
MySQL thread id 33311, OS thread handle 0x700001ab7000, query id 204129 localhost root updating
update study_update_deadlock set quantity = quantity+1 where u_key= 1470344318505049187 and nu_key=12498159
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 16 page no 22255 n bits 392 index `PRIMARY` of table `test`.`study_update_deadlock` trx id 76027 lock_mode X locks rec but not gap waiting
Record lock, heap no 255 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 8036731e; asc 6s ;;
1: len 6; hex 0000000128fa; asc ( ;;
2: len 7; hex 770000179e081e; asc w ;;
3: len 8; hex 97163705443d799d; asc 7 D=y ;;
4: len 8; hex 8000000000beb4ef; asc ;;
5: len 4; hex 800000bb; asc ;;
6: len 4; hex 8000005a; asc Z;;
*** (2) TRANSACTION:
TRANSACTION 76028, ACTIVE 0 sec starting index read
mysql tables in use 3, locked 3
4 lock struct(s), heap size 1184, 3 row lock(s)
MySQL thread id 33312, OS thread handle 0x700001f3b000, query id 204130 localhost root updating
update study_update_deadlock set version = version+1 where u_key= 1663577608119220637 and nu_key=12498159
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 16 page no 22255 n bits 392 index `PRIMARY` of table `test`.`study_update_deadlock` trx id 76028 lock_mode X locks rec but not gap
Record lock, heap no 255 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 8036731e; asc 6s ;;
1: len 6; hex 0000000128fa; asc ( ;;
2: len 7; hex 770000179e081e; asc w ;;
3: len 8; hex 97163705443d799d; asc 7 D=y ;;
4: len 8; hex 8000000000beb4ef; asc ;;
5: len 4; hex 800000bb; asc ;;
6: len 4; hex 8000005a; asc Z;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 16 page no 22336 n bits 952 index `nu_key` of table `test`.`study_update_deadlock` trx id 76028 lock_mode X waiting
Record lock, heap no 660 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8000000000beb4ef; asc ;;
1: len 4; hex 8036731c; asc 6s ;;
*** WE ROLL BACK TRANSACTION (2)
