SoFunction
Updated on 2025-04-12

Implementation of Implicit Lock principle of MySQL

1. Background: What is an implicit lock?

MySQL's InnoDB storage engine supports multiple types of locks, mainly including explicit locks (such as shared locks, exclusive locks) and implicit locks. Implicit locks are locks that are automatically managed by InnoDB. Transactions do not require explicit requests when handling certain DML operations, and they are implicitly applied to specific records. Implicit locks usually occur at line level operations (such asINSERT、UPDATEorDELETE) and accompanied by automatic locking behavior of tables, it is used to ensure concurrency of data.

Compared to explicit locks (locks explicitly declared and controlled by users or SQL commands), implicit lock management is handled automatically internally by the MySQL engine and does not require application developers to manually lock or unlock.

2. How implicit locks work

Implicit locks are closely related to the life cycle of a transaction. Specifically, implicit locks are usually used for the following operations:

  • When a transaction modifies a row of data, InnoDB implicitly adds an exclusive lock (X lock) to the row to prevent concurrent modification or reading of other transactions on the row.
  • When a new row is inserted, the transaction implicitly locks on the inserted row to prevent other transactions from reading uncommitted data concurrently.

The life cycle of these locks is usually related to the start and commit operations of a transaction:

  • Implicit locks are retrieved at transaction startup and released when transaction commits or rollbacks.
  • Implicit locks are not recorded in the InnoDB lock table, that is, they will not be displayed in the followingSHOW ENGINE INNODB STATUSetc., this makes them different from explicit locks.

3. Types of implicit locks

Implicit locks mainly include the following types:

  • Exclusive Lock, X lock: When a transaction performs modifications to a certain line (such asUPDATE、DELETE) This line is implicitly added to the exclusive lock to prevent other transactions from modifying or reading the line at the same time.
  • Insert Intention Lock: When a transaction performs an insert operation, InnoDB implicitly locks to prevent other transactions from inserting rows in the same position concurrently.

4. Implementation and source code analysis of implicit locks

The implementation of implicit locks in MySQL InnoDB is closely connected to the transaction management and lock management modules, and the relevant codes are mainly distributed in(Transaction Management),(Lock Management) file.

4.1 The process of obtaining implicit locks

Execute in transactionINSERT、UPDATE、DELETEWhen operating, InnoDB will automatically lock the involved lines in the background. This process passeslock_rec_lock()Implemented by a function, which is the core of row-level locks.

function:lock_rec_lock()

This function is used to lock a specific row. It receives parameters such as lock type, data block, etc., and determines whether the lock needs to be added, and which lock is added. ForUPDATEorDELETEOperation, an exclusive lock (implicit X lock) will usually be automatically added.

bool lock_rec_lock(
    ulint type,                // Lock type, such as X lock    dict_index_t* index,       // row corresponding index    const buf_block_t* block,  // The data block where the line is located    ulint heap_no,             // The position of the row in the index    trx_t* trx                 // Current transaction) {
    // Locking logic, determine whether an implicit exclusive lock is needed    if (type == LOCK_X) {
        // Add exclusive lock (implicit lock)        lock_rec_add_to_queue(type, block, heap_no, trx);
    }
    // Return the lock result    return true;
}

4.2 Implicit locks in insertion operation

ForINSERTIn operation, InnoDB uses an Insert Intention Lock. An insertion intention lock is a special implicit lock that allows multiple transactions to insert data concurrently, as long as they are inserted at different locations. This lock does not conflict with the row lock, because it does it before determining the insertion position.

function:lock_clust_rec_create()

When transaction executionINSERTDuring operation, MySQL will calllock_clust_rec_create()Function, the task of this function is to generate an insertion intention lock for newly inserted rows on the index.

bool lock_clust_rec_create(
    dict_index_t* index,       // Clustered index    const buf_block_t* block,  // Data block    ulint heap_no,             // The position of the row    trx_t* trx                 // Current transaction) {
    // Logic of inserting intention lock    // Determine the position of the inserted row in the clustered index    lock_rec_add_to_queue(LOCK_IX, block, heap_no, trx);
    return true;
}

