Examples to explain optimistic lock and pessimistic lock in MySQL

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

Main technical means of optimistic lock and pessimistic lock concurrency control

Pessimistic lock

In relational database management system, pessimistic concurrency control (pessimistic lock, PCC) is a method of concurrency control. It can prevent 1 transaction from modifying data in a way that affects other users. If a lock is applied to each row of data in an operation performed by a transaction, then 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 intense and where the cost of using locks to protect data in case of concurrency conflicts is lower than that of rolling back transactions

Pessimistic lock, which refers to the conservative attitude (pessimism) that the data is modified by the outside world (including other current transactions of the system and transactions from the external system), so the data is locked during the whole summer processing. The realization of pessimistic lock, 1 generally depends on the lock mechanism provided by the database (recommended tutorial: MySQL tutorial)

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

Try to place an exclusive lock on any record before modifying it If the lock 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 the lock will be unlocked after the transaction is completed In the meantime, if there are other operations to modify the record or add exclusive lock, we will wait for us to unlock or throw an exception directly

Use of pessimistic locks in MySQL InnoDB

To use pessimistic locking, you must turn off the auto-commit property of the mysql database, because MySQL defaults to autocommit mode, which means that when you perform an update operation, MySQL will submit the result immediately

// Start a transaction 

begin;/begin work;/start transaction; ( 3 Person selection 1 A) 

select status from t_goods where id=1 for update;

// Generate orders based on commodity information 

insert into t_orders (id,goods_id) values (null,1);

// Modify merchandise status For 2

update t_goods set status=2;

//  Commit transaction 

commit;/commit work;

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

We use select... for update will lock the data, but we need to pay attention to 1 lock level, MySQL InnoDB default row level lock. Row-level locks are all index-based. If an SQL does not use an index, row-level locks will not be used, and table-level locks will be used to lock the whole table.

Characteristic

It provides a guarantee for the safety of data processing

In efficiency, because of the mechanism of handling locking, the database will generate extra overhead and increase the chance of deadlock

Because there are no conflicts in read-only transactions and there is no need to use locks, this 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 when they are processed, each transaction can process the affected part of the data without generating a lock. Before committing the data update, each transaction checks whether other transactions modify the data after the transaction reads the data, and if so, rolls back the committed transaction

Compared with pessimistic lock, optimistic lock assumes that data will not conflict, so when the data is submitted and updated, it will formally detect whether the data conflicts or not. If the conflict is found, it will return user error information and let the user decide what to do

Optimistic lock realizes 1-like use of record version number, adds 1 version identification to data, and updates the version identification when updating data

Realization

When using version numbers, you can specify 1 version number when the data is initialized, and the +1 operation is performed on the version number every time the data is updated. And judge 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 orders based on commodity information

3. Modify the product status to 2

update t_goods

set status=2,version=version+1

where id=#{id} and version=#{version};

Characteristic

Optimistic concurrency control believes that the probability of data contention between transactions is small, so it goes ahead as far as possible and does not lock until commit, so it will not produce any locks and deadlocks