In the database, concurrency control has optimistic lock and pessimistic lock between, when with optimistic lock compare good when with pessimistic lock?

pessimistic lock, optimistic lock, only applicable to update operation

In the actual production environment, if the concurrency is not small, can use pessimistic locking method, this method is very convenient and simple to use.
However, if the concurrency of the system is very large, pessimistic locking can lead to very large performance problems, so choose optimistic locking method.

Pessimistic locking assumes that other users are trying to access or change the object you are accessing, changing the probability is very high, so in a pessimistic lock environment, before you begin to change this object to lock the object, and until you commit the changes before releasing the lock. The pessimistic flaw is that whether it is a page lock or a row lock, the lock-up time can be very long, which may lead to limited access to other users, that is, pessimistic lock concurrent access is not good.

Optimistic locking holds the probability that other users are trying to change the object you are changing, so the optimistic lock locks the object until you are ready to commit the changes, and does not lock when you read and change the object. It can be seen that the optimistic lock and lock time is shorter than the pessimistic lock, and the optimistic lock may obtain better concurrent access performance with larger lock granularity. But if the second user reads the object exactly before the first user commits the change, the database will find that the object has changed when he has made his own changes, so that the second user has to reread the object and make changes. This means that in an optimistic lock environment, the number of times the object is read by the concurrent user is increased.

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

"Missing Updates"
Xiao Zhang want to modify the source code inside a method, while she changes, Xiao Li opened the file, modified the B method and saved the file, and so on after the small Zhang Modified, save the file, Xiao Li's changes are covered.

"Inconsistent reading"
Xiao Zhang wants to know how many classes there are in the package, the package is divided into a A, a, two sub-packages. Xiao Zhang opened a package and saw 7 classes. Suddenly small Zhang received a call from his wife, in the small Zhang answer the phone, Xiao Li to a package added 2 classes, b package added 3 classes (originally B package is 5 classes).

Xiao Zhang after the phone and then open the B package, saw 8 classes, it is natural to conclude that there are 15 classes in the package.

Unfortunately, 15 is never the right answer. Before Xiao Li's amendment, the correct answer is 12 (7+5), 17 (9+8) after the change. Both of these answers are correct, although there is one that is not current. But 15 is wrong, because the data read by Xiao Zhang is inconsistent.

Summary: Inconsistent reading means that you are reading two kinds of data, both of which are correct, but not both at the same time.

"Isolated and immutable"
In enterprise applications, two common means of resolving concurrency conflicts are isolation and immutable.

Concurrency issues are only raised when multiple activities (processes or threads) access the same data at the same time. A natural way of thinking is that only one activity is allowed to access the data at the same time. This problem can be solved if the file is opened by a small sheet, and the other person is not allowed to open it, or someone can only open the copy in a read-only manner.

Isolation can effectively reduce the likelihood of errors occurring. We often see programmers fall into the quagmire of concurrency problems, each piece of code to consider concurrency problems, so tired. We can use isolation techniques to create isolated areas that do not care about concurrency when the program enters the quarantine domain. A good concurrency design is to create such isolated areas and keep the code running as much as possible.

Another way of thinking: only when you need to modify the shared data can cause concurrency problems, so we can make the data to be shared "immutable" to avoid concurrency problems. Of course we can't make all the data immutable, but if some of the data is immutable, we can relax our nerves when we work on them concurrently.

"Optimistic concurrency control, pessimistic concurrency control"
If the data is mutable and cannot be isolated. Two of the most common controls in this case are optimistic concurrency control and pessimistic concurrency control.



Suppose Xiao Zhang and Xiao Li want to modify the same file at the same time. If you use optimistic lock, both can open the file for modification, if the small Zhang Xian submitted content, no problem, his changes will be saved to the server. But Xiao Li will be in trouble when submitting, the version control server will detect two kinds of changes in the conflict, Xiao Li's submission will be specific, and by Xiao Li decide how to deal with this situation (for most version control software, will read and identify the changes made by Xiao Zhang, and then by the small Lee decide whether to merge).



If you are using a pessimistic lock, the small Zhang Xian check out file, then Xiao Li will not be able to check out the same file again, until the small Zhang submitted his changes.



It is recommended that you think of optimistic locking as a means of detecting conflict, and pessimistic locking is a means of avoiding conflict (strictly speaking, the optimistic lock is not actually called "lock", but the name has been spread out, then continue to use it). Some older version control systems, such as VSS 6.0, use a pessimistic locking mechanism. Modern version control systems generally support both, using optimistic locking by default.

The two kinds of locks have advantages and disadvantages ... This lazy translation, it is obvious that optimistic lock can improve the efficiency of concurrent access, but if there is a conflict can only be thrown up, and then again, pessimistic lock can avoid the occurrence of conflicts, but will reduce efficiency.



