Reduce lock competition Reduce MySQL user wait time

The lock mechanism enables multiple threads to operate a table at the same time. As shown in, user A, user B, and user C may query or update data table A at the same time or successively (the previous job has not been completed. When a thread involves an update operation, it needs to obtain exclusive access. During the update process, all other threads that want to access the table must wait until the update is complete. This will cause lock competition issues. Thus, the user's waiting time is prolonged. In this article, I will discuss with you what measures can be taken to effectively avoid lock competition and reduce the waiting time of MySQL users.

　　

 

Background simulation:

To better illustrate this problem, I first simulate a daily case. Through the case, you can read the following content, which may be clearer. As shown in the following figure.

First, user A sends A query request to Table.

Then, user B sends an update request to Table. At this time, user B's request can be executed only after user a's job is completed.

Finally, user C sends A query request to Table. In the MySQL database, the priority of the update statement is higher than that of the query statement. Therefore, the query statement of user C can be executed only after the update job of user B is completed. User B's update job can only be executed after user a's query statement is completed. In this case, there is a serious lock competition problem.

What database engineers need to do now is to take measures in the database design and optimization process to reduce the unfavorable situation of such lock competition?

Measure 1: Use Lock Tables to increase the update speed.

For update jobs, performing many updates in a lock is faster than all locked updates. Therefore, if a table is updated frequently, such as the supermarket cash register system, you can use the Lock Tables option to increase the update speed. As the update speed increases, conflicts with Select query jobs will be significantly reduced, and lock competition can also be significantly restrained.

Measure 2: divide a table into several tables to reduce lock Competition

For example, a large shopping supermarket, such as Walmart, has many daily update operations on its sales record table. In this case, if the user needs to query the user while updating the user, it is obvious that the lock competition will be serious. In this case, you can manually divide a table into several tables. For example, you can set a data table for each cashier. In this case, user operations between cash registers are completed in their own tables without interfering with each other. During statistical analysis, you can use a view to integrate them into a table.

Step 3: Adjust the priority of a job

By default, in MySQL databases, update operations have a higher priority than Select queries. As shown in, if user B first sends a query application, then user C then sends an update request. After user a's query job is completed, which of the following requests will the system first execute? Note: by default, the system does not follow the first-to-last rule, that is, it does not execute user B's query request first, but executes the update process of user C. This is mainly because the update process has a higher priority than the Query Process.

However, in some specific circumstances, this priority may not meet the requirements of the enterprise. In this case, the database administrator needs to adjust the statement priority based on the actual situation. If necessary, you can implement it in the following three methods.

First, use the LOW_PRIOITY attribute. This attribute can reduce the priority of a specific statement. For example, you can lower the priority of a specific update statement or insert statement. However, you must note that this attribute is only useful for specific statements. That is, its scope is only for a specific statement, and does not affect the global.

The second is through the HIGH_PRIOITY attribute. There is a HIGH_PRIOITY attribute corresponding to the LOW_PRIOITY attribute. As the name suggests, this attribute can be used to increase the priority of a specific Select query statement. In the preceding case, if the HIGH_PRIOITY attribute is added to user C's query statement, user a immediately executes the query statement of user C after the query is completed. After user C completes, user B's update will be executed. It can be seen that the priority of the query statement is increased. Note that, like the preceding attribute, this scope is limited to specific query statements. It does not affect other query statements without this parameter. That is to say, if this attribute is not added to other query statements, the priority level is still lower than that of the update process.

Set LOW_PRIORIT_UPDATES = 1. The preceding two attributes are for specific statements without global impact. If the database administrator needs to adjust the priority level for a connection, how can this problem be achieved? In the preceding example, you need to increase the priority of the query statements connected to user C, instead of using the above attributes for each query. Set LOW_PRIORIT_UPDATES = 1. This option can be used to determine that all update processes in a specific connection use a lower priority. Note that this option is only useful for specific connections. It is not applicable to other connections.

4. Use the Low_Priority_updates option. As mentioned above, the first two statements are specific, and the last one is for specific connections, which will not affect the entire database. Can I reduce the priority of update statements within the entire database? In the above case, if other parameters are not used, will the query statement of user C be executed earlier than that of user B? If you have such requirements, you can use the Low_Priority_updates option to start the database. When this option is used to start the database, the system will give all the update statements in the database a lower priority. In this case, the query statement of user C is executed earlier than the update request of user B. The query job is not locked. Therefore, the query request of user A can be performed simultaneously with that of user C. Therefore, the lock competition can be effectively reduced by adjusting the statement execution priority.

It can be seen that attributes or options can be used to adjust the priority of a statement. For example, there is an application mainly for users to query. Updates are generally performed by administrators, and the updated data is not sensitive to users. In this case, the query priority can be increased based on the user priority principle. In this case, the user will experience less lock competition, which can shorten the waiting time of the user. When adjusting the user priority, you need to consider the Adjustment Scope. That is, it only adjusts specific statements, adjusts specific connections, or takes effect for the entire database.

Measure 4: You can use specific options for mixed operations.

Sometimes jobs with mixed operations, such as update operations, insert operations, and query operations, must adopt specific options based on specific circumstances. If you want to Insert and delete data tables at the same time, using the Insert Delayed option will greatly help you. Then, if you execute the Select and Delete statements on the same data table, there will be lock contention. In this case, the database administrator can select the Delete Limint option based on the actual situation to solve the speed problem.

In general, the lock competition is different from the deadlock, and does not have a great impact on the database operation. The user's waiting time may be extended. If the probability of concurrent access is not very high, there will be very little lock competition. So using the above measures will not bring much benefit. On the contrary, if you have a large number of concurrent accesses to a table, especially when different users perform queries, updates, deletes, inserts, and other hybrid jobs on the table, so the above measures can greatly reduce lock conflicts and reduce user waiting time.