When performing insertion, if two transactions attempt to insert data at the same location, a conflict of insertion intent locks will occur, causing one of the transactions to be blocked until the lock is released.

4.3 Lock queue and conflict detection

existlock_rec_add_to_queue()In the function, InnoDB will add the lock request to the lock queue and check whether it conflicts with the current lock holder.

function:lock_rec_add_to_queue()

This function is one of the core lock request handling functions, which will add locks on a given row and perform lock collision detection. If there is a conflicting lock on the current line, the transaction will be blocked until the conflicting lock is released.

bool lock_rec_add_to_queue(
    ulint type,                // Lock type (such as implicit X lock)    const buf_block_t* block,  // The data block corresponding to the row    ulint heap_no,             // The position of the row    trx_t* trx                 // Current transaction) {
    // Add the lock to the lock queue and check for conflicts    if (lock_is_conflicting(type, block, heap_no, trx)) {
        trx->wait_for_lock();  // If there is a conflict, the current transaction enters the waiting queue        return false;
    }

    // Lock is successfully added, the lock queue is updated    add_lock_to_queue(type, block, heap_no, trx);
    return true;
}

Through lock queues and conflict detection mechanisms, InnoDB ensures the correctness and consistency of multiple transactions when accessing the same row.

5. The life cycle of implicit locks

The life cycle of an implicit lock is closely related to the life cycle of a transaction:

  • Implicit lock acquisition: InnoDB automatically locks when the transaction starts to modify data.
  • Implicit lock holding: The implicit lock remains valid during transaction holding until the transaction is committed or rolled back.
  • Release of implicit lock: When a transaction is committed, the implicit lock will be automatically released, allowing other transactions to access previously locked lines.

The automatic management mechanism of implicit locks ensures transaction isolation and data consistency without causing additional operational complexity to the user.

6. The difference between implicit lock and explicit lock

  • An explicit lock is a lock that is explicitly declared by the user through a SQL statement, such asLOCK TABLESorSELECT ... FOR UPDATE。
  • Implicit locks are automatically added by InnoDB when performing certain operations, so users do not need to care about the specific locking process. It exists to ensure the security of transaction concurrent operations.

Explicit locks are more suitable for scenarios where locks need to be managed manually, while implicit locks are suitable for regular row-level data operations.

7. Sample Scenario

Consider the following scenarios to better understand the operation of implicit locks:

Scenario 1: Row update (UPDATE)

  • Transaction A executionUPDATE users SET name = 'Alice' WHERE id = 1。
  • Transaction A will implicitly matchid=1Add the row to the exclusive lock (X lock) until transaction A commits or rolls back.
  • During this period, other transactions cannot be modified or read.id=1 The way。

Scene 2: Line Insert (INSERT)

  • Transaction B ExecutionINSERT INTO users (id, name) VALUES (2, 'Bob')。
  • Transaction B implicitly adds an insertion intent lock to the newly inserted row.
  • If transaction C tries to insert rows in the same position, a lock conflict occurs and transaction C is blocked.

8. Summary

MySQL's implicit lock is a lock automatically managed by the InnoDB engine, which is used to ensure data consistency and security of transactions when modifying rows. Its main features and working principles include:

  • Automatic management: The locking and release of implicit locks is automatically done by InnoDB without user intervention.
  • Row-level lock: Implicit locks are mainly used for row-level operations, such asUPDATE、DELETEandINSERT。
  • Lock conflict detection: InnoDB uses lock queue and conflict detection mechanisms internally to ensure that multiple transactions do not have data inconsistency when operations are concurrently performed.

In the underlying implementation, the management of implicit locks is closely related to the transaction system, and the acquisition and conflict detection of locks are mainly throughlock_rec_lock()、lock_clust_rec_create()Equal function implementation. Implicit locks are retrieved at the beginning of the transaction and released at commit or rollback.

This is the end of this article about the implementation of the Implicit Lock principle of MySQL. For more related content of implicit locks, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!