sql-optimistic lock, pessimistic lock in the concurrency

Every time you write a blog, the first sentence is this: the programmer is very hard, in addition to writing procedures, but also to write a blog! Of course, hope that the future day, a boss see this blog, to your programmer staff pay bar! Because the programmer's world is silent except bitter force. Most of the programmers in my eyes do not love to talk, silently bear the great pressure of programming, in addition to technical exchanges, they do not want to be good at communicating with others, but also not willing to anyone into their hearts!

Recently realized a truth, here to share to everyone: education represents your past, the ability to represent your present, study represents your future. We all know that the computer technology is developing rapidly, the speed is amazing fast, you and I are not careful, will be slowly eliminated! Therefore: daily uninterrupted learning is the magic weapon to avoid being eliminated.

Of course, the topic is more, let me get to the point!

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.

The most common method of handling multiuser concurrent access is locking. When a user locks an object in the database, other users can no longer access the object. The impact of locking on concurrent access is reflected in the granularity of the lock. For example, a lock placed on a table restricts concurrent access to an entire table, a lock placed on a data page restricts access to the entire data page, and a lock on a row restricts concurrent access to that row. Visible row lock granularity is minimal, concurrent access is best, page lock granularity is greatest, and the performance of concurrent access will be lower.

pessimistic Lock: it is assumed that concurrency conflicts occur and that all operations that may violate data integrity are masked. [1] Pessimistic locking assumes that other users attempt to access or change the object you are accessing, change the probability is very high, so in a pessimistic lock environment, before you start to change the object is locked, and until you commit the changes to release the lock. The pessimistic flaw is whether a page lock or a row lock, lock the time may be very long, so that may long lock an object, restrict the access of other users, that is, pessimistic lock concurrent access is not good.

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 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.

From the database vendor's point of view, it is better to use optimistic page locks, especially in batch operations that affect many rows, and to reduce the need for resources to improve database performance. Consider the clustered index again. Records in the database are stored in the physical order of the clustered index. If a page lock is used, when two users concurrently access changes to two adjacent rows on the same data page, one of the users must wait for another user to release the lock, which can significantly degrade the system's performance. InterBase, like most relational databases, employs optimistic locks, and read locks are shared, and write locks are exclusive. You can place a read lock on a read key, but you cannot place a write lock; you cannot place any locks on the write lock. Lock is an effective method to solve multi-user concurrent access at present.

In summary: in the actual production environment, if the concurrency is not large and dirty reading is not allowed, you can use pessimistic lock to solve the concurrency problem, but if the system concurrency is very large, pessimistic locking will bring very large performance problems, so we have to choose optimistic locking method.

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.

Here: For a simple example to illustrate the application of pessimistic locking, we take SQL Server as an example to illustrate:

If two threads modify the same record in the database at the same time, it causes the latter record to overwrite the previous one, causing some problems.

For example:

A ticketing system has a number of votes, the client every call to the ticket method, the number of votes minus one.

Scene:

A total of 300 tickets, assuming that two tickets, exactly at the same time ticket, they do the operation is first check the number of votes, and then minus one.

General SQL statements:

The problem is that the same time to obtain the remaining tickets are 300, each ticket has done an update to 299 of the operation, resulting in less than 1 of the votes, and actually out of two tickets.

Open two query windows and quickly run the above code to see the effect.

Therefore: We can use pessimistic lock to solve this problem

An update lock is added to the query to ensure that no dirty data is generated until the transaction ends without being read by other transactions.

To solve the above problems.

If the ticketing system is too high (for example: Chinese New Year ticket sales, October National Day tickets and other tickets for the high-level operation), we adopt the above pessimistic locking scheme will be greatly reduced system performance. For example: Conductor a locked the operation, conductor A in the process of processing this business, and went to drink a cup of coffee, during which the other salesman is unable to make the booking, so ...

Optimistic locking solution:

This is the optimistic locking solution that solves the data error problem caused by concurrency, but does not guarantee that every call to update will succeed and may return ' update failed '

Pessimistic lock and optimistic lock

Pessimistic locks must be successful, but when the concurrency is particularly large, it can cause a long blockage or even timeout, only suitable for small concurrency situations.

Optimistic locks do not always succeed every time, but can take full advantage of the system's concurrency processing mechanism, in large concurrency when the efficiency is much higher.

under SQL Server for example, details of pessimistic lock and optimistic lock ( Please be sure to understand the implementation of optimistic locking, because: in the actual system, optimistic lock application is more extensive )

