在业务数据新增或者修改的时候,需要同步的维护 business 的数据,这时候正确的做法是diff新旧数据,得到需要删除的一部分数据,需要新增的一部分数据以及需要更新的一部分数据,这种实现有点麻烦(其实也不麻烦,使用Guava的集合操作),因此工程师们的通常做法是先根据c_id删除现有数据,再插入新数据。这个时候很容易出现死锁。
Primary key value(s) of the locked record if LOCK_TYPE='RECORD', otherwise NULL.
This column contains the value(s) of the primary key column(s) in the locked row, formatted as a valid SQL string (ready to be copied to SQL commands).
If there is no primary key then the InnoDB internal unique row ID number is used.
If a gap lock is taken for key values or ranges above the largest value in the index, LOCK_DATA reports “supremum pseudo-record”.
When the page containing the locked record is not in the buffer pool (in the case that it was paged out to disk while the lock was held), InnoDB does not fetch the page from disk, to avoid unnecessary disk operations. Instead, LOCK_DATA is set to NULL.
mysql> show engine innodb status \G
...
------------------------
LATEST DETECTED DEADLOCK
------------------------
2016-07-21 19:11:05 7f6b90de8700
*** (1) TRANSACTION:
TRANSACTION 203797, ACTIVE 42 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 9, OS thread handle 0x7f6b90db7700, query id 144 localhost root update
insert into business (c_id, business_id) values (6, 1)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 20 page no 4 n bits 80 index `uniq_idx_c_id_business_id` of table `test`.`business` trx id 203797 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) TRANSACTION:
TRANSACTION 203798, ACTIVE 38 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 10, OS thread handle 0x7f6b90de8700, query id 147 localhost root update
insert into business (c_id, business_id) values (7, 1)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 20 page no 4 n bits 80 index `uniq_idx_c_id_business_id` of table `test`.`business` trx id 203798 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 20 page no 4 n bits 80 index `uniq_idx_c_id_business_id` of table `test`.`business` trx id 203798 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
//删除,会在满足条件的记录上加一个next-key锁,也就是锁住之前的Gap和待删除的记录。
DELETE FROM ... WHERE ... sets an exclusive next-key lock on every record the search encounters.
//显然,如果删除的数据比现有最大数据max还大,就会锁(max, +∞)这个Gap
//同理,如果删除的数据比现有最小数据min还小,就会锁(-∞, min)这个Gap
An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion.
This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.
Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6, respectively, each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.
INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.
Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.
下面这个是 precise mode 的兼容矩阵:(这个兼容矩阵发生在X与X,S与X不兼容的情况下再进行比对的)
G I R N (已经存在的锁,包括等待的锁)
G + + + +
I - + + -
R + + - -
N + + - -
+ 代表兼容, -代表不兼容. I代表插入意图锁,
G代表Gap锁,I代表插入意图锁,R代表记录锁,N代表Next-Key锁.
Gap locks in InnoDB are “purely inhibitive”, which means they only stop other transactions from inserting to the gap.
They do not prevent different transactions from taking gap locks on the same gap. Thus, a gap X-lock has the same effect as a gap S-lock.
---TRANSACTION 204350, ACTIVE 24 sec
4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
MySQL thread id 22, OS thread handle 0x7fd7ee4f0700, query id 245 localhost root cleaning up
---TRANSACTION 204349, ACTIVE 31 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1184, 4 row lock(s), undo log entries 2
MySQL thread id 20, OS thread handle 0x7fd7ee4bf700, query id 250 localhost root update
insert into business (c_id, business_id) values (3, 1)
------- TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 20 page no 4 n bits 80 index `uniq_idx_c_id_business_id` of table `test`.`business` trx id 204349 lock mode S waiting
Record lock, heap no 12 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
0: len 4; hex 80000004; asc ;;
1: len 1; hex 01; asc ;;
2: len 4; hex 0000001e; asc ;;