Pessimistic lock summary and practice

Recently studied the database pessimistic lock and optimistic lock, according to their own understanding and online resources summarized as follows:

Pessimistic Lock Introduction (encyclopedia):

A pessimistic lock, as its name implies, is conservative in that the data is modified by the outside world (including other transactions of the system, as well as transactions from the external system), so that the data is locked during the entire process of processing. Pessimistic lock implementation, often rely on the database to provide the lock mechanism (and only the database layer to provide the lock mechanism to truly ensure the exclusive access to data, otherwise, even in this system to achieve the lock mechanism, can not guarantee that the external system will not modify the data).

examples of using scenarios : Take MySQL InnoDB as an example

There is a field in the Product goods table Status,status 1 for the product is not under the order, the status of 2 for the goods have been orders, then we have a list of goods must ensure that the status of the item is 1. Suppose the product ID is 1.

1 If the lock is not used, then the operation method 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. Modify the status of the commodity to 2

Update t_goods set status=2;

This scenario is likely to cause problems with high concurrent access.

As mentioned earlier, only when the goods status is 1 o'clock can the product be listed, the first step in the above operation, the product status of the query is 1. However, when we perform the third step update operation, it is possible that other people first step to the product order goods status modified to 2, but we do not know that the data has been modified, which may cause the same product to be under a single 2 times, so that 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, the middle has a process of order processing, the use of pessimistic lock principle is, when we query out the goods information on the current data lock, until we modify the completion of the lock. So in this process, because the goods is locked, there is no third party to modify it.

Note: To use pessimistic locks, we must turn off the automatic submission property of the MySQL database because MySQL defaults to the autocommit mode, that is, when you perform an update operation, MySQL will immediately submit the results.

We can use the command to set MySQL as a autocommit mode:

Set autocommit=0;

After Autocommit is set up, we can perform our normal business. Specifically as follows:

0. Start the business

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. Modify the status of the commodity to 2

Update t_goods set status=2;

4. Submission of services

Commit;/commit work;

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

In the first step, 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 so that pessimistic locks are implemented through the database. At this point in the T_goods table, the data ID 1 is locked, and other transactions must wait for this transaction to be committed before they can be executed. This allows us to ensure that the current data is not modified by other transactions.

Note: It should be noted that in the transaction, only select ... For UPDATE or lock in SHARE MODE the same data will wait for the other transaction to finish before executing, general Select ... is not affected by this. Take the example above, when I execute select status from T_goods where id=1 for update; I'm in another transaction. If you perform the select status from T_goods where id=1 for update again, the second transaction waits for the first transaction to be committed, at which point the second query is blocked. But if I execute the Select status from T_goods where Id=1 in the second transaction, the data can be queried normally without being affected by the first transaction.

add: MySQL select...for update row lock and table lock

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

An example is provided:

database table T_goods, including Id,status,name three fields, ID as the primary key, the database records as follows;   SQL code mysql> SELECT * from T_goods; +----+--------+------+   | ID | Status |   name |  +----+--------+------+   |      1 | 1 |   Props |  |      2 | 1 |   Equipment | +----+--------+------+ 2 rows in set mysql>

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

Example 1: (explicitly specifying the primary key and having this data, row lock)

Console1: Query for results, but lock the data into SQL code mysql> SELECT * from T_goods where id=1 for update; +----+--------+------+   | ID | Status |   name |  +----+--------+------+   |      1 | 1 |   Props | +----+--------+------+ 1 row in set mysql>

Console2: Query blocked SQL code mysql> SELECT * from T_goods where id=1 for update;

Console2: If Console1 is not committed for a long time, the SQL code mysql> SELECT * from T_goods where id=1 for update; ERROR 1205:lock wait timeout exceeded; Try restarting transaction

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

Console1: Query result is empty SQL code mysql> SELECT * from T_goods where id=3 for update; Empty Set

Console2: The query result is empty and the query is not blocked, indicating that Console1 did not lock the SQL code on the data mysql> select * from T_goods where id=3 for update; Empty Set

Example 3: (No primary key, table lock)

Console1: Query name= prop data, query normal SQL code mysql> SELECT * from t_goods where name= ' props ' for update; +----+--------+------+   | ID | Status |   name |  +----+--------+------+   |      1 | 1 |   Props | +----+--------+------+ 1 row in set mysql>

Console2: Query name= equipment data, query blocking, description console1 the table to lock the SQL code mysql> SELECT * from t_goods where name= ' equipment ' for update;

Console2: If Console1 is not committed for a long time, the query returns an empty SQL code mysql> SELECT * from t_goods where name= ' equip ' for update; Query OK,-1 rows affected

Example 4: (primary key ambiguous, table lock)

Console1: Query normal SQL code mysql> begin;   Query OK, 0 rows affected mysql> SELECT * from T_goods where id>0 for update; +----+--------+------+   | ID | Status |   name |  +----+--------+------+   |      1 | 1 |   Props |  |      2 | 1 |   Equipment | +----+--------+------+ 2 rows in set mysql>

Console2: The query is blocked, stating that console1 the table to lock the SQL code mysql> SELECT * from T_goods where id>1 for update;

Example 5: (primary key ambiguous, table lock)

Console1:sql code mysql> begin;   Query OK, 0 rows affected mysql> SELECT * from T_goods where id<>1 for update; +----+--------+------+   | ID | Status |   name |  +----+--------+------+   |      2 | 1 |   Equipment | +----+--------+------+ 1 row in set mysql>

Console2: The query is blocked, stating that console1 the table to lock the SQL code mysql> SELECT * from T_goods where id<>2 for update;

CONSOLE1: Commit TRANSACTION SQL code mysql> commit; Query OK, 0 rows affected

Console2:console1 transaction commits, console2 query results Normal SQL code mysql> SELECT * from T_goods where id<>2 for update; +----+--------+------+   | ID | Status |   name |  +----+--------+------+   |      1 | 1 |   Props | +----+--------+------+ 1 row in set mysql>

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

Example:

We modify the T_goods table to create an index for the status field

Modify the status of the data with ID 2 to 2, when the data in the table is: SQL code mysql> SELECT * from T_goods; +----+--------+------+   | ID | Status |   name |  +----+--------+------+   |      1 | 1 |   Props |  |      2 | 2 |   Equipment | +----+--------+------+ 2 rows in set mysql>

Example 6: (explicitly specify the index and have this data, row lock)

Console1:sql Code mysql> SELECT * from T_goods where status=1 for update; +----+--------+------+   | ID | Status |   name |  +----+--------+------+   |      1 | 1 |   Props | +----+--------+------+ 1 row in set mysql>

Console2: Blocks when querying Status=1 data, returns null after timeout, indicating that the data is locked by CONSOLE1 SQL code mysql> SELECT * from T_goods where status=1 for update; Query OK,-1 rows affected

Console2: Query status=2 data, the normal query, indicating that Console1 only locked the line, unlocked table SQL code mysql> SELECT * from T_goods where status=2 for update; +----+--------+------+   | ID | Status |   name |  +----+--------+------+   |      2 | 2 |   Equipment | +----+--------+------+ 1 row in set mysql>

Example 7: (explicitly specify the index, if this data is not found, no lock)

Console1: Query status=3 data, return empty data SQL code mysql> SELECT * from T_goods where status=3 for update; Empty Set

Console2: Query status=3 data, return empty data SQL code mysql> SELECT * from T_goods where status=3 for update