Shulou(Shulou.com)06/01 Report--

I. introduction of optimistic locks

Optimistic lock is relative to pessimistic lock, and it is also a mechanism to avoid data processing errors caused by database fantasy reading and long business processing time, but optimistic lock will not deliberately use the locking mechanism of the database itself. but to ensure the correctness of the data according to the data itself.

Optimistic locking mechanism: add the version number or time summary to each database, and every time you operate on the data (especially the modification operation), you will always take the version number to obtain the data and modify the version number after changing the data.

Code example of optimistic lock

2.1 create a table

Create table em_oplock

(

Id VARCHAR (100) not null

Value VARCHAR (100)

Version int (10)

PRIMARY key (id)

) ENGINE=INNODB DEFAULT CHARSET=utf8

2.2 insert a piece of data

INSERT into em_oplock values ('1century, 1 month, week 1)

2.3 modify data

Update em_oplock set value='2',version=version+1 where id = 1 and version= 1

III. Examples of business use of optimistic locks

Transaction 1

Transaction 2

Description:

When two users operate the data with ID 1 at the same time, or when one user has not finished processing the data and the other user also operates on the data, after a series of business processing, the two users think that their data judgment is correct, so they both modify and submit the same data. If we do not do version control, the post-processing users will overwrite the data of the previous users. If we add version control, when user 1 processes successfully, user 2 will not process a single piece of data.

IV. Examples of business use of pessimistic locks

Transaction 1: data locked successfully

Transaction 2: waiting for the lock to be released

Transaction 1: the operation locks the data and commits, while releasing the locked data

Transaction 2: acquire data lock (latest data)

Description:

For the correctness of data processing, the data is locked (for update) before it is manipulated. The exclusive lock mechanism of the database itself is used to ensure that the data can only be processed by one user.

5. Comparison between optimistic lock and pessimistic lock

5.1 optimistic locks require additional fields to record the version number, increasing the complexity of the database design. (disadvantages of optimistic locks)

5.2 optimistic locks require the version number to be updated at the same time for each change, increasing the cost of development. (disadvantages of optimistic locks)

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

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

VI. The choice of optimistic lock and pessimistic lock

Whether pessimistic lock or optimistic lock, are for the actual business services, are to ensure the correctness of the data. The choice of optimistic lock or pessimistic lock needs to be weighed according to specific business scenarios, database design, development costs and other factors. If this business involves more areas, more developers, etc., it is recommended to use pessimistic lock. If the business is relatively single or there are few database operations and high concurrency requirements, it is recommended to use optimistic locks.

If we make the business more reasonable and the data better, it may not be so troublesome!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.