Pessimistic lock (pessimistic lock), as the name implies, is very pessimistic, every time to get the data are thought that others will be modified, so every time when the data will be locked, so that others want to take this data will block until it gets the lock. The traditional relational database inside the use of a lot of this locking mechanism, such as row locks, table locks, read locks, write locks, etc., are locked before doing the operation. It refers to a conservative attitude towards data being modified by the outside world (including other transactions of the system, as well as transactions from external systems), so that the data is locked during the entire process of processing. Pessimistic lock implementation, often rely on the database to provide the lock mechanism (and only the database layer to provide the lock mechanism to truly ensure the exclusive access to data, otherwise, even in this system to achieve the lock mechanism, can not guarantee that the external system will not modify the data).
Optimistic lock (optimistic lock), as the name suggests, is very optimistic, every time to get the data are thought that others will not be modified, so will not be locked, but in the update will be judged in this period when others have to update this data, you can use the version number and other mechanisms. Optimistic locks are suitable for multiple-read application types, which can increase throughput, as if the database provides optimistic locks that are provided similar to the write_condition mechanism.
Both types of locks have advantages and disadvantages, do not think one is better than the other, such as optimistic lock for the case of less write, that is, conflicts really rarely occur, this can save the lock overhead, increase the overall throughput of the system. However, if the conflict is frequent, the top application will continue to retry, which reduces performance, so it is more appropriate to use pessimistic lock in this case.
In essence, the optimistic locking practices and pessimistic locking practices of the database are mainly to solve the hypothetical scenarios below to avoid loss of update problems:
A more clear scene.
The following hypothetical actual scenario can help us understand this problem more clearly:
Suppose that when an online user buys a book, there is an order in the database with order number 001, of which the Status field is ' valid ' to indicate that the order is valid;
Admin to check this 001 order and see if the status is valid
The user finds that the next order is wrong, and then orders are revoked, assuming that running such a sql:update order_table set status = ' Cancel ' where order_id = 001;
The backend manager sees the state as valid at this step in B, at this time, although the user in C this step has revoked the order, but the manager did not refresh the interface, see the order status is still valid, and then click the "Delivery" button, the order sent to the Logistics department, while running similar to the following SQL, the order status changed to shipped: Update order_table Set status = ' shipped ' WHERE order_id = 001
Point 1: Only a system with very serious conflict needs pessimistic locks; "All pessimistic locking practices are suitable for the state is modified the probability of a relatively high situation, the specific suitability of the need to be judged according to the actual situation." The expression is also this meaning, but the statement is not accurate; indeed, the reason for pessimistic lock is because two users update the same data probability is high, that is, the conflict is more serious situation, so use pessimistic lock.
Viewpoint 2: A Select for update check before submitting the final submission and then submitting update is also an optimistic lock practice, indeed, this is in line with the traditional optimistic lock practice, is to the last to check. But when it comes to interpreting pessimistic locks, the wiki is ' It isn't ' appropriate for use in Web application development. ' And now there are few pessimistic locking practices anymore, So I myself put this two-time check to the variant of the pessimistic lock, because this in all optimistic locks, the approach and pessimistic lock is the closest, are the first select for update, and then update
In the actual application of our data in the update, the more rigorous approach is to bring the "status" before the update, such as
Update order_table Set status = ' Cancel ' where order_id = 001 and Status = ' pending payment ' and ...
Update order_table Set status = ' shipped ' WHERE order_id = 001 and Status = ' paid ' and ...;
Then in the business logic code to determine the number of updated records, 0 to indicate that the database has been updated, otherwise it is normal.
