Concurrent INSERT deadlock validation

The database

MySQL Community Server (GPL)

Isolation level: REPEATable Read

Table structure:

desc track_lock;

+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| id          | varchar(100) | NO   | PRI | NULL    |       |
| status      | int(2)       | NO   |     | NULL    |       |
| create_date | timestamp    | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
Copy the code

Validation scenario 1: Three transactions simultaneously insert records with the same primary key. Transaction 1 committed normally, transaction 2,3 failed to insert, no deadlock problem.

Validation scenario 2: Three transactions simultaneously insert records with the same primary key. Transaction 1 is inserted and rolled back, transaction 2 and 3 have one insert success and one deadlock error.

Conclusion:

1. A high number of concurrent inserts may cause a deadlock. The same record is inserted under the primary key index, the insert record of transaction 1 has not been committed, and transaction 2 and 3 are also inserted. When transaction 1 rolls back the transaction, transaction 2 and 3 have one insert success and one deadlock error.
2. During the design of the service system, try to avoid high concurrent insertion of the same record services. You can perform filtering before the database. Such as Java locks, distributed locks.

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.

2. 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.

3. 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. 6. Transaction 2 and transaction 3 obtain S lock at the same time, and re-check the uniqueness constraint. 2. Transaction 2 and transaction 3 acquire the S lock at the same time, and then 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.

conclusion

A high number of concurrent inserts create the possibility of deadlock. The same record is inserted under the primary key index, transaction 1 has not committed the insert record, and transaction 2,3 also inserts the operation. After transaction 1 rolls back the transaction, transaction 2,3 has a deadlock error and the S lock is released, so that the other transaction can obtain the lock successfully.