MySQL Select ... for update

Recent projects, as it relates to the use of optimistic and pessimistic locks in MySQL data, summarize the knowledge of optimistic and pessimistic locks in conjunction with project and online knowledge points.

Pessimistic Lock Introduction

Pessimistic locking is a pessimistic view of the data being modified (there must be concurrency problems when the data is modified), so the data is locked during the entire data processing process. Pessimistic lock implementation, often rely on the database provided by the lock mechanism (also only the database layer provides a lock mechanism to truly guarantee the exclusivity of data access, otherwise, even in the application layer to implement the locking mechanism, there is no guarantee that the external system will not modify the data).

Examples of usage scenarios

The Product goods table has a field status,status of 1 for the product is not under order, status is 2 for the product has been orders, then we have to order a product to ensure that the product status is 1. Suppose the product has an ID of 1. If the lock is not used, then the operation is as follows:

1. Check the product information select status from T_goods where ID=1;//2. Generate orders based on product information insert into t_orders (id,goods_id) values (null,1);//3. Modify the product status to 2update t_goods set status=2;

This scenario is likely to cause problems in high concurrent access scenarios. As mentioned earlier, only if the goods status is 1 o'clock to order the goods, the first step above, the item status of the query is 1. However, when we perform the third update operation, it is possible for others to first change the goods status to 2 for the order, but we do not know that the data has been modified, which may cause the same item to be put on the order 2 times, which makes the data inconsistent. So it's not safe to say this way.

use pessimistic locks to achieve

In the above scene, the product information from the query out to modify, in the middle there is a process of processing orders, the use of pessimistic locking principle is that when we query out goods information after the current data lock, until we modify and then unlock. So in this process, because the goods is locked, there will be no third party to modify it. To use pessimistic locks, we must close the auto-commit property of the MySQL database.

Set autocommit=0; After setting up the autocommit, we can execute our normal business. Specific as follows://0. Start transaction begin;/begin Work;/start transaction; (You can choose one of the three)//1. Check the product information select status from T_goods where id=1 for UPDATE;//2. Generate orders based on product information insert into T_orders (id,goods_id) Val UEs (null,1)//3. Modify the product status to 2update T_goods set STATUS=2;//4. Commit the transaction commit;/commit work;

Note: The above begin/commit is the start and end of the transaction, because in the previous step we closed the MySQL autocommit, so we need to manually control the commit of the transaction, here is not a detailed table.

In the first step, we performed a query operation: Select status from T_goods where id=1 for update; Unlike normal queries, we used the Select...for Update method to implement pessimistic locking through the database. At this point in the T_goods table, the data with ID 1 is locked, and the other transaction must wait for the transaction to be committed before it can be executed. This allows us to ensure that the current data is not modified by other transactions.

Note: It is important to note that in a transaction, only select ... For UPDATE or lock in SHARE MODE the same data will wait for other transactions to finish before executing, general Select ... is not affected by this. Take the example above when I perform the select status from T_goods where id=1 for update; I am in another transaction if the select status from T_goods where id=1 for update is executed again, then the second transaction waits for the first transaction to commit, at which point the second query is in a blocked state. But if I was executing the select status from T_goods where Id=1 in the second transaction, the data would be queried normally and not affected by the first transaction.

added : MySQL select...for update's Row lock and table lock

As we mentioned above, using Select...for Update will lock the data, but we need to pay attention to some lock levels, MySQL InnoDB default row-level lock, so only "explicitly" to specify the primary key, MySQL will execute row lock ( Lock only the selected data), or MySQL will execute table lock (lock the entire data form).

To illustrate:

database table T_goods, including Id,status,name three fields, the ID is the primary key, the database is recorded as follows;

　

Note: In order to test the database lock, I use two console to simulate different transaction operations, represented by Console1, Console2, respectively.

Example 1: (Explicitly specify primary key, and have this data, row lock)

Console1: The result was queried, but the data was locked

Set autocommit=0; SELECT * from T_goods where id=1 for update;

　

Console2: Query is blocked

　　Console2: If Console1 is not submitted for a long time, it will report an error

Example 2: (explicitly specify the primary key, if the data is not found, no lock)

Console1: The query result is empty

Set autocommit=0; SELECT * from T_goods where id=4 for update;

Console2: The query result is empty, the query is non-blocking, indicating that Console1 does not lock the data

Set autocommit=0; SELECT * from T_goods where id=4 for update;

Example 3: (No primary key, table lock)

Console1:

Set autocommit=0; SELECT * from T_goods where status=1 for update;

Console2:

SELECT * FROM person where state=2 for UPDATE

The query was blocked, indicating that Console1 locked the watch. If Console1 is not committed for a long time, the lock timeout is returned.

Example 4: (primary key ambiguous, table lock)

Console1: Query OK

Set autocommit=0; SELECT * from T_goods where id>1 for update;

Console2: The query was blocked, indicating that Console1 locked the watch.

SELECT * FROM person where id>=2 for UPDATE

The above is an example of the impact of the database primary key on the MySQL lock level, and it is important to note that the use of indexes also affects the locking level of the database in addition to the primary key.

Example: We modify the T_goods table to create an index to the Status field.

Example 5: (explicitly specified index, and there is this data, row lock)

Console1:

Set autocommit=0; SELECT * from T_goods where status=1 for update;

Console2: Blocking when querying Status=1 data, returning empty after timeout, indicating that the data is locked by Console1

Example 7: (explicitly specified index, no lock if no data is found)

Console1: Querying status=3 data, returning empty data

Set autocommit=0; SELECT * from T_goods where status=3 for update;

Console2: Querying status=3 data, returning empty data

SELECT * from T_goods where status=3 for update;

MySQL Select ... for update