Shulou(Shulou.com)06/01 Report--

This article introduces the relevant knowledge of "mysql pessimistic lock and optimistic lock example analysis". In the actual case operation process, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

background

Consider the following two concurrency problems:

Missing updates: The update results of one transaction overwrite the update results of other transactions, i.e., the so-called update loss.

Dirty reads: Dirty reads occur when a transaction reads a record that is half complete with another transaction.

For example:

Two users modify the commodity inventory table at the same time, A and B enter at the same time, and see that the inventory is 100, A purchases one and modifies the inventory to 99(100-1). At this time, B buys two pieces and modifies the inventory to 98(100-2), because A and B read the inventory at the same time is 100, B can not see the inventory update made by A, so it causes B to read dirty, causing A to lose updates.

So in order to solve these problems caused by concurrency. We need to introduce concurrency control mechanisms-locks.

lock classification

pessimistic locking

Pessimism lock is that users look pessimistic when modifying data, conservative attitude, worried that other users will modify this data at the same time, so each time they modify this data will be locked in advance, only they can modify it (but other users can read it), and then release the lock after they have modified it.

optimistic locking

Optimistic lock is that users are optimistic when modifying data. No matter whether others modify or not, I will not lock it. When I modify it, I will judge whether the data has changed. If there is no change, I will update it successfully. If there is a change, it will not update successfully. I will retry the previous action until the update is successful.

lock application

pessimistic locking

To use pessimistic locking, we must first turn off the autocommit property of mysql database, because MySQL uses autocommit mode by default, that is, when you perform an update operation, MySQL will immediately commit the result.

The closing command is: set autocommit=0;

Pessimal locks are generally implemented using select... for update, which locks data during execution. Although the data is locked, it does not affect the use of ordinary queries for other transactions.

When we use pessimistic locks, statements in transactions such as:

//Start transaction

begin;/begin work;/start transaction;

//Query information

select * from order where id=1 for update;

//modify information

update order set name='names';

//Submit a transaction

commit;/commit work;

The query statement for update keyword here, only SELECT... FOR UPDATE or LOCK IN SHARE MODE The same piece of data will wait for other transactions to end before execution, and general SELECT queries will not be affected.

precautions

The keyword select…for update locks the data while the transaction is executing, preventing other transactions from changing the data. But there are rules to locking data.

Query condition and locking range:

1. Specific primary key value is query condition

For example, if the query condition is primary key ID=1, etc., if this piece of data exists, the current row of data is locked, if it does not exist, it is not locked.

2. The unspecified primary key value is the query condition

For example, if the query condition is Primary Key ID>1, etc., the entire data table will be locked.

3. There is no primary key in the query criteria

The entire data table is locked.

4. If index is used as query condition in query condition

Clearly specify the index and find it, then lock the entire data. If the specified index data cannot be found, no lock is placed.

optimistic locking

1. Use a self-increasing integer to represent the data version number. Check whether the version number is consistent when updating. For example, the data version in the database is 666. Version=666+1 when updating is submitted. Use the version value (=667) to compare with the database version+1(=667). If they are equal, they can be updated. If they are not equal, it is possible that other programs have updated the record, so an error is returned or a retry action is initiated.

e.g., Table

student(id,name,version)

1 a 1

Update student set name='txt' where id = #{id} and version = #{version};

At this point, after the operation, the data will change to id = 1,name = txt,version = 2. When another transaction 2 also performs the update operation, it is found that version != 1. At this time, transaction 2 will fail, thus ensuring the correctness of the data.

2, the use of time stamps to achieve the same principle.

3. Use other database fields, such as: amount, add conditions to judge whether the amount changes when updating, and the principle is the same as above.

optimistic lock diagram

"mysql pessimistic lock and optimistic lock example analysis" content is introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.