Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to solve the problem of dirty reading in the implementation of count in Mysql". In daily operation, I believe that many people have doubts about how to solve the problem of dirty reading in the implementation of count in Mysql. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the problem of "how to solve the problem of dirty reading in Mysql implementation of count". Next, please follow the editor to study!

[detailed description of the problem]

SequoiaDB starts the transaction, and the transaction level is RC. Dirty reading occurs when count is executed in Mysql. The specific operations are as follows:

1. Open transaction, isolation level is RC

two。 Execute select count (*) from x1 to get the number of records

3. Insert the record again

4. Perform step 2 again

Step 4 found that count returned the number of newly inserted records, resulting in a dirty read problem.

See Annex mysql-1/2/3/4.png for details.

[solution]

1. The quick count mechanism in the current version (v3.2.1) is indeed dirty and is only temporarily used to return results quickly when count is unconditional.

two。 At present, quick count is a special optimization opened by the sequoiadb_optimize_select_count parameter, which can be turned on and off by the hidden system parameter: sequoiadb_optimize_select_count:.

[configuration method]

1. Non-persistent effective mode

Mysql > set global sequoiadb_optimizer_select_count=OFF

Restarting the mysql parameter returns to the default value of ON.

2. How persistence takes effect

Modify the database//auto.cnf configuration file, configure sequoiadb_optimizer_select_count=OFF in the "mysqld" module, and restart the instance by performing bin/sdb_sql_ctl restart after modification.

[notes]

1. Select count (*) from table where with filter conditions. Statement is still the same performance, because mysql does not push the count down to the storage engine.

2. The count () interface of sdb accesses the counter directly, which is equivalent to dirty reading. If users mind, they can't open sequoiadb_optimize_select_count.

The 3.sequoiadb_optimizer_select_count parameter is an internal hidden parameter, which is not open to the public, and there is no related data description.

At this point, the study on "how to solve the dirty reading problem in the implementation of count in Mysql" is over. I hope to be able to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.