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

This article is about what optimistic locks and pessimistic locks are in MySQL. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

The task of concurrency control in database management system is to ensure that the same data in the database is accessed by multiple transactions at the same time without destroying the isolation and unity of transactions and the unity of the database.

The main technical means of optimistic locking and pessimistic locking concurrency control

Pessimistic lock

Pessimistic concurrency control (pessimistic lock, PCC) is a method of concurrency control in relational database management systems. It prevents one transaction from modifying data in a way that affects other users. If a lock is applied to each row of the operation performed by a transaction, only when the transaction lock is released can other transactions perform operations that conflict with the lock

Pessimistic concurrency control is mainly used in environments where data contention is fierce, and the cost of using locks to protect data in the event of concurrency conflicts is lower than that of rolling back transactions.

Pessimistic lock, which refers to a conservative (pessimistic) attitude towards the modification of data by the outside world (including other current transactions of the system, as well as transactions from external systems), so that the data is locked throughout the summer vacation. The implementation of pessimistic locks generally depends on the locking mechanism provided by the database (recommended tutorial: MySQL tutorial)

In the database, the process of pessimistic locking is as follows

Before modifying any record, try to add an exclusive lock to the record

If locking fails, the record is being modified, and the current query may have to wait or throw an exception

If the lock is successful, the record can be modified and unlocked after the transaction is completed

In the meantime, if there are other operations to modify the record or add an exclusive lock, it will wait for us to unlock or directly throw an exception.

Use pessimistic lock in MySQL InnoDB

To use pessimistic locks, you must turn off the autocommit property of the mysql database, because MySQL defaults to autocommit mode, that is, when you perform an update operation, MySQL will submit the results immediately

/ / start transaction begin;/begin work;/start transaction; (choose one of the three) select status from t_goods where id=1 for update;// generate order insert into t_orders (id,goods_id) values (null,1) based on commodity information; / / modify commodity status to 2update t_goods set status=2;// commit transaction commit;/commit work

In the above query statement, the select...for update mode is used to realize the pessimistic lock by opening the exclusive lock. The corresponding record is locked and other transactions must wait for this transaction to be committed before it can be executed.

We use select... For update locks the data, but we need to pay attention to some lock levels. MySQL InnoDB defaults to row-level locks. Row-level locks are index-based. If a SQL does not need an index, it will not use row-level locks. Table-level locks will be used to lock the entire table.

Characteristics

It provides guarantee for the security of data processing.

In terms of efficiency, the mechanism of handling locking will incur additional overhead on the database and increase the chance of deadlock.

In read-only transactions, there is no conflict and there is no need to use locks, which will increase the system load and reduce parallelism

Optimistic lock

Optimistic concurrency control is also a method of concurrency control.

Assuming that multi-user concurrent transactions do not affect each other, each transaction can process that part of the data affected by each other without producing a lock. Before committing the data update, each transaction will first check whether other transactions modify the data after the transaction has read the data, and if so, roll back the transaction that is committing.

Compared with the pessimistic lock, the optimistic lock assumes that the data will not conflict, so when the data is submitted for update, the conflict of the data will be formally detected. If the conflict is found, the user error information will be returned and the user will decide what to do.

Optimistic lock implementations generally use the record version number to add a version ID to the data, and update the version ID when the data is updated

Realize

When using a version number, you can specify a version number during data initialization, and each update to the data performs a + 1 operation on the version number. And determine whether the current version number is the latest version number of the data.

1. Query the commodity information select (status,status,version) from t_goods where id=# {id} 2. Generate order 3 according to commodity information. Modify the commodity status to 2update t_goods set status=2,version=version+1where id=# {id} and version=# {version}; characteristics

Optimistic concurrency control believes that the probability of data competition between transactions is small, so do it as directly as possible and lock it until commit, so there are no locks or deadlocks.

Thank you for reading! About MySQL optimistic lock and pessimistic lock what is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, you can share it and let more people see it.

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.