Database concurrency control do you choose optimistic or pessimistic locks?

In the actual production environment, if the concurrency is not large, you can use the pessimistic locking method. This method is very convenient and simple to use. However, if the system concurrency is very large, pessimistic locking will bring about a very large performance problem, so we need to choose an Optimistic Locking method.

Pessimistic locks assume that other users attempt to access or change the objects you are accessing and changing have a high probability. Therefore, in the pessimistic locks environment, lock the object before you begin to change it, and release the lock until you submit the changes. The pessimistic defect is that whether it is a page lock or a row lock, the lock may take a long time. This may limit the access of other users for a long time, that is, the concurrent access of the pessimistic lock is not good.

Optimistic locks hold that the probability of other users attempting to change the object you are changing is very small, so optimistic locks do not lock the object until you are about to commit the change, it is not locked when you read or change this object. It can be seen that optimistic locks take less time to lock than pessimistic locks. Optimistic locks can achieve better concurrent access performance with a larger lock granularity. However, if the second user reads the object just before the first user submits the change, the database will find that the object has changed when he completes the change, in this way, the second user has to re-read the object and make changes. This indicates that the number of times concurrent users read objects increases in the optimistic lock environment.

Take the version control system as an example to illustrate the two most basic concurrency problems.

Loss of updates]

John wants to modify method a in the source code. While she is modifying it, she opened the file, modified method B, and saved the file. After the modification, she saved the file, the changes made by Mr. Li are overwritten.

Inconsistent read]

John wants to know the total number of classes in the package. The package is divided into two sub-packages, a and B. John opened package a and saw seven classes. Suddenly, Mr. Zhang received a call from his wife. When Mr. Zhang answered the call, Mr. Li added two classes to the package, in Package B, three classes are added. In the original package B, five classes are added ).

After receiving the call, Mr. Zhang opened package B and saw eight classes. He naturally concluded that there were 15 classes in the package.

Unfortunately, 15 are never the correct answers. The correct answer is 127 + 5 before Mr. Li's modification, and 179 + 8 after the modification ). Both are correct, although one is not the current one. But 15 is incorrect, because the data read by John is inconsistent.

Conclusion: inconsistent reading means that you want to read both types of data. Both types of data are correct, but not both are correct at the same time.

Isolation and immutable]

In enterprise applications, two common means to resolve concurrency conflicts are isolation and immutable.

The concurrency issue occurs only when multiple active processes or threads access the same data at the same time. A natural idea is to allow only one activity to access data at a time. If John opens the file, other people are not allowed to open the file, or others can only open the copy in read-only mode to solve this problem.

Isolation can effectively reduce the possibility of errors. We often see programmers fall into the quarary of concurrency issues, and every piece of code should be written to consider concurrency issues, so it is too tired. We can use the isolation technology to create an isolation region. When the program enters the isolation region, you do not need to worry about concurrency issues. A good concurrency design is to create such isolated areas and ensure that the code runs as much as possible.

Another idea is to avoid concurrency issues only when you need to modify the shared data. Therefore, we can make the data to be shared as "immutable. Of course, we cannot make all the data immutable, but if some data is immutable, we can relax our nerves when performing concurrent operations on them.

Optimistic Concurrency Control and pessimistic Concurrency Control]

What if the data is variable and cannot be isolated? In this case, the two most common types of control are optimistic concurrency control and pessimistic concurrency control.

Assume that John and John want to modify the same file at the same time. If the optimistic lock is used, both of them can open the file and modify it. If John submits the content first, no problem will occur, and the changes he makes will be saved to the server. However, when Mr. Li submits the changes, the version control server detects conflicts between the two changes. Mr. Li's submission will be specific, it is up to Xiao Li to determine how to deal with this situation. For most version control software, Xiao Zhang's changes will be read and identified, and then Xiao Li decides whether to merge the changes ).

If you are using a pessimistic lock, John checks out the check out file first, then John cannot check the same file again until he submits his changes.

We recommend that you think of optimistic locks as a means to detect conflicts. pessimistic locks are a means to avoid conflicts. Strictly speaking, optimistic locks cannot be called "locks ", however, this name has been circulated, so continue to use it ). Some old version control systems, such as VSS 6.0, use a pessimistic lock mechanism. Modern version control systems generally support both types. Optimistic locks are used by default.

The two locks have their own advantages and disadvantages... This piece of lazy translation clearly shows that optimistic locks can improve the efficiency of concurrent access, but if there is a conflict, it can only be thrown up and then repeated; pessimistic locks can avoid conflicts, but reduce efficiency.

