preface

Please correct any mistakes you find

Mysql has some locks

  • There are four types of locks in mysql:

    1. X locks (exclusive locks) that can only be owned by one transaction
    2. S locks (shared locks) that can be owned by multiple transactions at the same time
    3. IX lock (intended exclusive lock), table level lock, IX lock before X row lock, IX lock does not conflict (tell other transactions that want X table lock, IX lock already exists)
    4. IS lock (intentional shared lock), table level lock, IS lock before S row lock, IS lock does not conflict (tell other transactions that want S table lock, IS lock already exists)

  • For ease of understanding, we can think of the above locks as two types:

    1. Read locks (S locks, IS locks). Multiple transactions can read locks on specified data at the same time. A read lock on a row means that no other transaction can write locks on it, but it can still be queried by a simple SELECT
    2. Write locks (X locks, IX locks). Only one transaction can write locks on specified data at a time. A write lock on a row means that no other transaction can write locks or read locks on it, but it can still be queried by a simple SELECT

  • In most cases, we refer to “write lock”. There are three types of write lock in mysql:

    1. Record Lock (or LOCK_REC_NOT_GAP), a row Lock that locks only one row and is used against rows that match a query at the Read COMMITTED isolation level
    2. A Gap Lock (or LOCK_GAP) is used by repeatable read to Lock the interval between the upper and lower parts of a row but not the row itself
    3. Next-key Locks (or LOCK_ORDINARY) are used by repeatable read for next-key Locks (or LOCK_ORDINARY)

When can a lock be triggered?

  • Explicit trigger
    1. performselect ... for updateTrigger a write lock
    2. performselect ... in share modeTrigger read lock
  • Implicit trigger
    1. performupdate .../insert .../delete ...All trigger write locks

How to write a deadlock

Deadlock can occur in many ways, but this article only describes one situation that is most likely to be encountered in mysql: repeatable Read isolation level, update/delete/insert multiple times on the same table in the same transaction

In this isolation level, interval locks and temporary locks take effect. According to the mysql official document, when an update/ DELETE/INSERT statement uses a unique or primary key index on a row, only the release lock is touched. When an ordinary index is encountered, the temporary lock is triggered.

Suppose a table is structured as follows, where user_id is a normal index and ID is an autoincrement primary key:

To demonstrate write lock:

  1. The first stage
  • A transaction is A:
begin; delete from table where user_id = 10; -- In theory the user_id interval (-∞, 10) and (10, 20) are lockedCopy the code
  • Transaction B:
begin; insert into table(user_id) values (13); Attempt to insert data into the locked interval of transaction A while transaction B blocks because transaction A has not committedCopy the code
  1. The second stage
  • A transaction
commit; -- Transaction A commits, transaction B unblocks, and data is successfully insertedCopy the code

Demo deadlock:

Assume that the table structure and data are the same as above:

  1. The first stage
  • A transaction is A:
begin; delete from table where user_id = 10; -- The user_id interval (-∞, 10) and (10, 20) are locked by transaction ACopy the code
  • Transaction B:
begin; delete from table where user_id = 30; -- The user_id interval [20, 30) and [30, +∞) are locked by transaction BCopy the code
  1. The second stage
  • A transaction is A:
insert into table(user_id) values (24); Transaction A tries to insert data, but the interval is already locked by transaction B, so it blocks waiting for transaction B to release the lockCopy the code
  • Transaction B:
insert into table(user_id) values (13); Transaction B tries to insert data, but the interval is already locked by transaction A, so it blocks waiting for transaction A to release the lockCopy the code
  • Result: Transaction A and transaction B wait for each other to release the lock, resulting in A deadlock

conclusion

Be careful when writing multiple writes to the same table in the same transaction. Deadlocks can occur in high concurrency situations. How to avoid deadlocks if possible:

  • Large transactions break small transactions, and large transactions are more prone to deadlocks
  • If possible, lock all required resources at once in a transaction to reduce the probability of deadlock
  • Lower the isolation level (if you can accept dirty phantom reads)
  • Add a reasonable index