Shulou(Shulou.com)05/31 Report--

This article introduces the role of pessimistic lock and optimistic lock in MYSQL. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

Pessimistic lock (Pessimistic Lock)

Pessimistic lock (Pessimistic Lock), as its name implies, is very pessimistic. Every time you go to get the data, you think that someone else will modify it, so you lock it every time you get the data, so that others will block the data until it gets the lock. Many of these locking mechanisms are used in traditional relational databases, such as row locks, table locks, read locks, write locks and so on.

The most commonly used one is select. For update, which is a row lock that locks the resulting rows from select, and does not allow other transactions to do update, delete, or for update operations on these rows until the transaction is committed or rolled back.

Optimistic lock (Optimistic Lock)

Optimistic lock (Optimistic Lock), as the name implies, is very optimistic. Every time you go to get the data, you think that others will not modify it, so it will not be locked. During this period, the data can be easily read by others, but during the update, you can judge whether others have updated the data during this period. You can use mechanisms such as version number.

The version number mechanism is the most commonly used way of optimistic locking, that is, add a version number field to the table, check it before updating, and then update it as the where condition of the update statement. If the data has changed after obtaining the version number and before the update, the update will fail, because 0 pieces of data have been updated finally, and if the number of updates obtained by the PHP backend is 0, then the update failed. There is a concurrency problem, and then do specific treatment.

For example, two people modify a piece of data at the same time, and the process is as follows:

Operator An operates as follows:

Select id, balance, version from table where id= "1"

Query results: id=1, balance=1000, version=1

Update table set balance=balance+100, version=version+1 where id= "1" and version=1

After execution, the update result returned is 1, indicating that an update has been made. The result in the database is: id=1, balance=1100, version=2.

Operator B operates as follows:

Select id, balance, version from table where id= "1"

Query results: id=1, balance=1000, version=1, indicating that operator A has not been modified.

Update table set balance=balance-50, version=version+1 where id= "1" and version=1

At the time of check, operator A has not been modified, and when it is to be updated, operator A has already modified successfully, so the actual values in the database are id=1, balance=1100, version=2.

Operator B also added the version number (version=2) to attempt to submit data (balance=950) to the database, but the data of where id= "1" and version=1 could not be found at this time, so update failed and the execution result was 0, indicating that no data was updated successfully.

Now check again, the result is still the result of the completion of operator An operation.

Select id, balance, version from table where id= "1"

Query results: id=1, balance=1100, version=2

On the role of pessimistic locks and optimistic locks in MYSQL is shared here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.