Java Multithreading Series-pessimistic lock and optimistic lock combat __java

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>

Mysql> SELECT * from t_goods;+----+--------+------+| ID | Status | Name |+----+--------+------+|  1 |      1 | Props | |  2 |      1 | Equipped with |+----+--------+------+2 rows in setmysql>

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>

Mysql> SELECT * from T_goods where id=1 for update;+----+--------+------+| ID | Status | Name |+----+--------+------+|  1 |      1 | Prop |+----+--------+------+1 row in setmysql>

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

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

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

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

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>

Mysql> SELECT * from t_goods where name= ' props ' for update;+----+--------+------+| ID | Status | Name |+----+--------+------+|  1 |      1 | Prop |+----+--------+------+1 row in setmysql>

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;

Mysql> SELECT * from t_goods where name= ' equip ' 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

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>

Mysql> begin; Query OK, 0 rows affectedmysql> select * t_goods where id>0 for update;+----+--------+------+| ID | Status | Name |+----+--------+------+|  1 |      1 | Props | |  2 |      1 | Equipped with |+----+--------+------+2 rows in setmysql>

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;

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>

Mysql> begin; Query OK, 0 rows affectedmysql> select * t_goods where id<>1 for update;+----+--------+------+| ID | Status | Name |+----+--------+------+|  2 |      1 | Equipped with |+----+--------+------+1 row in setmysql>

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;

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

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

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>

Mysql> SELECT * from T_goods where id<>2 for update;+----+--------+------+| ID | Status | Name |+----+--------+------+|  1 |      1 | Prop |+----+--------+------+1 row in setmysql>

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>

Mysql> SELECT * from t_goods;+----+--------+------+| ID | Status | Name |+----+--------+------+|  1 |      1 | Props | |  2 |      2 | Equipped with |+----+--------+------+2 rows in setmysql>

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>

Mysql> SELECT * from T_goods where Status=1 for update;+----+--------+------+| ID | Status | Name |+----+--------+------+|  1 |      1 | Prop |+----+--------+------+1 row in setmysql>

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

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>

Mysql> SELECT * from T_goods where status=2 for update;+----+--------+------+| ID | Status | Name |+----+--------+------+|  2 |      2 | Equipped with |+----+--------+------+1 row in setmysql>

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

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; Empty Set

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

Talk about the MySQL pessimistic lock, but pessimistic lock is not applicable to any scene, it also has some deficiencies, because the pessimistic lock in most cases rely on the database lock mechanism to achieve, to ensure the maximum degree of exclusivity. If the lock is too long and other users are unable to access for a long time, affecting concurrent access to the program, it also has a significant impact on the performance overhead of the database, especially for long transactions, which are often unbearable. So as opposed to the pessimistic lock, we have an optimistic lock, see below for details:

optimistic Lock Introduction:

Optimistic lock (optimistic locking) relative pessimistic lock, optimistic lock hypothesis that the data will not cause conflicts in general, so when the data is submitted to update, it will formally detect the conflict or not, if found conflict, so that the return of the user error information, Let the user decide how to do it. So how do we achieve optimistic lock, in general there are the following 2 ways:

1. Use the data version of the record mechanism implementation, this is the optimistic lock most commonly used as a way of implementation. What is the data version. That is, adding a version ID to the data is typically done by adding a "version" field of a numeric type to the database table. When reading the data, the value of the version field is read together, and each time the data is updated, add one to the version value. When we submit an update, we determine that the current version of the corresponding record in the database table is compared to the version value that was first taken out, if the current version number of the database table is equal to the first version value, it is updated, otherwise it is considered to be expired data. Use one of the following pictures to illustrate:

As shown in the figure above, if the update operation is executed sequentially, the version of the data is incremented sequentially, and no conflict occurs. However, if there is a different business operation that modifies the same version of the data, the first commit (b) will update the data version to 2, and the update of a will fail when it finds that the version of the data has been modified after B submits the update.

2. The second implementation of optimistic locking is similar to the first, adding a field in a table that requires optimistic lock control, the name does not matter, and the field type uses a timestamp (timestamp), similar to the version above. It is also in the update submission of the current database to check the timestamp of the data and its own update before the time stamp to compare, if the same is OK, otherwise is version conflict.

Use examples : take MySQL InnoDB as an example

Or take the previous example to lift: the Product goods table has a field Status,status 1 for the product is not under the order, the status of 2 for the goods have been orders, then we have to order a product to ensure that the status is 1. Suppose the product ID is 1.

The following order includes 3 steps:

1. Check out the product information

Select (status,status,version) from T_goods where Id=#{id}

2. Generate orders based on product information

3. Modify the status of the commodity to 2

Update T_goods

Set status=2,version=version+1

Where Id=#{id} and version=#{version};

So in order to use optimistic locks, we first modify the T_goods table, add a version field, the data default version value is 1.

T_goods table initial data is as follows: SQL code mysql> SELECT * from T_goods; +----+--------+------+---------+   | ID | Status | name |   Version |  +----+--------+------+---------+   |      1 | 1 |       Props |   1 |  |      2 | 2 |       Equipment |   2 | +----+--------+------+---------+ 2 rows in set mysql>

Mysql> select * from T_goods;
+----+--------+------+---------+
| id | status | name | version |
+----+--------+------+---------+
|  1 |      1 | Props |       1 |
|  2 |      2 | Equipment |       2 |
+----+--------+------+---------+
2 rows in set

mysql>

For the implementation of optimistic locks, I use mybatis to practice, as follows:

Goods entity classes: