First, a wave of lock stew… I drew a map. I’ll play it later

1. The row locks

Lock one or more rows. InnoDB uses row-level locking by default and uses automatic locking mechanism.

  • For UPDATE, DELETE, and INSERT statements, InnoDB automatically locks all data sets involved.
  • InnoDB does not lock any normal SELECT statements, but can explicitly lock them:
    • Shared lock: lock in share more
    • Exclusive locks: for update
      • Row lock if there is matching data and index (primary key, unique) is used, otherwise table lock.
      • If no data is found, there is no lock; If the index is valid, it is a table lock

— Only for InnoDB storage engine and must be in transaction block

Advantages: Lock granularity is small and concurrency is strong.

Disadvantages: high overhead, slow table locking, deadlocks.

1) Record lock

A record lock locks a record in a table. The condition for a record lock must be that the index is accurately matched and the index is unique.

2) Gap lock

Also known as interval lock, each lock is to lock an interval. If a query condition matches an index and no record is found, the range data in the query condition is locked (even if the data does not exist in the range library).

Gap locks occur only at the repeatable read transaction isolation level, and not only on secondary indexes, where there are gaps, there may be gaps.

3) Key lock

The query condition matches the index, and a record is found in the database. Mysql row locks are used by default as temporary key locks, which are implemented by both record locks and gap locks.

2. The table lock

Lock an entire table. While the table is locked, other transactions cannot operate on the table until the lock on the current table is released.

Locking mode:

  • lock tables … Read /write, you can use unlock tables to actively release locks.
  • Metadata lock (MDL) does not need to be used explicitly, but is automatically added when accessing a table.

Advantages: small overhead, fast lock, no deadlock.

Disadvantages: Large granularity of locks, high probability of lock conflicts, and low concurrent processing capability.

3. Global lock

Locks the entire database instance.

Flush tables with read lock

4. Page locks

The overhead and locking time are between table locking and row locking. Deadlocks occur; The locking granularity is between table locking and row locking, and the concurrent processing capability is average.

5. Exclusive lock

Also known as write lock or exclusive lock, it blocks other write locks and read locks until the current write operation is complete.

6. A Shared lock

Also known as read lock, it is used to check whether data exists. Multiple read operations can be performed simultaneously without affecting each other. If a transaction modifies a read lock, a deadlock is likely to occur.

7. Optimistic locking

Assume that no concurrency problems will occur, so no locks will be created. However, during the update, it will determine whether other threads have modified the data before, and if so, the operation will fail and be rolled back.

Implementation method:

  • Version: Adds the Version field to the database table and changes the Version during the update. After the operation, check whether the Version has been changed.
  • Compare and Swap (CAS) algorithm: When multiple threads try to use CAS to update a variable at the same time, only one thread can update the value of the variable, but all other threads fail. The failed thread is not suspended, but is informed that it failed in the competition and can try again.

8. Pessimistic locking

Every time we fetch data, we assume that some other thread will modify it, so we lock it. Once a lock is placed, only one thread can execute it while different threads are executing at the same time, and the other threads wait at the entrance until the lock is released.