catalog

    • Locking rules
    • Equivalent query gap lock
    • Non-unique index equivalent lock
    • Primary key index range locking
    • Non-unique index range lock
    • Unique index range lock bug
    • Examples of “equivalence” exist on non-unique indexes
    • Limit statement locks
    • About the deadlock

conclusion

1. Only objects accessed during the query process will be locked, and the basic unit of locking is next-key lock (open before closed); 2, equivalence on MySQL query optimization: the index of equivalent query, if it is the only index, next to the key the lock will degenerate into row locks, if not the only index, need access to the first value does not meet the conditions, the next – key lock lock will degenerate into clearance; 3. Range query: No matter whether the index is unique or not, the range query needs to access the first value that does not meet the condition;

Locking rules

The default isolation level is repeatable.

Principle 1: The basic unit of locking is next-key lock. The interval of the lock is front open and back closed

Principle 2: Only objects accessed during lookup are locked

Optimization 1: equivalent query on index, next-key lock degenerates to row lock when unique index is locked

Optimization 2: the next-key lock degrades to a gap lock when the last value does not meet the equivalence condition

Bug: Range queries on unique indexes access to the first value that does not meet the criteria.

Construction sentences:

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL.PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0.0.0), (5.5.5),
(10.10.10), (15.15.15), (20.20.20), (25.25.25);
Copy the code

Equivalent query gap lock

Non-unique index equivalent lock

Session A assigns A read lock to the row c=5 on index C.

According to principle 1, the lock unit is next-key lock, so (0,5) is assigned a next-key lock.

C is a common index, so if c=5, you need to traverse right until c=10.

According to principle 2, everything accessed should be locked, so (5,10) should be next-key lock;

At the same time, it conforms to Optimization 2: equivalent judgment and traverses to the right. The last value does not satisfy C =5, so it degenerates into gap lock (5,10).

(The gap locks (0, 5) analyzed above still exist, and there are two gap locks (0, 5] and (5, 10) combined)

According to principle 2, only the accessed objects are locked. This query uses an overwrite index and does not need to access the primary key index, so there is no lock on the primary key index, so session B’s UPDATE statement can complete.

** only objects accessed by ** are locked. This “object” refers to columns, not rows. Add: lock, is added to the index. If there is an index, add it to the index; Column, no index, is added to the primary key; 支那

Session C attempts to insert (7,7,7) records, which will be locked by session A’s gap lock (5,10).

Lock in share mode locks all rows on the primary key index. For update, lock all rows on the primary key index.

Conclusion:

The lock is added to the index;

If you use lock in share mode to lock rows from being updated, you must bypass the optimization of overwriting the index and add columns that do not exist in the index to the query field.

Session A:

select id from t where c=5 lock in share mode; 
Copy the code

Modified to

select d from t where c = 5 lock in share mode;
Copy the code

If the primary key index is not accessed, the primary key index will not be locked if the primary key index is not accessed. If no overwrite index is used, and the current query is for update,update and delete are read, then the primary key index is accessed, and the primary key index is locked.

Primary key index range locking

For table T, there are two query statements with different lock ranges:

select * from t where id = 10 for update;
select * from t where id > = 10 and id < 11 for update;
Copy the code

These two statements are logically equivalent, but the locking rules are different.



Execution process:

Select next key lock (5,10) where id=10; According to optimization 1, the equivalent condition on the primary key ID degenerates into a row lock, and only the row lock with id = 10 is added

Select * from next where id=15; select * from next where id=15;

So, session A at this time lock range is primary key index, row lock id = 10 and next-key lock(10,15). Because it is a range query, not an equivalent query, it will not be optimized 2; So you have blocks of B and C.

Note that when session A locates and searches the row with ID =10 for the first time, it is judged as an equivalent query, while when session A scans to the right and finds the row with ID =15, it is judged as A range query.

- references:
1.Go to the primary key ID index and retrieve the ID=10(note that this is the equivalent query)2.Again from the id=10Start with that line, and keep going to the right and taking out each line until it doesn't have an ID greater than or equal to10That is less than11After this condition, then stop (note that this is the range query) according to the initial Creaetable/ insert valuesStatements (such as10The back is15), and then according to the locking rules (principles1Principle,2And optimize the1And optimize the2, bug5): Perform steps1Because it is an equivalent query, the primary key index is a unique index, according to the principle1Principle,2And optimize the1Finally, only row locks are added10; step2Because it is a range query, the primary key index is a unique index, according to the principle1Principle,2Bug5, and the first value that doesn't satisfy this condition is15, so eventually lock (10.15]; This piece is relatively cumbersomeCopy the code

Non-unique index range lock

Unique index range lock bug

Examples of “equivalence” exist on non-unique indexes

Insert a new record into table T

insert into t values(30.10.30);
Copy the code

Now I have two rows in my table where C is equal to 10. Since the primary key is unique, there are no two identical rows, and the index C in this case is:



Two c’s =10, but with different primary key ids (10 and 30, respectively), there is also a gap between the two c=10 records.

The delete statement is used to verify this. The DELETE principle is the same as the previous update principle.



Session A accesses the first record whose c=10 when iterating. According to principle 1: the addition here is

(c = 5,id = 5) to (c = 10,id = 10) the next-key lock.

Session A then looks right until it hits the line (c=15, ID =15) and the loop ends. According to optimization 2, this is an equivalent query. The right row is found that does not meet the condition, so it degenerates into a gap lock between (c=10,id=10) and (c=15,id=15).

In other words, the delete statement lock range on index C is as follows:



Note that both rows (c=5,id=5) and (c=15,id=15) are unlocked.

Limit statement locks

About the deadlock

Next-key lock is actually the result of a gap lock and a row lock combined.



Analysis process:

SQL > alter table c add next-key lock(5,10); SQL > alter table c add next-key lock(10,15);

Select * from session B where next-key lock(5,10) is set;

Session A then inserts the row (8,8,8), which is locked by session B’s gap lock. InnoDB rolls back session B due to deadlock.

We think the lock of session B has not been successfully applied.

Session B’s “add next-key lock(5,10)” operation is actually divided into two steps: first, add (5,10) gap lock. And then the row lock of c=10 is added, and the second step is locked.

That is to say, when we analyze the specific steps of locking, we need to divide it into two sections: gap locking and row locking.