Examples of optimistic locking and pessimistic locking in MySQL

The task of concurrency control in a database management system is to ensure that multiple transactions accessing the same data in the database at the same time do not destroy the isolation and unity of the transactions and the unity of the database.

The main technical means used by optimistic locking and pessimistic locking concurrency control

Pessimistic Lock

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

Pessimistic concurrency control is mainly used in environments with intense data contention and where the cost of using locks to protect data when a concurrency conflict occurs is lower than the cost of rolling back the transaction.

Pessimistic locking refers to a conservative (pessimistic) attitude towards data being modified by the outside world (including other current transactions in the system and transaction processing from external systems). Therefore, the data is locked during the entire summer processing process. The implementation of pessimistic locking generally relies on the locking mechanism provided by the database (recommended tutorial: MySQL tutorial)

In the database, the pessimistic locking process is as follows

• Before modifying any record, try to get an exclusive lock on the record.
• If the lock fails, it means that the record is being modified, and the current query may have to wait or throw an exception.
• If the lock is successfully acquired, the record can be modified and the lock will be unlocked after the transaction is completed.
• If there are other operations to modify or add exclusive locks to the record, they will wait for us to unlock it or throw an exception directly.

Using pessimistic locking in MySQL InnoDB

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

//Start transaction begin;/begin work;/start transaction; (choose one of the three)

select status from t_goods where id=1 for update;

//Generate an order based on product information insert into t_orders (id,goods_id) values ​​(null,1);

//Change the product status to 2

update t_goods set status=2;

//Commit transaction commit;/commit work;

In the above query statement, the select...for update method is used, and pessimistic locking is implemented by opening an 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 to lock the data, but we need to pay attention to the lock level. MySQL InnoDB uses row-level locks by default. Row-level locks are all based on indexes. If a SQL statement does not use an index, a row-level lock will not be used, and a table-level lock will be used to lock the entire table.

Features

Provides a guarantee for the security of data processing

In terms of efficiency, the locking mechanism will cause additional overhead to the database, increasing the chance of deadlock.

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

Optimistic Locking

Optimistic concurrency control is also a concurrency control method.

Assume that multiple concurrent transactions will not affect each other during processing, and each transaction can process the part of the data affected by it without generating locks. Before submitting data updates, each transaction will first check whether other transactions have modified the data after the transaction reads the data. If so, the transaction being submitted will be rolled back.

Optimistic locking is different from pessimistic locking. It assumes that data will not conflict. Therefore, when data is submitted and updated, it will formally check whether the data conflicts. If a conflict is found, an error message will be returned to the user, allowing the user to decide what to do.

Optimistic locking generally uses a record version number to add a version identifier to the data, and updates the version identifier when the data is updated.

accomplish

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

1. Query product information

select (status,status,version) from t_goods where id=#{id}

2. Generate orders based on product information

3. Change the product status to 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 contention between transactions is small, so it tries to proceed as directly as possible and does not lock until the transaction is committed, so no locks or deadlocks will occur.