Optimistic lock and pessimistic lock and application example

Recently, because of the need in the work, learning the optimistic lock and pessimistic lock related knowledge, here I pass this article, I myself to these two "lock home" brother understanding record down;
-Pessimistic lock: As its name implies, it refers to the conservative attitude of the data being modified by the outside world (including other transactions currently in the system, as well as transactions from external systems), so that the data is locked during the entire data 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).
Take the usual MySQL InnoDB storage engine as an example: Add a Product table in the Items table has a field Status,status=1 indicates that the product has not been orders, Status=2 said that the product has been put orders, then we have to order each item before you must ensure that the product status= 1. Suppose you have a product with an ID of 10000, and if you don't use a lock, here's how to do it:
Identify the product status
Select status from items where id=10000;
Generate orders based on product information
INSERT into orders (id,item_id) values (null,10000);
Modify the item status to 2
Update Items set status=2 where id=10000;
These scenarios may be problematic in high concurrency environments:
It has already been mentioned that only the status=1 of goods is able to order it, the first step above, the item status is 1. However, when we perform the third update operation, it is possible for others to change the status of item to 2 first, but we do not know that the data has been modified, which may cause the same item to be put on the order 2 times, which makes the data inconsistent. So it's not safe to say this way.
Use pessimistic lock to achieve: In the above scene, the product information from the query to modify, in the middle there is a process of processing orders, the use of pessimistic locking principle is that when we query the items information after the current data locked, until we modify and then unlock. So in this process, because items are locked, there is no third party to modify them.
Note: To use pessimistic locks, we must turn off the auto-commit property of the MySQL database because MySQL uses the autocommit mode by default, which means that when you perform an update operation, MySQL will immediately submit the results. We can use the command to set MySQL to non-autocommit mode:
Set autocommit=0;
After setting up the autocommit, we can execute our normal business. Specific as follows:
Start a transaction
Begin;/begin Work;/start Transaction; (You can choose one of the three)
Check out the product information
Select status from items where id=10000 for update;
Generate orders based on product information
INSERT into orders (id,item_id) values (null,10000);
Change Item status to 2
Update items set status=2 where id=10000;
Commit a transaction
Commit;/commit work;
Note: The above begin/commit is the start and end of the transaction, because in the previous step we closed the MySQL autocommit, so we need to manually control the commit of the transaction, here is not a detailed table.
In the first step, we performed a query operation: Select status from items where id=10000 for update; Unlike normal queries, we used the Select...for Update method. This enables pessimistic locking through the database. At this point in the items table, the data with ID 10000 is locked, and the other transaction must wait for the transaction to be committed before it can be executed. This allows us to ensure that the current data is not modified by other transactions.
Note: It is important to note that in a transaction, only select ... For UPDATE or lock in SHARE MODE the same pen data will wait for other transactions to finish before executing, general Select ... is not affected by this. Take the example above when I execute the Select status from items where id=10000 for update; I am in another transaction if you execute the Select status again from the items where id=10000 for update, the second transaction waits for the first transaction to be committed, at which point the second query is in a blocked state. But if I was executing the select status in the second transaction from the items where id=10000; The data can be queried normally and not affected by the first transaction.
As we mentioned above, using Select...for Update will lock the data, but we need to pay attention to some lock level, MySQL innodb default row-level lock, so only explicitly specify the primary key, MySQL will execute row lock ( Lock only the selected data), or MySQL will execute table lock (lock the entire data form). In addition to the primary key, using an index can also affect the lock level of the database.
Pessimistic locking is not suitable for any scenario, it also has some shortcomings in its existence, because pessimistic locks rely on the database lock mechanism in most cases to ensure the maximum degree of exclusivity of the operation. If the lock time is too long, other users can not access for a long time, affecting the program's concurrent access, but also the database performance cost impact is very large, especially for long transactions, such overhead is often unbearable. So, in contrast to pessimistic locks, we have an optimistic lock, the concept of optimistic locking is as follows:
-Optimistic lock(optimistic Locking) relative pessimistic lock, optimistic locking hypothesis that the data is generally not a conflict, so when the data is submitted to update the data will be formally conflicting or not detected, if a conflict is found, let the user return the wrong information, let users decide how to do. So how do we achieve optimistic locking, there are generally the following 2 ways:
1. Using the data version recording mechanism is implemented, which is the most common way to implement optimistic locking. What is the data version. is to add a version identifier to the data, typically by adding a "version" field of a numeric type to the database table. When the data is read, the value of the version field is read together, and the data is updated every time that the version value is +1. When we submit an update, the current version of the corresponding record of the database table is judged to be compared with the first fetch, if the current version number of the database table is equal to the first one taken out, it is updated, otherwise it is considered to be outdated data. Use one of the following diagrams to illustrate:
As shown in the figure above, if the update operation is executed sequentially, the version of the data is incremented sequentially, and no conflict occurs. However, if there are different business operations to modify the same version of the data, then the first commit operation (Figure B) will update the data version to 2, when a after B commits the update found that the data version has been modified, then A's update operation will fail.
2. The second way to achieve optimistic locking is similar to the first one, as well as adding a field to the table that requires optimistic lock control, the name does not matter, the field type uses the timestamp (timestamp), and the above version is like The time stamp of the data in the current database is also checked at the time of the update submission, and the timestamp is compared before the update, if the consistency is OK, otherwise it is the version conflict.
For example, the MySQL InnoDB storage engine, or take the previous example commodity table in the Items table has a field status,status=1 that the product has not been orders, status=2 that the product has been put orders, Then we must ensure the status=1 of this product before we order each item. Suppose there is a commodity whose ID is 10000;
The order operation consists of 3 steps:
Check out the product information
Select (status,version) from items where Id=#{id}
Generate orders based on product information
Change Item status to 2
Update items set status=2,version=version+1 where Id=#{id} and version=#{version};
In order to use optimistic lock, we need to first modify the items table, add a version field, the data default version can be set to 1;
In fact, many of the products around us are optimistic about the use of locks, such as we often use the distributed storage engine xxx,xxx stored in each data has a version number, the version number will be incremented after each update, corresponding to the XXX put interface also has this version parameter, This parameter is to solve the concurrent update of the same data set, which is actually optimistic lock;
In many cases, the update data is first get, modify the get back data, then put back to the system. If more than one client get to the same data, modify and save it, then the first saved changes will be overwritten by the subsequent changes, resulting in data consistency issues, in most cases the application can be accepted, but in a few special cases, this is what we do not want to happen.
For example, there is a value of "1" in the system, and now both A and B clients take this value. After a and B clients want to change this value, suppose a to change to 12,b to change to 13, if not control, regardless of a and B who first update success, its update will be after the update overwritten. The optimistic locking mechanism introduced by XXX avoids such problems. Just in the example, suppose A and B fetch the data at the same time, the version number is 10,a first update, after the successful update, the value is 12, the version is 11. When B is updated, because it is based on a version number of 10, the server rejects the update and returns version error to avoid overwriting A's update. b You can choose to get the new version of value and then modify it based on it, or you can choose to force the update.