Optimistic lock and pessimistic lock-solve concurrency problems and concurrency

Optimistic lock and pessimistic lock-solve concurrency problems and concurrency
Introduction Why do I need to lock (Concurrency Control )?

InMulti-User Environment. This is a well-known concurrency problem.

Typical conflictsInclude:

• Update loss: The update of a transaction overwrites the UPDATE results of other transactions, which is called update loss. For example, if user A changes the value from 6 to 2, and user B changes the value from 2 to 6, user A loses his update.
• Dirty read: Dirty reads occur when a transaction reads records of the other half of the transaction. For example, if user A and B See 6 values, user B changes the value to 2, and user A still reads 6 values.

To solve these concurrency problems. We need to introduce the concurrency control mechanism.

Concurrency Control Mechanism

Pessimistic lock: it is assumed that concurrency conflicts will occur,Block all operations that may violate data integrity. [1]

Optimistic lock: If no concurrency conflict occurs,Check whether data integrity is violated only when submitting the operation. [1] Optimistic locks cannot solve the dirty read problem.

 

Optimistic lock Application

Optimistic lock introduction:

Compared with the pessimistic lock, the Optimistic lock assumes that data generally does not cause conflicts, so when the data is submitted for update, in order to check whether the data conflict is detected. if a conflict is found, the user error information is returned and the user determines how to proceed. In general, there are two ways to implement optimistic locks:

1. UseData version(Version) record mechanism implementation, which is the most common implementation method of optimistic locks. What is the data version? That is, add a version ID for the data, which is generally achieved by adding a numeric "version" field to the database table. When reading data, read the value of the version field. Each time the data is updated, add one to the version value. When we submit an update request, compare the current version information recorded in the database table with the version value obtained for the first time. If the current version number of the database table is the same as the version value obtained for the first time, it is updated; otherwise, it is considered as expired data. Use the following figure to describe:

As shown in, if the update operation is executed sequentially, the version of the data increases sequentially without conflict. However, if different business operations are performed to modify the data of the same version, the first submitted operation (B in the figure below) will update the data version to 2, when A submits an update request after B and finds that the data version has been modified, the update operation of A fails.

 

2. The second method of Optimistic Locking is similar to the first method. It also adds a field to the table requiring Optimistic Locking control. The name does not matter, and the Field TypeTimestamp), Similar to the version above, it also checks the timestamp of data in the current database during update submission and compares it with the timestamp obtained before update. If it is consistent, OK, otherwise, a version conflict occurs.

 

Example: Take MySQL InnoDB as an Example

Take the previous example as an example: in the goods table, there is a field status. status 1 indicates that the product has not been placed, and status 2 indicates that the product has been placed, when placing an order for a product, we must ensure that the product status is 1. Assume that the product id is 1.

 

To place an order, follow these steps:

1. query the product information

select (status,status,version) from t_goods where id=#{id}

2. generate an order based on the product information

3. Change the product status to 2.

update t_goods set status=2,version=version+1where id=#{id} and version=#{version};

To use the optimistic lock, first modify the t_goods table and add a version field. The default version value of the data is 1.

The initial data of the t_goods table is as follows:

Mysql> select * from t_goods; + ---- + -------- + ------ + --------- + | id | status | name | version | + ---- + -------- + ------ + --------- + | 1 | 1 | item | 1 | 2 | | equipment | 2 | + ---- + -------- + ------ + --------- + 2 rows in setmysql>

 

For the implementation of optimistic locks, IUse MyBatis for practice, As follows:

Goods object class:

/*** ClassName: Goods <br/> * Function: Commodity entity. <br/> */public class Goods implements Serializable {/*** serialVersionUID: serialization ID. */private static final long serialVersionUID = 6803161908148880587l;/*** id: primary key id. */private int id;/*** status: Commodity status: 1 unordered; 2 unordered. */private int status;/*** name: product name. */private String name;/*** version: version number of the product data. */private int version; @ Override public String toString () {return "good id:" + id + ", goods status:" + status + ", goods name: "+ name +", goods version: "+ version;} // setter and getter}

 

GoodsDao

/*** UpdateGoodsUseCAS: Use CAS (Compare and set) to update product information * @ param goods product object * @ return number of rows affected */int updateGoodsUseCAS (Goods goods );

 

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

@ Testpublic void goodsDaoTest () {int goodsId = 1; // query the product information based on the same id and assign it to two objects Goods goods1 = this. goodsDao. getGoodsById (goodsId); Goods goods2 = this. goodsDao. getGoodsById (goodsId); // print the current product information System. out. println (goods1); System. out. println (goods2); // update item Information 1 goods1.setStatus (2); // modify status to 2 int updateResult1 = this. goodsDao. updateGoodsUseCAS (goods1); System. out. println ("modify product information 1" + (updateResult1 = 1? "Successful": "failed"); // update Product Information 2 goods1.setStatus (2); // modify status to 2 int updateResult2 = this. goodsDao. updateGoodsUseCAS (goods1); System. out. println ("Modify Product Information 2" + (updateResult2 = 1? "Successful": "failed "));}

 

Output result:

Good id: 1, goods status: 1, goods name: item, goods version: 1 good id: 1, goods status: 1, goods name: item, goods version: 1. Item Information Modification 1. Item Information Modification successful 2. Failed

 

 

Note:

In the GoodsDaoTest test method, we also find the data of the same version, assign it to different goods objects, modify the good1 object, and then perform the update operation. Then we modify goods2 and prompt that the operation failed when performing the update operation. The data in the t_goods table is as follows:

Mysql> select * from t_goods; + ---- + -------- + ------ + --------- + | id | status | name | version | + ---- + -------- + ------ + --------- + | 1 | 2 | item | 2 | 2 | | equipment | 2 | + ---- + -------- + ------ + --------- + 2 rows in setmysql>

 

We can see that the data version with id 1 has been changed to 2 during the first update. Therefore, when we update good2, the update where condition does not match, so the update will not succeed. The specific SQL statement is as follows:

update t_goods set status=2,version=version+1where id=#{id} and version=#{version};

In this way, we have implemented the optimistic lock.

 

Pessimistic lock Application

You need to use the database lock mechanism. For example, if the SQL server tablockx (exclusive table lock) option is selected, SQL Server will set the exclusive lock on the entire table until the command or transaction ends. This prevents other processes from reading or modifying table data.

SqlServerUsed in

Begin Tran
Select top 1 @ TrainNo = T_NO
From Train_ticket with (UPDLOCK) where S_Flag = 0

Update Train_ticket
Set T_Name = user,
T_Time = getdate (),
S_Flag = 1
Where T_NO = @ TrainNo
Commit

We use the with (UPDLOCK) option during query. When querying records, we add an update lock to the records, indicating that we are about to update the records. note that the update lock does not conflict with the shared lock, that is, other users can query the content of this table, but it conflicts with the update lock and the exclusive lock. therefore, other update users will be blocked.

Conclusion

In the actual production environment, if the concurrency is small and dirty reads are not allowed, you can use the pessimistic lock to solve the concurrency problem. However, if the system concurrency is very large, pessimistic locking can cause very high performance problems, so we need to choose an Optimistic Locking method.

Thank you: Thank you for your patience!