SoFunction
Updated on 2025-04-26

Detailed explanation of MySQL transaction and lock mechanism and precautions

Detailed explanation of MySQL transaction and lock mechanism

In relational databases, transaction and lock mechanisms are two key technologies to ensure data consistency and concurrency control. This article will introduce the basic concepts of transactions, ACID characteristics, transaction isolation level, and lock mechanism in MySQL in detail, helping developers better design and optimize database operations in practical applications.

1. Transaction basis

1.1 What is a transaction?

Transaction refers to a set of indivisible database operation units. This set of operations is either executed successfully or rolled back. Transactions ensure the atomicity of data operations and avoid partial success and partial failure.

1.2 ACID Features

Transactions have four basic characteristics, namely the famous ACID principle:

  • Atomicity: All operations within a transaction are treated as a whole, and operations are either successful or all failed to roll back.
  • Consistency: Before and after the transaction begins, the database must remain in a consistent state, that is, all business rules and constraints are met.
  • Isolation: Concurrently executed transactions are independent of each other, and the intermediate state of one transaction should not be seen by other transactions.
  • Durability: Once a transaction is committed, the result should be permanently saved and will not be lost even if the system fails.

2. Transactions in MySQL

2.1 Transaction-supported storage engine

  • InnoDBIt is MySQL's default transactional storage engine, fully supporting ACID features.
  • MyISAMIt does not support transactions and is only suitable for read-intensive application scenarios.

2.2 Turn on and manage transactions

In MySQL, transactions can be controlled through the following SQL statements:

  • START TRANSACTIONorBEGIN: Start a transaction.
  • COMMIT: Submit transactions and persist all operations to the database.
  • ROLLBACK: Roll back the transaction, undo all operations, and restore to the state before the transaction starts.

Example:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
-- Check balance、Execute other logic
COMMIT;

3. Transaction isolation level

In order to prevent dirty reading, non-repeatable reading and phantom reading in a concurrent environment, the database provides different transaction isolation levels. MySQL (InnoDB) supports the following four isolation levels:

READ UNCOMMITTED (read not submitted)
Minimum isolation level, allowing uncommitted data to be read, dirty reads will occur.

READ COMMITTED (read submitted)
Reading only the submitted data can avoid dirty reading, but may not be repeated.

REPEATABLE READ (repeatable)
Ensure that the results of multiple reads within the same transaction are consistent, effectively preventing non-repeatable readings. MySQL InnoDB uses this level by default.
Notice: Phantom reading may still occur under REPEATABLE READ. InnoDB solves the phantom reading problem through Next-Key Locking technology.

SERIALIZABLE (serializable)
The highest isolation level, serializes all transactions and performs them in serialized manner, with a high performance overhead and is usually only used under special requirements.

Isolation level setting example:

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

4. Detailed explanation of the lock mechanism

The locking mechanism is an important means used by database management systems to manage concurrent operations, which ensures that data conflicts will not occur when multiple transactions access data concurrently. MySQL mainly adopts two types of lock mechanisms: row-level lock and table-level lock.

4.1 Row-level lock

  • Features: Small granularity, high concurrency performance, suitable for a large number of concurrent write operations.
  • Implementation method：
    • Record lock: Lock specific data rows to prevent other transactions from modifying or reading data from that row.
    • Gap lock: Lock the gap between records to prevent other transactions from inserting new data, and solve the problem of phantom reading.
    • Next-Key Locking: The combination of record lock and gap lock, used to prevent phantom reading, is the key mechanism for InnoDB to implement reproducible reading.

4.2 Table-level lock

  • Features: Large granularity, locking the entire table, suitable for large-scale reading or writing scenarios.
  • Pros and cons：
    • advantage: Simple implementation, small overhead, suitable for scenarios where more reads and fewer writes (such as MyISAM).
    • shortcoming: Low concurrency performance may lead to lock contention.

4.3 Intention lock

  • definition: Intention lock is a table-level lock flag that indicates that the transaction wants to add a lock on certain rows. With intent locks, the database can quickly determine whether there is a conflict before executing a row-level lock.
  • effect: Improve the efficiency and judgment speed of lock management, and ensure that row-level locks and table-level locks can work in a coordinated manner.

4.4 Deadlock and prevention

  • Deadlock: A deadlock occurs when two or more transactions are waiting for each other to release the lock. When InnoDB detects a deadlock, it will automatically roll back one of the transactions to unblock the deadlock.
  • Preventive measures： Keep a consistent lock application order: In multi-table operations, try to add locks in the same order.
    • Reduce the time when transactions hold locks: Execute transaction operations as soon as possible and submit or roll back in time.
    • Select isolation level reasonably: On the premise of meeting business needs, choose a lower isolation level to reduce lock competition.

5. Things to note in practical application

Reasonable design of transaction scope
Try to minimize the operation range of transactions and reduce the use of lock resources by long transactions.

Optimize SQL statements
Optimize query and update statements to ensure that the index is used properly and avoid full table scanning to cause large locks.

Monitor lock status
useSHOW ENGINE INNODB STATUSCommands to monitor the current lock status and deadlock information, and timely adjust application policies.

Regular review and test
Stress testing of critical business logic to ensure that transactions and lock mechanisms work properly in high concurrency environments and avoid performance bottlenecks.

6. Summary

MySQL's transaction and lock mechanisms together form the core of database concurrency control. By following ACID principles and reasonably setting transaction isolation levels, data consistency and integrity can be effectively guaranteed. At the same time, understanding and applying mechanisms such as row-level locks, table-level locks and intention locks is crucial for developers to optimize performance in high concurrency scenarios. Through continuous monitoring and optimization, the stability and response speed of the database system can be maximized. Hopefully this article provides you with valuable reference when designing and tuning your MySQL application!

This is the end of this article about the detailed explanation of MySQL transaction and lock mechanism. For more related contents of MySQL transaction and lock mechanism, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!