Optimistic locking and pessimistic locking--solving concurrency problems

Why does the introduction require a lock (concurrency control)?

In a multiuser environment , multiple users may update the same record at the same time, which can create a conflict. This is the famous concurrency problem.

Typical conflicts are:

• missing updates : Updates for one transaction overwrite the update results of other transactions, which is called update loss. For example: User A changes the value from 6 to 2, and User B changes the value from 2 to 6, then user a loses his update.
• Dirty Read : Dirty reads occur when a transaction reads other records that complete half the transaction. For example, the user A, B sees a value of 6, the value is changed to 2, and the value read by user A is still 6.

In order to solve the problems caused by these concurrency. We need to introduce concurrency control mechanisms.

concurrency control mechanism

pessimistic Lock: it is assumed that concurrency conflicts occur and that all operations that may violate data integrity are masked. [1]

optimistic Lock: Assume that there will be no concurrency conflicts and only check for violation of data integrity when committing the operation. [1] Optimistic locking does not solve the problem of dirty reading.

Optimistic lock application

Introduction to optimistic Locking:

Optimistic lock (optimistic Locking) relative pessimistic lock, optimistic locking hypothesis that the data generally do not cause conflict, so when the data is submitted to update the data will be formally conflicting or not detected, if a conflict is found, then let the return of the user error information, Let the user decide how to do it. So how do we achieve optimistic locking, there are generally the following 2 ways:

1. Using the data version recording mechanism is implemented, which is the most common way to implement optimistic locking. What is a data version? is to add a version identifier to the data, typically by adding a "version" field of a numeric type to the database table. When the data is read, the value of the version field is read together, and the data is updated each time, adding one to this version value. When we submit an update, the current version of the corresponding record of the database table is judged to be compared with the first fetch, if the current version number of the database table is equal to the first one taken out, it is updated, otherwise it is considered to be outdated data. Use one of the following diagrams to illustrate:

As shown, if the update operation is executed sequentially, the version of the data is incremented sequentially, and no conflict occurs. However, if there are different business operations to modify the same version of the data, then the first commit operation (Figure B) will update the data version to 2, when a after B commits the update found that the data version has been modified, then A's update operation will fail.

2. The second way to achieve optimistic locking is similar to the first one, as well as adding a field to the table that requires optimistic lock control, the name does not matter, the field type uses the timestamp (timestamp), and the above version is like The time stamp of the data in the current database is also checked at the time of the update submission, and the timestamp is compared before the update, if the consistency is OK, otherwise it is the version conflict.

Examples of Use : MySQL InnoDB as an example

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

The order operation consists of 3 steps:

1. Check out the product information

Select  from where id=#{id}

2. Generate orders based on product information

3. Change the item status to 2

Updateset status=2, version=version+1  where id= and version=#{version};

So in order to use optimistic lock, 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:

Mysql> Select *  fromT_goods;+----+--------+------+---------+|Id|Status|Name|Version|+----+--------+------+---------+|  1 |      1 |Props|       1 ||  2 |      2 |Equipment|       2 |+----+--------+------+---------+2Rowsinch SetMySQL>

For the implementation of optimistic locking, I use MyBatis to practice , specifically as follows:

Goods entity classes:

/*** Classname:goods <br/> * Function: Commodity entity. <br/>*/ Public classGoodsImplementsSerializable {/*** Serialversionuid: Serialization ID. */    Private Static Final LongSerialversionuid = 6803791908148880587L; /*** ID: primary key ID. */    Private intID; /*** Status: Commodity: 1 No orders, 2 orders. */    Private intstatus; /*** Name: Product name. */    PrivateString name; /*** Version: Product data version number. */    Private intversion; @Override PublicString toString () {return"Good ID:" +id+ ", goods Status:" +status+ ", Goods Name:" +name+ ", goods version:" +version; }    //Setter and Getter}

Goodsdao

/** * Updategoodsusecas: Update product information with CAS (Compare and set)  @param@return*/int Updategoodsusecas (Goods Goods);

Mapper.xml

<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 voidgoodsdaotest () {intGoodsid = 1; //Query the product information according to the same ID, assign to 2 objectsGoods GOODS1 = This. Goodsdao.getgoodsbyid (GOODSID); Goods Goods2= This. Goodsdao.getgoodsbyid (GOODSID); //Print Current Product informationSystem.out.println (GOODS1);        System.out.println (GOODS2); //Update product Information 1Goods1.setstatus (2);//Modify status to 2    intUPDATERESULT1 = This. Goodsdao.updategoodsusecas (GOODS1); System.out.println ("Modify product Information 1" + (updateresult1==1? ") Success ":" Failed ")); //Update product Information 2Goods1.setstatus (2);//Modify status to 2    intUPDATERESULT2 = This. Goodsdao.updategoodsusecas (GOODS1); System.out.println ("Modify product Information 2" + (updateresult2==1? ") Success ":" Failed "));}

Output Result:

Good id:1,goods status:1,goods Name: props, goods version:1  good id:1,goods status:1,goods name: props, goods version:1  Modify Product Information 1 success  

Description

In the goodsdaotest test method, we also identify the same version of data, assign to different goods objects, and then modify the good1 The object then performs an update operation that executes successfully. Then we modify the goods2, and the prompt operation fails when the update operation is performed. At this point The data in the T_goods table is as follows:

Mysql> Select *  fromT_goods;+----+--------+------+---------+|Id|Status|Name|Version|+----+--------+------+---------+|  1 |      2 |Props|       2 ||  2 |      2 |Equipment|       2 |+----+--------+------+---------+2Rowsinch SetMySQL> 

We can see that the data version with ID 1 has been modified to 2 on the first update . So when we update good2 , the update where condition already does not match, so the updates will not succeed, the specific SQL as follows:

Updateset status=2, version=version+1  where id= and version=#{version};

So we can achieve an optimistic lock.

Pessimistic lock Application

You need to use a lock mechanism for the database, such as SQL Server's TABLOCKX (exclusive table Lock) When this option is selected, SQL Server will lock the entire table until the command or transaction ends. This prevents other processes from reading or modifying the data in the table.

SQL Server used 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 [email protected]
Commit

We used the WITH (UPDLOCK) option when querying the records and we added an update lock to the record, indicating that we are about to update this record. Note Update locks and shared locks do not conflict, that is, other users can also query the contents of this table, but the update lock and exclusive lock is a conflict. So the other update users will block.

Conclusion

In the actual production environment, if the concurrency is not large and dirty reading is not allowed, pessimistic locking can be used to solve the concurrency problem, but if the system is very large concurrency, pessimistic locking can cause a lot of performance problems, so we should choose optimistic locking method.

Acknowledgements: Thank you for your patience to read!

Optimistic locking and pessimistic locking--solving concurrency problems