How to Run SELECT Statements in MySQL Without Locking?

How to Execute SELECT Statements Without Causing Locks in MySQL

In MySQL, executing SELECT statements can sometimes trigger locks, particularly when the underlying table is undergoing concurrent modifications. This can be problematic, especially on slave databases that rely on binlogging.

One potential solution, suggested in the provided article, is utilizing the "NOLOCK" modifier. However, MySQL does not support an equivalent keyword. Instead, you can achieve the same effect by adjusting the transaction isolation level.

Solution: Read Uncommitted Transaction Isolation Level

To avoid locks while executing SELECT statements, you can set the transaction isolation level to "READ UNCOMMITTED." This allows uncommitted transactions to be visible, preventing locks from occurring. Here's how to do it:

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT COUNT(online.account_id) cnt from online;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Slave Database Compatibility

When using the READ UNCOMMITTED isolation level on a slave database, you may encounter the error: "Binary logging not possible...Transaction level 'READ-UNCOMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT.'" To resolve this, you can modify the my.cnf file on the master database to enable the "READ UNCOMMITTED" transaction isolation level for binlogging.

Alternative Solution

Alternatively, you can use the following technique instead of setting the isolation level:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT COUNT(online.account_id) cnt from online;
COMMIT;

This forces the query to read uncommitted data and then immediately commit the transaction, preventing any potential locks or conflicts.

Note that using the READ UNCOMMITTED isolation level allows access to uncommitted data, which may lead to data inconsistencies. It's important to consider the potential risks and use it cautiously when necessary.

The above is the detailed content of How to Run SELECT Statements in MySQL Without Locking?. For more information, please follow other related articles on the PHP Chinese website!