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

This article mainly introduces the relevant knowledge of "how to use for update in mysql". The editor shows you the operation process through an actual case. The operation method is simple, fast and practical. I hope this article "how to use for update in mysql" can help you solve the problem.

The use of for update in mysql is "select * from table where...for update"; with for update, you can add exclusive locks for rows when querying, and when one transaction operation is not completed, other transactions can be read but cannot be written or updated.

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

What is the use of for update in mysql

Usage scenarios of 1.for update

If you encounter scenarios with high concurrency and high requirements for data accuracy, you need to understand and use for update.

The role of for update is to add exclusive locks to rows when querying. When the operation of one transaction is not completed, other transactions can be read but cannot be written or updated.

For example, it involves money, inventory and so on. Typically, these operations are long and open transactions. If the inventory starts with 1, and immediately another process update updates the inventory to 0, and the transaction is not finished, the wrong data will be executed all the time, there will be a problem. Therefore, for upate is required to lock the data to prevent data errors during high concurrency.

Remember one principle: one lock, two judgments, three updates.

How to use 2.for update

Use poses:

Select * from table where xxx for update

Lock table of 3.for update

InnoDB defaults to row-level locks and, when there is an explicitly specified primary key, row-level locks. Otherwise, it is at the table level.

Example: suppose the table foods has three fields: id, name and status. Id is the primary key and status has an index.

Example 1: (specify the primary key clearly, and have this record, row-level lock)

SELECT * FROM foods WHERE id=1 FOR UPDATE;SELECT * FROM foods WHERE id=1 and name=' programmer programming Advanced Notes' FOR UPDATE

Example 2: (specify the primary key / index clearly, if there is no such record, there will be no lock)

SELECT * FROM foods WHERE id=-1 FOR UPDATE

Example 3: (no primary key / index, table level lock)

SELECT * FROM foods WHERE name=' programmer programming Advanced Notes' FOR UPDATE

Example 4: (primary key / index is not clear, table-level lock)

SELECT * FROM foods WHERE id'3' FOR UPDATE;SELECT * FROM foods WHERE id LIKE'3' FOR UPDATE

Pay attention to 4.for update

1.for update applies only to InnoDB, and the transaction must be opened to take effect between begin and commit.

two。 To test the lock table of for update, you can use MySQL's Command Mode to open two windows to do the test.

This is the end of the content about "how to use for update in mysql". Thank you for reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

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.