How to handle concurrent updates of MySQL data

Will UPDATE lock?

Will the SQL statement be locked if it is as follows?

UPDATE table1 SET num = num + 1 WHERE id=1;

The answer is no

In fact, MySQL supports locking data rows (InnoDB), and it does automatically add exclusive locks during UPDATE/DELETE operations. However , not all locks will be applied as long as there is an UPDATE keyword . For the above MySQL statement, it is not just one UPDATE statement, but should be similar to two SQL statements (pseudo code):

a = SELECT * FROM table1 WHERE id=1;
UPDATE table1 SET num = a.num + 1 WHERE id=1;

There is no lock when executing a SELECT statement, and it is only locked when executing an UPDATE. Therefore, inconsistent update data occurs during concurrent operations. Once the cause is found, solving the problem is not far away. There are two ways to solve this kind of problem:

• Explicitly lock SELECT through transactions
• Use optimistic locking mechanism

SELECT explicit

There are two ways to lock SELECT, as follows:

SELECT ... LOCK IN SHARE MODE #Shared lock, other transactions can read, but cannot update SELECT ... FOR UPDATE #Exclusive lock, other transactions cannot read or write

If you do not use these two statements, the SELECT statement will not be locked by default. And for the scenarios mentioned above, exclusive locks must be used. In addition, the above two statements are only effective within a transaction, otherwise they will not take effect. The way to use transactions in the MySQL command line is as follows:

SET AUTOCOMMIT=0; 
BEGIN WORK; 
 a = SELECT num FROM table1 WHERE id=2 FOR UPDATE; 
 UPDATE table1 SET num = a.num + 1 WHERE id=2; 
COMMIT WORK;

In this way, whenever you update data in the future, you will use this transaction to perform the operation; then in the case of concurrency, the later executed transaction will be blocked until the current transaction is completed. (Concurrency is changed to sequential execution through locking)

Using optimistic locking

Optimistic locking is a mechanism for lock implementation that naively assumes that all data that needs to be modified will not conflict. Therefore, it will not lock the data before updating, but only query the version number of the data row (the version number here is a custom field, which requires an additional field to be added on the basis of the business table, and it will be automatically incremented or updated every time it is updated).

When updating data, version number information will be added to the update conditions.

• When the version number does not change, it means that the data row has not been updated and the update conditions are met, so the update will be successful.
• When the version number changes, the data row cannot be updated because the condition is not met. At this time, a SQL operation is required. (Requery the record data row and update the data again with the new version number)

practice

Practice for update lock once on a student table, which has one data

Open two clients

Executed after the first open transaction

select name from student where id = 1 for update; 

After the second transaction is opened, the same statement is executed and it is found that the data is blocked by the first transaction.

At this time, the first transaction executes the modification and commits;

The select execution of the second transaction was found to be blocked for more than 4 seconds

summary

In general, both methods can support concurrent update operations on the database. But which one to use depends on the actual application scenario, which one the application scenario supports better and has the least impact on performance.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.