Shulou(Shulou.com)06/01 Report--

Will UPDATE add locks?

Will the lock be added when the SQL statement 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 does automatically add exclusive locks to operations such as UPDATE/DELETE. It's just that not as long as there is a UPDATE keyword, the whole process will be locked. For the above MySQL statement, it is not just a 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 the SELECT statement is executed, but only when the UPDATE is executed. This is why the update data during concurrent operations is inconsistent. If the reason is found, the solution to the problem is not far away. There are two ways to solve this kind of problem:

Explicit locking of SELECT through transactions using optimistic locking mechanism

SELECT explicit

Lock the SELECT can be locked in two ways, as follows:

SELECT... LOCK IN SHARE MODE # shared lock, other transactions readable, non-updatable SELECT... FOR UPDATE # exclusive lock, other transactions cannot be read or written

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 can only take effect in a transaction, otherwise they will not take effect. Using transactions on 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, such transactions are used for operation as long as the data is updated later; in the case of concurrency, later executed transactions are blocked until the current transaction is completed. (change concurrency to sequential execution by locking)

Use optimistic locks

Optimistic locking is a mechanism for lock implementation, which always naively assumes that all the data that needs to be modified will not conflict. So it does not lock the data before the update, but only queries the version number of the data row (the version number here is a custom field, which needs to be added to the business table. Each update will be self-increasing or updated).

Version number information will be added to the update condition when the data is specifically updated.

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 conditions are not met, and a SQL operation is needed. (re-query the record data row and update the data with the new version number again)

Practice

Practice locking on for update one student table at a time with one piece of data in it

Open two client

Execute after the first transaction is opened

Select name from student where id = 1 for update

The second one executes the same statement after opening the transaction and finds that the data is blocked by the lock of the first transaction.

At this point, the first transaction performs the modification and commit

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

Summary

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

The above is the whole content of this article, I hope it will be helpful to your study, and I also hope that you will support it.

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.