How to Select Data from a MySQL Slave Database Without Locking?

Selecting Data Without Locking in MySQL

When querying a table that is actively modified by events, it's possible to encounter table locks, which can impact performance. Is there a way to perform a select statement in MySQL that does not cause such locks?

Solution for Slave Databases

The solution mentioned in the article titled "MYSQL WITH NOLOCK" is not applicable in this case as it's a slave database. Additionally, setting the transaction isolation level to READ-UNCOMMITTED will result in an error on a slave database configured for STATEMENT-based binary logging.

MySQL-Specific Methodology

To avoid locking while selecting data on a MySQL slave database, you can use the following approach:

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

This method temporarily sets the transaction isolation level to READ UNCOMMITTED, which allows the select statement to proceed without obtaining locks. However, it's important to remember that this setting can only be used within a transaction with a subsequent COMMIT statement.

Improvement Suggestion

As an alternative, you can consider using the following syntax suggested by Michael Mior:

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

The above is the detailed content of How to Select Data from a MySQL Slave Database Without Locking?. For more information, please follow other related articles on the PHP Chinese website!