What is the usage of for update in mysql

The usage of for update in mysql is "select * from table where...for update"; using for update can add an exclusive lock to the row during query. When a transaction operation is not completed, Other transactions can read but cannot write or update.

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

What is the usage of for update in mysql

1. The usage scenario of for update

If there is high concurrency and the data is accurate In very demanding scenarios, you need to understand and use for update.

The function of for update is to add an exclusive lock to the row when querying. When a transaction operation is not completed, other transactions can read but cannot write or update.

For example, it involves money, inventory, etc. Generally, these operations are a long series and start a transaction. If the inventory is 1 when it is first read, and another process immediately updates the inventory to 0, and the transaction has not yet ended, the wrong data will continue to be executed, and there will be problems. Therefore, for upate is needed to lock data to prevent data errors during high concurrency.

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

2. How to use for update

Usage posture:

select * from table where xxx for update

3. Lock table for update

InnoDB defaults to row-level locks. When there is a clearly specified primary key, it is row-level locks. Otherwise it is table level.

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

Example 1: (Explicitly specify the primary key, and there is this record, row-level lock)

SELECT * FROM foods WHERE id=1 FOR UPDATE;
SELECT * FROM foods WHERE id=1 and name=‘码农编程进阶笔记’ FOR UPDATE;

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

SELECT * FROM foods WHERE id=-1 FOR UPDATE;

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

SELECT * FROM foods WHERE name='Coder Programming Advanced Notes' FOR UPDATE;

Example 4: (Unclear primary key/index, table-level lock)

SELECT * FROM foods WHERE id<>‘3’ FOR UPDATE;
SELECT * FROM foods WHERE id LIKE ‘3’ FOR UPDATE;

4. Notes on for update

1.for update is only applicable to InnoDB and must Opening a transaction will only take effect between begin and commit.

2. To test the table lock situation for update, you can use MySQL's Command Mode to open two windows for testing.

Recommended learning: mysql video tutorial

The above is the detailed content of What is the usage of for update in mysql. For more information, please follow other related articles on the PHP Chinese website!