Introduction to optimistic locking and pessimistic locking in MySQL (code example)

This article brings you an introduction to optimistic locking and pessimistic locking in MySQL (code examples). It has certain reference value. Friends in need can refer to it. I hope it will help You helped.

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

• The main technical means used in optimistic locking and pessimistic locking concurrency control

Pessimistic lock

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

• Pessimistic concurrency control is mainly used in environments with intense data contention, and when concurrency conflicts occur, the cost of using locks to protect data is lower than the cost of rolling back transactions

pessimistic Locking refers to a conservative attitude (pessimism) towards data being modified by the outside world (including other current transactions of the system and transaction processing from external systems), so the data is locked during the entire summer vacation processing. The implementation of pessimistic locking generally relies 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 the lock fails, it means that the record is being modified , then the current query may have to wait or throw an exception

• If the lock is successfully locked, the record can be modified, and it will be unlocked after the transaction is completed

• If there are other operations to modify the record or add an exclusive lock, they will wait for us to unlock or directly throw an exception

Pessimistic locking is used in MySQL InnoDB

To use pessimistic locking, you must turn off the auto-commit attribute of the mysql database, because MySQL uses autocommit mode by default, that is, when you perform an update operation, MySQL will immediately submit the result

//开始事务
begin;/begin work;/start transaction;（三者选一个）
select status from t_goods where id=1 for update;
//根据商品信息生成订单
insert into t_orders (id,goods_id) values (null,1);
//修改商品status为2
update t_goods set status=2;
// 提交事务
commit;/commit work;

In the above query statement, the select...for update method is used to implement pessimistic locking by turning on exclusive lock. Then the corresponding record is locked, and other transactions must wait for this transaction to be submitted before they can be executed.

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

Features

• Provides guarantee for the security of data processing

• In terms of efficiency, due to the lock processing The mechanism will cause additional overhead to the database and increase the chance of deadlock

• Since there will be no conflicts in read-only transactions, there is no need to use locks, which will increase the system load and reduce the Parallelism

Optimistic lock

• Optimistic concurrency control is also a method of concurrency control.

• Assuming that multi-user concurrent transactions will not affect each other during processing, each transaction can process the part of the data it affects without generating a lock, before submitting the data update , each transaction will first check whether other transactions have modified the data after the transaction reads the data, and if so, roll back the submitting transaction

Optimistic lock relative For pessimistic locking, it is assumed that the data will not conflict, so when the data is submitted for update, the conflict of the data will be officially detected. If a conflict is found, an error message will be returned to the user to let the user decide what to do. Do

• Optimistic lock implementation generally uses record version number, adds a version identification to the data, and updates the version identification when the data is updated

Implementation

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

1.查询出商品信息
select (status,status,version) from t_goods where id=#{id}
2.根据商品信息生成订单
3.修改商品status为2
update t_goods 
set status=2,version=version+1
where id=#{id} and version=#{version};

Features

Optimistic concurrency control believes that the probability of data competition between transactions is small, so do it as directly as possible. The lock is not locked until submission, so no locks or deadlocks will occur

The above is the detailed content of Introduction to optimistic locking and pessimistic locking in MySQL (code example). For more information, please follow other related articles on the PHP Chinese website!