The selected lock depends on the Access frequency and the severity of the conflict. If the probability of concurrent access to the system is very low, or the consequences after a conflict are not very serious, the so-called consequence should be that the conflicting commit will fail and must be repeated), you can use optimistic locks, otherwise, use the pessimistic lock.

I would like to add another two words: we often use locks when accessing the database. How can we implement optimistic locks and pessimistic locks? Taking Hibernate as an example, you can add a version or timestamp field to the record to implement optimistic locks. You can use session. Lock () to Lock an object to implement a pessimistic Lock. Essentially, the SELECT * FROM t for update statement is executed ).

Another high concurrency control solution Optimistic Concurrency Control and pessimistic concurrency control summary:

We can use two forms of concurrency control policies: Optimistic Concurrency Control and pessimistic concurrency control.

Suppose martin and David both need to edit the Customer file. If the optimistic lock policy is used, both of them can get a Copy of the file, and

You can edit files as needed. If David finishes his first job, he can update his modifications without any difficulty. However, when Martin wants to submit his modifications, the concurrency control policy will take effect. The source code control system detects a conflict between Martin's modifications and David's modifications, and rejects Martin's submission. Martin is responsible for identifying how to handle this situation. If the pessimistic lock policy is used, no one else can edit the file as long as someone extracts the file first. Therefore, if the file is obtained by Martin, then David can operate the file only after Martin completes the task and submits it.

If we regard optimistic locks as about conflict detection, then pessimistic locks are about conflict avoidance. In the source code control system of the actual application,

Both policies can be used, but currently most source code developers prefer Optimistic Locking policies. There is a reasonable saying: Optimistic locks are not really locked, but they are convenient and widely used, so that they cannot be ignored .)

These two policies have their own advantages and disadvantages. The problem with pessimistic locks is that concurrent programs are reduced. When Martin is editing a file locked by him,

Others can only wait. People who have used pessimistic source code control know how frustrating this is. Enterprise data often gets worse. As long as someone is editing, others cannot read the data, let alone edit the data.

Optimistic Locking policies allow people to be more free, because they may encounter obstacles only when being submitted. The problem with this policy is what happens when a conflict occurs? In fact, everyone after David must read the version modified by David when submitting the statement and point out how to merge the modifications made by himself and David, then, submit a new version. With the source code control system, this will not cause any trouble. In many cases, the source code control system can automatically Merge files. Even if the source code control system cannot automatically Merge files, the differences between different file versions can be easily seen. However, business data is usually difficult to be automatically merged, So we often have to discard the original stuff and start from scratch.

The criteria for selection between optimistic locks and pessimistic locks are the frequency and severity of conflicts. If there are few conflicts, or the consequences of the conflicts are not very serious, you should choose optimistic locks in general, because it can get better concurrency and be easier to implement. However, if the conflicting results are painful for users, pessimistic strategies should be used.

The limitation of optimistic locks is that business transactions are about to fail only when data is committed, and in some cases, it may be costly to fail too late. It may take an hour for the user to enter details about the lease. Too many errors may cause the user to lose confidence in the system. Another method is to use the pessimistic lock, which can detect errors as early as possible, but it is difficult to program and reduce the flexibility of the system.

Note: The above is a text description of the optimistic lock policy and pessimistic lock policy concepts and solutions in concurrency control, I will describe how to implement an optimistic lock policy and a pessimistic lock policy in the project .)

Optimistic lock policy implementation method:

It is to use transactions in C # Or SQL to roll back when data operations are unsuccessful. I personally feel that the ticket selling system at the railway station is doing the same. We can see that there are a small number of remaining tickets on the display screen, but we cannot buy it.

Pessimistic lock policy implementation method:

1. on the normal aspx page, after the user clicks submit, change the enabel of the submit and related buttons to false directly until the event is submitted and then changed back. In addition, when submitting data changes at the data layer, you must determine whether the previous data status has changed to prevent concurrent changes.

2. In jquery, a global variable can be set in jquery. When a global variable is submitted, the status of the global variable is determined first. If the submission is not allowed, the global variable is returned directly. For example, when the submission is allowed, set the global variable to "not allowed to submit", and then start to submit. After the submission is complete, in the callback method of the jquery post method, change the global variable to "allow submission ".

3. Modify the page in the pop-up window, and use the mode to pop up. For example, the window can be used in the web page. showModalDialog () is used to enable the modification page in modal mode to ensure that only one modification page is opened. This means that the data is locked from the data operation page, rather than in the database)

Original article title: in databases, there are optimistic locks and pessimistic locks in concurrency control. When is optimistic locks better? When is pessimistic locks better?

Link: http://www.cnblogs.com/chenlulouis/archive/2010/08/17/1801358.html