How to implement read submitted in mysql

To implement the read committed isolation level in MySQL, you can set the REPEATABLE READ or READ COMMITTED level. REPEATABLE READ ensures that the data read will not be modified during the transaction, while READ COMMITTED ensures that the data read will not be modified by the committed transaction. MySQL implements this isolation level using MVCC, row locks, and lock promotion mechanisms to reduce lock contention and improve performance. To set the isolation level, use the following command: SET TRANSACTION ISOLATION LEVEL [REPEATABLE READ | READ COMMIT

MySQL Read Committed Implementation

The read committed isolation level is a database isolation level that ensures that the content read by a transaction will not be modified by other running transactions. In MySQL, read committed can be achieved by setting the REPEATABLE READ or READ COMMITTED isolation level.

REPEATABLE READ

REPEATABLE READ The isolation level ensures that data read by a transaction during its operation will not be modified by other transactions. In other words, after a transaction starts reading data, the data will not change until it is committed. This isolation level provides higher data consistency, but can also cause lock contention and performance issues.

READ COMMITTED

READ COMMITTED The isolation level ensures that data read by a transaction will not be modified by other committed transactions. In other words, the data read by the transaction may have been modified before other transactions have committed. This isolation level provides lower lock contention and better performance, but can also cause non-repeatable read issues.

Implementation details

MySQL implements the read committed isolation level through the following mechanism:

• Multi-version Concurrency Control (MVCC) : MVCC allows transactions to read different versions of the database, thereby reducing lock contention. Each transaction has its own local snapshot that contains the latest version of every row in the database.
• Row lock: MySQL uses row locks to prevent two transactions from modifying the same row at the same time. When a transaction reads a row, it acquires a shared lock. If a transaction modifies a row, it acquires an exclusive lock.
• Lock upgrade: When the shared lock holder attempts to modify a row, the lock will be upgraded to an exclusive lock. This prevents other transactions from reading modifications that have been committed by other transactions.

Usage example

Set the read committed isolation level in MySQL:

<code class="sql">SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;</code>

or

<code class="sql">SET TRANSACTION ISOLATION LEVEL READ COMMITTED;</code>

Select isolation The exact manner in which levels are determined depends on the specific needs and performance requirements of the application.

The above is the detailed content of How to implement read submitted in mysql. For more information, please follow other related articles on the PHP Chinese website!