The choice to use that type of lock depends on the frequency of access and the severity of the conflict once it occurs. If the probability of a system being accessed concurrently is low, or the consequences of a conflict are less severe (the so-called consequence should be that the commit that is detected by the conflict will fail and must be repeated), you can use an optimistic lock, or use a pessimistic lock.



"I'll add two more words."
We often use locks when accessing the database, how to achieve optimistic lock and pessimistic lock. In hibernate, for example, you can implement optimistic locking by adding a version or timestamp field to a record. You can use the session. Lock () locks the object to implement a pessimistic lock (essentially, a select * from-t for UPDATE statement is executed).

Another high concurrency control solution optimistic concurrency control and pessimistic concurrency control Summary overview:

We can use two forms of concurrency control strategy: Optimistic concurrency control and pessimistic concurrency control.

Suppose Martin and David are both editing the customer file. If the optimistic locking strategy is used, all two of them can get a copy of the file and

You can freely edit the file. Assuming David is the first to finish the job, he can update his changes without any difficulty. However, when Martin wants to commit his changes, the concurrency control strategy will begin to work. The source control system detects a conflict between Martin's amendment and David's amendment, rejecting Martin's submission and pointing out how to handle the situation with Martin. If a pessimistic lock policy is used, no one else can edit the file as long as someone takes it out first. So, if Martin had taken the file first, David would have been able to work on the file only after Martin had completed the task and submitted it.

If you think of optimistic locking as a conflict detection, then pessimistic locking is about conflict avoidance. In the actual application of the source control system,

Both of these strategies can be used, but now most source code developers are more inclined to use optimistic locking strategies. (There is a very reasonable saying: optimistic lock is not a real lock, but it is very convenient and widely circulated, so that cannot be ignored.) ）

Both of these strategies have advantages and disadvantages. The problem with pessimistic locking is that it reduces concurrent programs. When Martin was editing a file he had locked,

The rest of us can only wait. People who have used pessimistic source control know what a frustrating thing this is. For enterprise data, the situation often gets worse, as long as someone is editing it, others can't read it, even more to edit it.

The optimistic locking strategy allows people to be freer, because it is only possible to encounter obstacles at the time of submission. The problem with this strategy is what happens when it comes to conflict. In fact, everyone behind David must read the version that David modified when they commit, and point out how to merge their own and David's changes before submitting a new version that has been modified. With the source code control system, there is no trouble in doing so. In many cases, the source control system does automatically merge, even when it cannot be automatically merged, making it easy to see the differences between different file versions. However, business data is often difficult to merge automatically, so it is often only possible to throw away the original stuff and start from scratch.

The criteria for choosing between optimistic and pessimistic locks are the frequency and severity of the conflict. If the conflict is small, or if the consequences of the conflict are not very serious, then the optimistic lock should usually be chosen because it can be better concurrency and easier to implement. However, if the outcome of the conflict is painful for the user, then a pessimistic strategy is needed.

The limitation of optimistic locking is that the business transaction will fail only when the data is submitted, and in some cases it can be costly to find that the failure is too late. It may take one hours for the user to enter the details of a lease, and too many errors will cause the user to lose confidence in the system. Another approach is to use pessimistic locking, which can detect errors early, but it is difficult to implement programmatically and reduces system flexibility.

(Note: The above is the optimistic locking strategy and pessimistic locking strategy in the concurrency control concept and the text description of the solution, the following I will specifically how to implement optimistic locking strategy and pessimistic lock policy description.) ）

Optimistic locking strategy implementation method:

is to use the transaction in C # or SQL to achieve the data operation is not successful rollback, personally feel the train station selling ticket system is also the operation, we see a small number of the display on the ticket, but we go to buy and can not come out.

Pessimistic lock policy Implementation method:

1, the normal ASPX page, when the user points submitted, directly to the submission and the relevant button Enabel to False, until the completion of the submission of the event, and then change back. In addition to the data layer, each time a data change is committed, it is necessary to determine whether the previous state of the data has changed to prevent the occurrence of concurrency changes.

2, in jquery, in jquery, you can set a global variable, commit, the first to determine the state of global variables, such as not allow the submission is directly returned, such as when the submission is allowed, the global variable is first set to "Do not allow commit", after the start of the commit, after the completion of the submission, In the callback method of the Post method of jquery, change the global variable to "allow commit".

3, pop-up window modification page, then use modal mode pop-up, such as Web page, can be used window.showModalDialog () to implement modal mode to open the modified page, to ensure that only one modification page is always opened. (This is a pessimistic lock on data from the data manipulation page, rather than a pessimistic lock in the database)