MySQL pessimistic lock summary and practice

Recently learned a database of pessimistic lock and optimistic lock, according to their own understanding and online resources summarized as follows:

Pessimistic lock Introduction (encyclopedia):

Pessimistic locking, as its name implies, is a conservative attitude to the data being modified by the outside world (including other transactions currently in the system, as well as transactions from external systems), so that 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 this system to implement the locking mechanism, there is no guarantee that the external system will not modify the data).

examples of usage scenarios : MySQL InnoDB as an example

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.

1 if the lock is not used, then the operation is as follows:

1. Check out 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. Change the item status to 2

Update 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.

2 Use pessimistic lock 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.

Note: To use pessimistic locks, we must turn off the auto-commit property of the MySQL database because MySQL uses the autocommit mode by default, which means that when you perform an update operation, MySQL will immediately submit the results.

We can use the command to set MySQL to non-autocommit mode:

Set autocommit=0;

After setting up the autocommit, we can execute our normal business. Specific as follows:

0. Start a transaction

Begin;/begin Work;/start Transaction; (You can choose one of the three)

1. Check out 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) values (null,1);

3. Change the item status to 2

Update t_goods set status=2;

4. Commit a 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.

The first step above we performed a query operation: Select status from T_goods where id=1 for update;

Unlike normal queries, we use the Select...for Update method, which enables 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 pen 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;

SQL code

1. Mysql> select * from T_goods;
2. +----+--------+------+
3. | ID | Status | name |
4. +----+--------+------+
5. |      1 | 1 | Props |
6. |      2 | 1 | Equipment |
7. +----+--------+------+
8. 2 rows in Set
10. Mysql>

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 specifying a primary key and having this data, row lock)

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

SQL code

1. Mysql> SELECT * from T_goods where id=1 for update;
2. +----+--------+------+
3. | ID | Status | name |
4. +----+--------+------+
5. |      1 | 1 | Props |
6. +----+--------+------+
7. 1 row in Set
9. Mysql>

Console2: Query is blocked

SQL code

1. Mysql> SELECT * from T_goods where id=1 for update;

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

SQL code

1. Mysql> SELECT * from T_goods where id=1 for update;
2. ERROR 1205:lock wait timeout exceeded; Try restarting transaction

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

Console1: The query result is empty

SQL code

1. Mysql> SELECT * from T_goods where id=3 for update;
2. Empty Set

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

SQL code

1. Mysql> SELECT * from T_goods where id=3 for update;
2. Empty Set

Example 3: (No primary key, table lock)

Console1: Query name= props data, query normal

SQL code

1. Mysql> SELECT * from t_goods where name= ' props ' for update;
2. +----+--------+------+
3. | ID | Status | name |
4. +----+--------+------+
5. |      1 | 1 | Props |
6. +----+--------+------+
7. 1 row in Set
9. Mysql>

Console2: Query name= equipment data, query blocking, instructions Console1 to lock the watch

SQL code

1. Mysql> SELECT * from t_goods where name= ' equipment ' for update;

Console2: If Console1 is not committed for a long time, the query returns empty

SQL code

1. Mysql> SELECT * from t_goods where name= ' equipment ' for update;
2. Query OK,-1 rows affected

Example 4: (primary key ambiguous, table lock)

Console1: Query OK

SQL code

1. Mysql> begin;
2. Query OK, 0 rows affected
4. Mysql> SELECT * from T_goods where id>0 for update;
5. +----+--------+------+
6. | ID | Status | name |
7. +----+--------+------+
8. |      1 | 1 | Props |
9. |      2 | 1 | Equipment |
10. +----+--------+------+
11. 2 rows in Set
13. Mysql>

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

SQL code

1. Mysql> SELECT * from T_goods where id>1 for update;

Example 5: (primary key ambiguous, table lock)

Console1:

SQL code

1. Mysql> begin;
2. Query OK, 0 rows affected
4. Mysql> SELECT * from T_goods where id<>1 for update;
5. +----+--------+------+
6. | ID | Status | name |
7. +----+--------+------+
8. |      2 | 1 | Equipment |
9. +----+--------+------+
10. 1 row in Set
12. Mysql>

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

SQL code

1. Mysql> SELECT * from T_goods where id<>2 for update;

Console1: Commit Transaction

SQL code

1. Mysql> commit;
2. Query OK, 0 rows affected

Console2 query results are normal after CONSOLE2:CONSOLE1 transaction commits

SQL code

1. Mysql> SELECT * from T_goods where id<>2 for update;
2. +----+--------+------+
3. | ID | Status | name |
4. +----+--------+------+
5. |      1 | 1 | Props |
6. +----+--------+------+
7. 1 row in Set
9. Mysql>

The above is about the impact of the database primary key on the MySQL lock level instance, it should be noted that in addition to the primary key, the use of indexes will also affect the database lock level

Example:

We modify the T_goods table to create an index to the Status field

Change the status of data with ID 2 to 2, at which time the data in the table is:

SQL code

1. Mysql> select * from T_goods;
2. +----+--------+------+
3. | ID | Status | name |
4. +----+--------+------+
5. |      1 | 1 | Props |
6. |      2 | 2 | Equipment |
7. +----+--------+------+
8. 2 rows in Set
10. Mysql>

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

Console1:

SQL code

1. Mysql> SELECT * from T_goods where status=1 for update;
2. +----+--------+------+
3. | ID | Status | name |
4. +----+--------+------+
5. |      1 | 1 | Props |
6. +----+--------+------+
7. 1 row in Set
9. Mysql>

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

SQL code

1. Mysql> SELECT * from T_goods where status=1 for update;
2. Query OK,-1 rows affected

Console2: Query status=2 data, can normal query, indicating that Console1 only locked the line, unlocked the table

SQL code

1. Mysql> SELECT * from T_goods where status=2 for update;
2. +----+--------+------+
3. | ID | Status | name |
4. +----+--------+------+
5. |      2 | 2 | Equipment |
6. +----+--------+------+
7. 1 row in Set
9. Mysql>

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

Console1: Querying status=3 data, returning empty data

SQL code

1. Mysql> SELECT * from T_goods where status=3 for update;
2. Empty Set

Console2: Querying status=3 data, returning empty data

SQL code

1. Mysql> SELECT * from T_goods where status=3 for update;
2. Empty Set

MySQL pessimistic lock summary and practice