How Can I Perform Non-Locking SELECT Queries in MySQL?

Non-Locking SELECT Queries in MySQL

In MySQL, frequent locking during SELECT operations can arise when tables are concurrently modified. To mitigate this issue, consider using techniques that allow for non-locking reads.

Using READ UNCOMMITTED

One approach is to set the transaction isolation level to READ UNCOMMITTED, which allows reads without acquiring locks. However, this option may not be suitable for slaves as it compromises data integrity.

The SQL Equivalent of WITH (NOLOCK)

For equivalence to the WITH (NOLOCK) clause in Microsoft SQL Server, MySQL offers a multi-step process:

1. Start the transaction with READ UNCOMMITTED isolation:

   SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

2. Perform the SELECT query:

   SELECT * FROM TABLE_NAME;

3. Reset the transaction isolation level back to REPEATABLE READ:

   SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Improved Non-Locking Query Execution

Michael Mior proposed an improved version of the non-locking query execution:

1. Set the transaction isolation level to READ UNCOMMITTED:

   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;

2. Perform the SELECT query:

   SELECT * FROM TABLE_NAME ;

3. Commit the transaction:

   COMMIT ;

The above is the detailed content of How Can I Perform Non-Locking SELECT Queries in MySQL?. For more information, please follow other related articles on the PHP Chinese website!