Introduction to pessimistic lock (Encyclopedia)
Pessimistic lock, as its name implies, is a conservative attitude towards the modification of data by the outside world (including other current transactions of the system, as well as transactions from external systems). Therefore, during the whole data processing process, the data is locked. The implementation of pessimistic lock often depends on the lock mechanism provided by the database (and only the lock mechanism provided by the database layer can guarantee the exclusive access of data, otherwise, even if the locking mechanism is implemented in this system, it can not guarantee that the external system will not modify the data).
Examples of use scenarios: take MySQL InnoDB as an example
There is a field status in the goods table. If the status is 1, it means that the product has not been placed, and if the status is 2, it means that the product has been placed. Then we must ensure that the status of the product is 1 when placing an order for a certain product. Suppose the ID of the product is 1.
1. If the lock is not used, the operation method is as follows:
//1. Find out the commodity information
select status from t_goods where id=1;
//2. Generate orders according to product information
insert into t_orders(id,goods_id) values (null,1);
//3. Modify the product status to 2
update t_goods set status=2;The above scenario is likely to cause problems in the case of high concurrent access.
As mentioned above, only when the goods status is 1 can you place an order for the product. In the first step above, the status of the product found is 1. However, when we perform the update operation in step 3, it may appear that someone else places an order for the goods and changes the goods status to 2. However, we do not know that the data has been modified, which may cause the same product to be ordered twice, resulting in different data. So it’s not safe.
2. Use pessimistic lock to realize the following
In the above scenario, there is an order processing process from query to modification of commodity information. The principle of pessimistic lock is that when we query the goods information, we lock the current data until we have finished modifying it. In this process, because goods is locked, there will be no third party to modify it.
Note: to use pessimistic lock, we must turn off the auto commit attribute of MySQL database, because MySQL uses autocommit mode by default, that is, when you perform an update operation, MySQL will submit the result immediately.
We can use the command to set Mysql to non autocommit mode
set autocommit=0;After setting up autocommit, we can perform our normal business. The details are as follows:
//0. Start transaction
Begin / begin work / start transaction; (choose one of the three)
//1. Find out the commodity information
select status from t_goods where id=1 for update;
//2. generate orders based on commodity information
insert into t_orders(id,goods_id) values (null,1);
//3. Modify the product status to 2
update t_goods set status=2;
//4. Commit the transaction
commit;/commit work;Note: the above begin / commit refers to the start and end of the transaction. Since we closed the autocommit of MySQL in the previous step, we need to manually control the transaction submission. Here we will not elaborate.
In the first step above, we perform a query operation:select status from t_goods where id=1 for update;
Unlike normal queries, we use theselect…for updateIn this way, the pessimistic lock is realized through the database. At this time, at t_ In the goods table, the data with ID 1 is locked by us. Other transactions can only be executed after the transaction is committed. In this way, we can ensure that the current data will not be modified by other transactions.
Note: it should be noted that in transactions, onlySELECT ... FOR UPDATEorLOCK IN SHARE MODEFor the same data, it will wait for other transactions to finish before executingSELECT ...Is not affected by this. Take the example above, when I executeselect status from t_goods where id=1 for update;After. If I execute it again in another transactionselect status from t_goods where id=1 for update;Then the second transaction will always wait for the first transaction to commit, and the second query is in the blocked state, but if I execute in the second transactionselect status from t_goods where id=1;The data can be queried normally without being affected by the first transaction.
Add: MySQLselect…for updateRow lock and table lock
As we mentioned above, usingselect…for updateThe data will be locked, but we need to pay attention to some lock levels. MySQL InnoDB defaults to row level lock. Therefore, only when the primary key is specified “explicitly” will MySQL execute row lock (only the selected data will be locked), otherwise MySQL will execute table lock (locking the entire data form).
For example:
Database table t_ Good includes three fields: ID, status and name. ID is the primary key. The records in the database are as follows;
mysql> select * from t_goods;
+----+--------+------+
| id | status| name |
+----+--------+------+
|1 | 1 | props|
|2 | 1 | equipment|
+----+--------+------+
2 rowsin set
mysql>Note: to test the database lock, I use two console to simulate different transaction operations, which are represented by console 1 and console 2 respectively.
Example 1: (specify the primary key explicitly and have this data, row lock)
Console 1: the result is found, but the data is locked
mysql>select * from t_goods where id=1 for update;
+----+--------+------+
| id | status | name |
+----+--------+------+
|1 | 1 | props|
+----+--------+------+
1 rowinset
mysql>Console 2: query blocked
mysql> select * from t_goods where id=1 for update;Console 2: if console 1 is not submitted for a long time, an error will be reported
mysql> select * from t_goods where id=1 for update;
ERROR 1205 : Lock wait timeout exceeded;
try restarting transactionExample 2: (specify the primary key clearly. If there is no such data, no lock)
Console 1: query result is empty
mysql> select * from t_goods where id=3 for update;
Empty setConsole 2: the query result is empty and the query is not blocked, indicating that console 1 does not perform locking on the data
mysql> select * from t_goods where id=3 for update;
Empty setExample 3: (no primary key, table lock)
Console 1: query the data with name = item, and the query is normal
mysql>select * from t_goods where name='tools' for update;
+----+--------+------+
| id | status | name |
+----+--------+------+
|1 |1 | tools |
+----+--------+------+
1 rowinset
mysql>Console 2: query the data of name = equipment, query blocking, indicating that console 1 has locked the table
mysql> select * from t_goods where name='device' for update;Console 2: if console 1 is not submitted for a long time, the query returns null
mysql> select * from t_goods where name='device' for update;
Query OK, -1 rows affectedExample 4: (table lock with ambiguous primary key)
Console 1: query normal
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 rowsin set
mysql>Console 2: the query is blocked, indicating that console 1 has locked the table
mysql> select * from t_goods where id>1 for update;Example 5: (table lock with ambiguous primary key)
console1：
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>Console 2: the query is blocked, indicating that console 1 has locked the table
mysql> select * from t_goods where id<>2 for update;Console 1: commit transaction
mysql> commit;
Query OK, 0 rows affectedConsole 2: after the console 1 transaction is committed, the console 2 query results are normal
mysql>select *from t_goods where id<>2 for update;
+----+--------+------+
| id | status | name |
+----+--------+------+
|1 | 1 | props|
+----+--------+------+
1 rowinset
mysql>The above is about the impact of database primary key on MySQL lock level. It should be noted that in addition to the primary key, the use of index will also affect the lock level of the database
give an example:
We modify t_ Good table, create an index for the status field
Modify the status of the data with ID 2 to 2, and the data in the table is as follows:
mysql> select * from t_goods;
+----+--------+------+
| id | status| name |
+----+--------+------+
|1 | 1 | props|
|2 | 2 | equipment|
+----+--------+------+
2 rowsin set
mysql>Example 6: (specify index explicitly and have this data, row lock)
console1：
mysql>select * from t_goods where status=1 for update;
+----+--------+------+
| id | status | name |
+----+--------+------+
|1 | 1 | props|
+----+--------+------+
1 rowinset
mysql>Console 2: when querying the data with status = 1, it will return to null after timeout, indicating that the data is locked by console 1
mysql> select * from t_goods where status=1 for update;
Query OK, -1 rows affectedConsole 2: query the data with status = 2, which can be queried normally. It indicates that console 1 only locks rows and does not lock tables
mysql>select * from t_goods where status=2 for update;
+----+--------+------+
| id | status | name |
+----+--------+------+
|2 | 2 | equipment|
+----+--------+------+
1 rowinset
mysql>Example 7: (specify the index clearly, if there is no such data, no lock)
Console 1: query the data with status = 3 and return null data
mysql> select * from t_goods where status=3 for update;
Empty setConsole 2: query the data with status = 3 and return null data
mysql> select * from t_goods where status=3 for update;
Empty setIntroduction to optimistic lock:
Compared with pessimistic lock, optimistic lock assumes that data will not cause conflict in general. Therefore, when data is submitted and updated, whether data conflict will be formally detected. If conflict is found, user error information will be returned to let the user decide how to do it. In general, there are two ways to achieve optimistic locking:
1. Using version recording mechanism, which is the most commonly used way to implement optimistic lock. What is data version? In other words, adding a version ID to the data is usually achieved by adding a numeric type “version” field to the database table. When the data is read, the value of the version field is read out together. Each time the data is updated, the version value is added by one. When we submit the update, we judge that the current version information of the corresponding record of the database table is compared with the version value extracted for the first time. If the current version number of the database table is equal to the version value extracted for the first time, it will be updated. Otherwise, it will be considered as expired data. Use the following diagram to illustrate:
As shown in the figure above, if the update operation is executed in sequence, the version of the data will be incremented in turn, and there will be no conflict. However, if different business operations modify the data of the same version, the operation submitted first (B in the figure) will update the data version to 2. When a submits the update after B, it is found that the version of the data has been modified, then the update operation of a will fail.
2. the second implementation of optimistic lock is similar to the first one. It is also used to add a field to the table that needs optimistic lock control. The name doesn’t matter. The field type uses timestamp, Similar to the version above, it is also used to check the time stamp of data in the current database and the timestamp taken before updating. If it is consistent, OK, otherwise, it will be version conflict.
Examples of use: take MySQL InnoDB as an example
Take the previous example as an example: there is a field status in the goods table. If the status is 1, it means that the product has not been placed, and if the status is 2, it means that the product has been ordered. Then we must ensure that the status of the product is 1 when placing an order for a certain product. Suppose the ID of the product is 1.
The order operation consists of three steps:
1. Find out the commodity information
select (status,status,version) from t_goods where id=#{id}2. Generate orders according to product information
3. Modify the product status to 2
update t_goods set status=2,version=version+1where id=#{id} and version=#{version};So in order to use optimistic locking, we first modify t_ In the goods table, add a version field. The default version value of the data is 1.
T_ The initial data of the good table are as follows:
mysql> select * from t_goods;
+----+--------+------+---------+
| id | status| name | version|
+----+--------+------+---------+
|1 | 1 | props | 1|
|2 | 2 | equipment | 2|
+----+--------+------+---------+
2 rowsin set
mysql>For the implementation of optimistic lock, I use mybatis to practice, as follows:
Goods entity class:
/**
* ClassName: Goods <br/>
*Function: Commodity entity. < br / >
*/
public class Goods implements Serializable {
/**
*SerialVersionUID: serialid
*/
private static final long serialVersionUID = 6803791908148880587L;
/**
*ID: primary key ID
*/
private int id;
/**
*Status: product status: 1 not ordered, 2 ordered
*/
private int status;
/**
*Name: product name
*/
private String name;
/**
*Version: Product data version number
*/
private int version;
@Override
public String toString(){
return "good id:"+id+",goods status:"+status+",goods name:"+name+",goods version:"+version;
}
//setter and getter
}GoodsDao
mapper.xml
<update id="updateGoodsUseCAS" parameterType="Goods">
<![CDATA[
update t_goods
set status=#{status},name=#{name},version=version+1
where id=#{id} and version=#{version}
]]>
</update>Goodsdaotest test class
@Test
public void goodsDaoTest(){
int goodsId = 1;
//According to the same ID query commodity information, assigned to two objects
Goods goods1 = this.goodsDao.getGoodsById(goodsId);
Goods goods2 = this.goodsDao.getGoodsById(goodsId);
//Print current product information
System.out.println(goods1);
System.out.println(goods2);
//Update product information 1
Goods1. Setstatus (2); // modify the status to 2
int updateResult1 = this.goodsDao.updateGoodsUseCAS(goods1);
System.out.println ("modify product information 1" + (updateresult1 = = 1? "Success": "failed"));
//Update product information 2
Goods1. Setstatus (2); // modify the status to 2
int updateResult2 = this.goodsDao.updateGoodsUseCAS(goods1);
System.out.println ("modify product information 2" + (updateresult2 = = 1? "Success": "failed"));
}Output results:
- good id:1,goods status:1 , goods name: props, goods version:1
- good id:1,goods status:1 , goods name: props, goods version:1
- Successfully modified product information 1
- Failed to modify product information 2
explain:
In the goodsdaotest test method, we find out the data of the same version at the same time, assign it to different goods objects, and then modify the good1 object, and then perform the update operation. The execution is successful. Then we modify goods2, and the update operation prompts that the operation failed. At this time, t_ The data in the good table are as follows:
mysql> select \* from t\_goods;
+----+--------+------+---------+
| id | status| name | version|
+----+--------+------+---------+
|1 | 2 | props | 2|
|2 | 2 | equipment | 2|
+----+--------+------+---------+
2 rowsin set
mysql>We can see that the data version with ID 1 has been changed to 2 at the first update. Therefore, when we update good2, the update where condition does not match, so the update will not succeed. The specific SQL is as follows:
update t_goods set status=2,version=version+1 where id=#{id} and version=#{version};