Deadlock problem – caused by insert and update

The phenomenon of

An error was reported in the service log. The Deadlock was found when trying to get lock. The error occurs when the UPDATE operation is performed. This is obviously caused by two transactions holding locks for each other and waiting for locks. The database isolation level is RR. But which situation is causing it requires careful analysis. Let’s look at the business logic.

background

The logic of this is a access database lock, a lock in the database tables, multiple tasks with the same ID through the lock insert records in the table to implement distributed lock, there is no record, the first insert the success of acquiring a lock, if there is no insertion is successful, further update the record, will fail status updates ChengJinXing, updated said lock.

create table lock(
	id varchar(20) primary key not null,
	status varchar(1) not null.-- 1: In progress, 2: successful, 3: failed
	times timestamp not null,);Copy the code

Analysis of the

The business logic

The business logic is very simple, in a transaction, do the insert first, if not successful then update, look at the code

@Transactional(rollbackFor = Exception.class)
public boolean tryLock(String key){
	if (tryLockInsert(key)) {
		return true;
	} else {
	  return tryLockUpdate(key)
	}
}
Copy the code
-- insert: returns a value >0 indicating success
insert into lock (id, status, times) values('key'.'1', sysdate);

-- update: Returns a value >0 indicating success
update lock set status = "1" where id = 'key' and status = '3'
Copy the code

Analysis of the 1

Two writes are performed in one transaction, and the modified record is determined by the primary key. It is likely that one transaction will acquire the X lock on the primary key index, and then perform two writes. Other transactions wait until the X lock is released after the first transaction has written twice, which should not be deadlocked.

Analysis of 2

SHOW ENGINE INNODB STATUS\G

------------------------ LATEST DETECTED DEADLOCK ------------------------ 2020-11-10 05:41:02 0x7f872c170700 *** (1) TRANSACTION: TRANSACTION 12399, ACTIVE 0 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 182, OS thread handle 140218145097472, Query ID 3535 172.28.0.1 root updating track_lock SET status=1, Create_date ='2020-11-10 13:41:02.095' WHERE (id =' test') *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 137 page no 3 n bits 96 index PRIMARY of table `pvuv`.`track_lock` trx id 12399 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 4; hex 74657374; asc test;; 1: len 6; hex 000000003066; asc 0f;; 2: len 7; hex 56000001a50a15; asc V ;; 3: SQL NULL; 4: SQL NULL; 5: len 5; hex 99a7d4da42; asc B;; 6: len 1; hex 31; asc 1;; *** (2) TRANSACTION: TRANSACTION 12400, ACTIVE 0 sec starting index read mysql tables in use 1, locked 1 3 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 175, OS thread handle 140218537019136, Query ID 3543 172.28.0.1 root updating track_lock SET status=1, SQL > LOCK(S); SQL > LOCK(S); SQL > LOCK(S); RECORD LOCKS space id 137 page no 3 n bits 96 index PRIMARY of table `pvuv`.`track_lock` trx id 12400 lock mode S locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 4; hex 74657374; asc test;; 1: len 6; hex 000000003066; asc 0f;; 2: len 7; hex 56000001a50a15; asc V ;; 3: SQL NULL; 4: SQL NULL; 5: len 5; hex 99a7d4da42; asc B;; 6: len 1; hex 31; asc 1;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 137 page no 3 n bits 96 index PRIMARY of table `pvuv`.`track_lock` trx id 12400 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 4; hex 74657374; asc test;; 1: len 6; hex 000000003066; asc 0f;; 2: len 7; hex 56000001a50a15; asc V ;; 3: SQL NULL; 4: SQL NULL; 5: len 5; hex 99a7d4da42; asc B;; 6: len 1; hex 31; asc 1;; *** WE ROLL BACK TRANSACTION (2)Copy the code

From the logs:

  1. Transaction TRX ID 12399 LOCK_mode X locks REc but not gap waiting. (1) WAITING FOR THIS LOCK TO BE GRANTED
  2. Transaction TRX ID 12400 LOCK Mode S locks REc but not gap. (2) HOLDS THE LOCK(S)
  3. Transaction TRX ID 12400 LOCK_mode X locks REc but not gap waiting. (2) WAITING FOR THIS LOCK TO BE GRANTED:

Two transactions, transaction 2 holds the shared lock and waits to acquire the exclusive lock, and transaction 1 waits to acquire the exclusive lock.

Analysis 3 Insert lock mechanism

Mysql > select * from user where shared lock exists; However, I still don’t understand the specific lock step description of insert operation. Insert into locking mechanism

Insert lock 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 insertion 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.If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock.

Insert imposes an exclusive lock on the row that is successfully inserted. This exclusive lock is a record lock, not a next-key lock (and of course not a GAP lock), and does not prevent other concurrent transactions from inserting records before the row. Before insertion, an insertion intention gap lock (I lock) is added to the gap where the insertion record is located. Concurrent transactions can apply I lock to the same gap. If the insert transaction has a duplicate-key error, the transaction locks the duplicate index record. This shared lock can cause a deadlock in concurrent cases, such as when two concurrent INSERT pairs are locked on the same record, and the record is locked exclusively by another transaction. After the exclusive lock is committed or rolled back, two concurrent INSERT operations can cause a deadlock.

Insert statement lock procedure

Implicit locks are mainly used in insert scenarios. During the execution of an Insert statement, you must check for two cases: if a gap lock is placed between records, records cannot be inserted to avoid phantom reads, and if an Insert record has a unique key conflict with an existing record, records cannot be inserted. Otherwise, insert statements are implicitly locked, but the tracking code finds that the lock_rec_ADD_to_queue function is not called to lock inserts, which is implicitly locked during the insert.

Implicit locks are converted to display locks only in special cases. This conversion is not done spontaneously by the thread with the implicit lock, but by other threads with conflicting row data. For example, if transaction 1 inserts a record but does not commit it, then transaction 2 tries to lock the record, then transaction 2 must first judge whether the transaction ID saved on the record is active, if active, it will help transaction 1 to establish a lock object, and transaction 2 itself enters the state of waiting for transaction 1

And the INSERT lock process

  1. Insert Intension Locks first.
  • If the GAP has been added GAP lock or next-key lock, the lock fails to enter the wait;
  • If not, the lock is successful, indicating that the disk can be inserted.
  1. It then determines whether the inserted record has a unique key, and if so, performs a uniqueness constraint check
  • If the same key value does not exist, the insert is completed
  • If the same key value exists, it determines whether the key value has a lock
    • If there is no lock, determine whether the record is marked for deletion
      • If marked as delete, the transaction has been committed and has not yet been purge, waiting for the row S lock to be rechecked for uniqueness constraints.
      • If no label is deleted, a 1062 duplicate key error is reported.
        • If the record has active transactions, add an X record lock to the active transaction. The current transaction waits for the row S lock and re-checks for uniqueness constraints.
        • If no transaction is active, the current transaction returns a unique key conflict error.
    • If there is a lock, the record is being processed (added, deleted, or updated) and the transaction has not yet committed. Wait for row S to lock and re-check for uniqueness constraints.
  1. Insert record and add X record lock to record;

To summarize, insert the same record in the primary key field with three specific transactions to simulate deadlock.

  1. Transaction 1,2,3 find the corresponding gap add insert intention gap lock I. Concurrent transactions can place an I lock on the same gap. To prevent phantoms, do not INSERT if there is a GAP or next-key lock between records.
  2. Transaction 1 was inserted successfully. No transaction has been committed.
  3. Duplicate key error occurs when the same record is inserted in transaction 2, and an exclusive lock X is added to transaction 1’s duplicate index record. Transaction 2 waits for the shared lock S
  4. Transaction 3 inserts the same record, finds X lock, waits for shared lock S
  5. If transaction 1 commits, the X lock is released.
    1. Transaction 2 and transaction 3 obtain S lock at the same time, and re-check the uniqueness constraint.
    2. Transaction 2 and transaction 3 both acquire the S lock and discover that the unique conflict is over. No deadlocks.
  6. If transaction 1 rolls back, the X lock is released.
    1. Transaction 2 and transaction 3 obtain S lock at the same time, and re-check the uniqueness constraint.
    2. Transaction 2, transaction 3 finds that records can be inserted and obtains the X lock first.
    3. Transaction 2 and transaction 3 each hold the S lock and simultaneously acquire the X lock, resulting in a deadlock.
    4. Transaction 2, transaction 3 had one deadlock error reported and the lock was released, and the other was successfully inserted.

A deadlock occurs when at least two insert transactions perform simultaneous inserts on the record before a transaction insert completes.

Think: Insert Intention Locks

Insert Intention Locks were introduced, I understand, to improve the concurrency of data inserts. If no Insert Intention Locks are available, Gap Locks may be used instead.

Analysis 4: Insert Update deadlock process

Insert update: Transaction 1 is waiting for X lock, transaction 2 is holding S lock, waiting for X lock

  1. Transaction 0 performs insert and the X lock has not committed the transaction
  2. Transaction 1 performs duplicate-key error after insert and waits for S lock
  3. Transaction 2 returns a duplicate-key error after insert and waits for S lock
  4. Zero transaction commit.
  5. Transaction 1 and transaction 2 both acquire the S lock,
  6. Transaction 1, transaction 2 perform update
  7. Transaction 1 and transaction 2 obtain the X lock of the same record and wait for the other party to release the S lock.
  8. Transaction 1, transaction 2: One finds a deadlock, releases the lock, and the other obtains the X lock and performs an update.

To solve

  1. Reduce the RR isolation level to the RC isolation level. Here the RC isolation level is read by snapshot so that the S lock is not applied.
  2. Insert select * for update with X lock.
  3. You can put distributed locks in advance, you can use Redis, or ZK, or whatever,
  4. Split the INSERT and UPDATE into two transactions depending on the business. Service correctness is not affected.

The first approach is not practical because the isolation level cannot be easily changed. The third method is more troublesome. So the second way is easier.

Solutions 1, 2, and 3 above are based on the memory of a magic Mysql deadlock check above, and the fourth addition is that I find another solution according to the business situation.

conclusion

  1. Avoid high-concurrency write operations on the same record.
    1. It is easy to deadlock the database unique index feature as distributed lock
    2. The feature of database unique index realizes interface idempotent, easy to deadlock.
  2. Avoid multiple changes to a record in a transaction, which can increase the complexity of locking.