Optimistic lock of database lock

I. Introduction to OPTIMISTIC locking

Optimistic lock is a relative pessimistic lock, but also to avoid the database phantom reading, business processing time too long and other reasons cause data processing error of a mechanism, but optimistic lock will not deliberately use the database itself lock mechanism, but according to the data itself to ensure the correctness of the data.

Optimistic locking mechanism: each database with a version number or time pinch, every time the operation of the data (especially the modification operation), always bring the version number to get the data and modify the version number after the change.

Second, optimistic lock code example

2.1 Creating a Table

CREATE TABLE Em_oplock

(

ID VARCHAR (+) NOT NULL,

Value VARCHAR (100),

version int (10),

PRIMARY Key (ID)

) Engine=innodb DEFAULT Charset=utf8;

2.2 Inserting a piece of data

INSERT into Em_oplock values (' 1 ', ' 1 ', 1);

2.3 Modifying data

Update Em_oplock set value= ' 2 ', version=version+1 where id = 1 and Version = 1;

Three, optimistic lock business use example

Transaction 1

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M02/8C/97/wKioL1hxFH7SEYOXAABLxKQoWSM726.png-wh_500x0-wm_3 -wmp_4-s_3674087905.png "title=" qq picture 20170108001617.png "alt=" Wkiol1hxfh7seyoxaablxkqowsm726.png-wh_50 "/>

Transaction 2

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M00/8C/97/wKioL1hxFP7RvCRhAABP19zPfT8341.png-wh_500x0-wm_3 -wmp_4-s_1639407910.png "title=" qq picture 20170108001852.png "alt=" Wkiol1hxfp7rvcrhaabp19zpft8341.png-wh_50 "/>

Description

When two users manipulate the data with ID 1 at the same time or one user does not finish processing another user, two users get the data to do a series of business processing and think their data is correct, so the same data is modified to submit. If we don't do versioning, the post-processing user will overwrite the previous user's data . If we add version control, when User 1 processing succeeds, user 2 will not process a single piece of data.

Four, pessimistic lock business use example

Transaction 1: Successfully locked data

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M01/8C/97/wKioL1hxGTbSncMBAAAyk4ASuDs075.png-wh_500x0-wm_3 -wmp_4-s_2353218128.png "title=" 1.png "alt=" Wkiol1hxgtbsncmbaaayk4asuds075.png-wh_50 "/>

Transaction 2: Waiting for the lock to be released

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M02/8C/97/wKioL1hxGYfwygblAAAp38Xlomc400.png-wh_500x0-wm_3 -wmp_4-s_3691114403.png "title=" 2.png "alt=" Wkiol1hxgyfwygblaaap38xlomc400.png-wh_50 "/>

Transaction 1: Operation locks Data and commits, releasing locked data at the same time

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M02/8C/97/wKioL1hxGpqDayphAAA96rSP5GA336.png-wh_500x0-wm_3 -wmp_4-s_153833244.png "title=" 3.png "alt=" Wkiol1hxgpqdayphaaa96rsp5ga336.png-wh_50 "/>

Transaction 2: Get Data Lock (latest data)

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M00/8C/97/wKioL1hxGraCul_zAAAO0JYVfHE115.png-wh_500x0-wm_3 -wmp_4-s_1111251704.png "title=" 4.png "alt=" Wkiol1hxgracul_zaaao0jyvfhe115.png-wh_50 "/>

Description

For the correctness of the data processing, the data is locked (for update) prior to operation. Using the database itself lock mechanism, ensure that the data can only one user of the processing.

The comparison between optimistic lock and pessimistic lock

5.1 Optimistic locking requires adding additional fields to record the version number, increasing the complexity of the database design. (Disadvantages of optimistic locking)

5.2 Optimistic locks need to update the version number at the same time for each modification, increasing the cost of development. (disadvantages of optimistic locking)

5.3 When the concurrency is large or the business time processing is long, it causes the database lock to wait for a long time, limit the concurrency and quickly consume the database resources. (disadvantage of pessimistic lock)

5.4 Pessimistic lock operation, you need to have a certain degree of understanding of the lock mechanism of the database. Otherwise, it is easy to cause a table lock or deadlock. (disadvantage of pessimistic lock)

Vi. the choice of optimistic lock and pessimistic lock

Whether pessimistic or optimistic lock, are for the actual business services, are to ensure the correctness of the data. Choosing optimistic or pessimistic locks requires tradeoffs based on specific business scenarios, database design, development costs, and more. If this business involves more faces, more developers, etc., it is recommended to use pessimistic lock. Optimistic locking is recommended if this business is relatively single or where database operations are relatively small and concurrency requirements are high.  

If we design the business a bit more rationally, the data is a little better for design, and perhaps it doesn't need to be so troublesome!

Optimistic lock of database lock