Pessimistic lock and optimistic lock some points of view (from http://www.hetaoblog.com/myblogs/post/pessimistic-optimistic-locking-views.jhtml)

In essence, the optimistic locks and pessimistic locks of databases are mainly used to solve the following hypothetical scenarios and avoid the loss of updates:

A clear scenario 

The actual scenarios of the Assumption below can help us understand the problem clearly:

1. assume that a user on Dangdang has bought a book, in this case, there is an order with the order number 001 in the database. One of the status fields is "valid", indicating that the order is valid.
2. the background administrator queries the 001 order and the status is valid.
3. the user found that the Order was wrong, then, cancel the order. Assume that the following SQL statement is run: Update order_table set status = 'cancel' where order_id = 001;
4. the background management personnel can see that the status is valid in step B. At this time, although the user has canceled the order in Step C, the management personnel did not refresh the interface, the order status is valid, so click the "ship" button to send the order to the Logistics Department, and run a SQL statement similar to the following to change the order status to "delivered: update order_table set status = 'shipping' where order_id = 001

In fact, we have made a detailed analysis of the optimistic lock practices and the pessimistic lock practices,

Here, the definition of Wiki is referenced for more authoritative reference description.

Http://en.wikipedia.org/wiki/Lock_%28database%29

There are mechanical ISMs employed to manage the actions of multiple concurrent users on a database- The purpose is to prevent lost updates and dirty reads . The two types of locking are pessimistic and Optimistic Locking.

• Pessimistic locking : A user who reads a recor D, with the intention of updating it, Places an exclusive lock on the record to prevent other users from manipulating I T. this means no one else can manipulate that record until the user releases the lock. the downside is that users can be locked out for a very long time, thereby slowing the overall system response and causing frustration.
  • Where to use pessimistic locking: This is mainly used in environments where data-contention (the degree of users request to the database system at any one time) is heavy; where the cost of protecting data through locks is less than the cost of rolling back transactions if concurrency conflicts occur. pessimistic concurrency is best implemented when lock times will be short, as in programmatic processing of records. Pessimistic concurrency requires a persistent connection to the databaseAnd is not a scalable option when users are interacting with data, because records might be locked for relatively large periods of time. It is not appropriate for use in Web Application Development.

In essence, Here wiki means that both pessimistic and optimistic locks are designed to solve the problem of loss of updates or dirty reads. The key of pessimistic locks and optimistic locks is whether they are directly locked when reading records. The disadvantage of the pessimistic lock is obvious. It requires a continuous database connection, which is no longer suitable for Web applications.

Opinion 1: Only systems with severe conflicts need pessimistic locks;

Analysis: this is a more accurate statement. I mentioned in the original article:

"All Pessimistic lockThis method is suitable for situations with a high probability of State modification. If it is appropriate, You Need To determine based on the actual situation .", The statement is not accurate enough. Indeed, the pessimistic lock is used because two users have a high probability of updating the same data, that is, when the conflict is serious, so we use the pessimistic lock.

Opinion 2: A select for update check before the final commit is performed, and then the update commit is also an optimistic lock.

Analysis: this is a more accurate statement;

Indeed, this is in line with the traditional optimistic lock approach, that is, to check again at the end. However, when wiki explains pessimistic locks ,' It is not appropriate for use in Web Application Development . ', There are very few pessimistic locks now, so I myself classify this secondary check practice as a variant of the pessimistic lock, because this is in all optimistic locks, the method is the closest to the pessimistic lock. Select for update first and then update

* ***** Apart from the above views 1 and 2, they are more accurate. All of the following views are incorrect ***********

Opinion 3: the reason for this problem is that the database isolation level is uncommitted read level;

Analysis: This opinion is incorrect;

This process occurs at the Read committed isolation level. Each step from A to D, especially D, is not because the uncommitted data is read, it is only because the user interface is not refreshed [in fact, it cannot be automatically refreshed. This is equivalent to refreshing the database immediately when a Database Change occurs. This requires listening to the database, which is obviously complicated];

Opinion 4: the pessimistic lock means that when a user updates data, other users cannot read this record; that is, the update blocking read is called the pessimistic lock;

Analysis: this is wrong;

This is especially common in the Development of DB2 background; Because DB2 by default means that update will block reading; however, this is the implementation of locking concurrent processing for reading and writing in different databases. But this is not the difference between pessimistic locks and optimistic locks. Oracle can achieve write-not-blocking read only because of Multi-version concurrency control ), Http://en.wikipedia.org/wiki/Multiversion_concurrency_control;

However, in Oracle, optimistic locks and pessimistic locks can be controlled. This is essentially an application-layer choice.

Opinion 5: Oracle actually uses optimistic locks.

Analysis: this is wrong;

As mentioned above, Oracle can indeed achieve write without blocking reading, but this is not a problem of pessimistic locks and optimistic locks. This is because multi-version concurrency control is implemented. According to the definition of Wiki, pessimistic locks and optimistic locks are selected at the application level. Oracle applications only need to select for update in step 2, which is a pessimistic lock;

Moreover, at any isolation level, except for the short time of two-phase commit of distributed transactions, there is no write blocking in all other cases, from this point of view, Oracle can no longer adopt pessimistic locks -_-

Opinion 6: You don't need to be so troublesome. You only need to perform a normal select Check when submitting the update in Step D. [This is the double check method]

Analysis: this is wrong.

In fact Http://www.hetaoblog.com/database-lost-update-pessimistic-lock/ , '3. the work und to the conventional pessimistic lock practices has already been explained. If you want to do so, you still need to make a select for update when submitting the double check before the last update, otherwise, records may still be modified from the end of the SELECT statement to the time before the update statement is submitted;

Opinion 7: Pessimistic locks should be used as much as possible;

Analysis: this is wrong;

A. according to the concept of pessimistic lock, the user locks the record during reading (Step B) and does not release the lock until the update ends. Therefore, the entire lock process takes a long time;

B. In addition, pessimistic Locks require a continuous database connection, which is almost nonexistent in today's Web applications. wiki also said,   Pessimistic lock' Is not appropriate for use in Web Application Development .'

Therefore, most applications should be optimistic;