Locks ( locking )  
The implementation of business logic often requires the exclusivity of data access. As in the final settlement of the financial system, we want to process the data for a certain  cut-off  point in time, and we do not want the data to change again during the settlement process (which may be a few seconds or maybe several hours). At this point, we need some mechanism to ensure that the data will not be modified by the outside in the process, such a mechanism, in this case, the so-called   "  lock  "  , that is, to our selected target data locked, so that it can not be modified by other programs.  
hibernate  supports two locking mechanisms: commonly referred to as   "  pessimistic lock ( pessimistic locking )  " and   "  Optimistic lock ( optimistic locking )    . The
Pessimistic lock ( pessimistic locking )  
pessimistic lock, as its name implies, is conservative in the data being modified by the outside world (including other transactions currently in the system, as well as transactions from external systems). The data is locked during the entire processing process. Pessimistic lock implementation, often rely on the database provided by the lock mechanism (also only the database layer provides a lock mechanism to truly guarantee the exclusivity of data access, otherwise, even in this system to implement the locking mechanism, there is no guarantee that the external system will not modify the data).  
A typical dependent database pessimistic lock call:  
Select * from account where name= ' Erica ' for update
This  sql  statement is locked All records in the  account  table that meet the search criteria ( name= "Erica"  ).  
before the transaction commits (when the transaction commits to release locks during the transaction), the records cannot be modified by the outside world.  
hibernate  's pessimistic lock is also a database-based lock mechanism implementation.  

Hibernate's lock modes are:
Ølockmode.none: no lock mechanism.
Ølockmode.write:hibernate is automatically acquired when the Insert and Update records are recorded.
The ølockmode.read:hibernate is automatically retrieved when the record is read.
Optimistic lock (optimistic Locking)
Relative pessimistic lock, the optimistic locking mechanism adopts a more relaxed locking mechanism. Pessimistic locking relies on the lock mechanism of the database in most cases, to ensure the maximum degree of exclusivity of the operation. But it comes with a lot of overhead for database performance, especially for long transactions, which are often unsustainable.

As a financial system, when an operator reads a user's data and modifies it on the basis of the user's data being read (such as changing the user account balance), the pessimistic locking mechanism means that the entire operation (from the operator reads the data, starts the modification, and commits the modified result. Even when the operator takes the time to cook the coffee, the database record is always locked, and you can see what happens if you face hundreds of thousand concurrent cases.

optimistic locking mechanism solves this problem to some extent. Optimistic locking, mostly based on data version  Version ) recording mechanism implementation. What is a data version? is to add a version identity to the data, which is typically achieved by adding a   "version"   field to a database table in a version-based (database table) solution. When the data is read, the version number is read together, and then the version number is added one after the update. At this point, the version data of the submitted data is compared to the current version information of the database table corresponding to the record, and if the submitted version number is greater than the current version number of the database table, it is updated, otherwise it is considered to be outdated data.

1  operator  A  reads it out ( version=1 ) and deducts  $50 ( $100-$50 ) from its account balance.
2  during operator  A  operation, operator  B  also reads this user information ( version=1 ) and deducts it from its account balance  $20  (  $100-$20 ).  
3  operator  A  completed the modification work, add a data version number one ( version=2 ), together with account deduction after balance ( balance=$50 ), Submit to database updates, at which time the data is updated, database records  version  updated to  2  because the submission data version is larger than the current version of the database record.  
4  operator  B  completed the operation, plus a version number ( version=2 ) attempt to submit data ( balance=$80 ) to the database. However, when compared to the database record version, the operator  B  submitted data version number is  2  The current version of the database record is also  2  not satisfied     The commit version must be larger than the current version in order to perform the update     optimistic lock policy, so the submission of the operator  B  is dismissed.  
This avoids the possibility that operators  B  the results of older  version=1 -based data modifications will overwrite operator  A  operations.  

As can be seen from the above example, the optimistic locking mechanism avoids the database lock-up overhead in a long transaction (both operator A and operator B do not locking the database data), which greatly improves the overall performance of the system under large concurrent volume.
It should be noted that the optimistic locking mechanism is often based on the data storage logic in the system, so there are some limitations, as in the above example, because the optimistic locking mechanism is implemented in our system, the user balance update from the external system is not controlled by our system, it may cause dirty data to be updated into the database. In the system design phase, we should take full account of the possibility of these situations, and make appropriate adjustments (such as the optimistic locking policy implemented in the database stored procedures, external only open the data update path based on this stored procedure, rather than the database table directly to the public).

Of course: In addition to the lock on the SQL can solve the data concurrency, we can also combine the programming language to achieve concurrency control.

Here: for example in C #, we can implement concurrency control through the definition of a code critical section. In the C # language, define a code critical section uses the keyword is lock, in this, the younger brother does not make the detailed introduction, only as the prompt everybody, the interested small partner may self-check about the C # critical section code keyword Lock's use, I Series blog: simulates concurrent/c# concurrency processing Lock or Thread

In the Lock keyword is part of the explanation, interested in the small tiger spots can refer to, thank you,

@ Chen Wolong's blog

sql-optimistic lock, pessimistic lock in the concurrency