Pessimistic locking and optimistic locking in MySQL

In relational databases, pessimistic locking and optimistic locking are solutions to resource concurrency scenarios. Next, we will explain in detail the actual use and advantages and disadvantages of these two concurrency solutions.

First, define the database and make a simple inventory table as follows:

CREATE TABLE `order_stock` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
 `oid` int(50) NOT NULL COMMENT 'Product ID',
 `quantity` int(20) NOT NULL COMMENT 'Inventory',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

Quantity represents the inventory of different commodity oids. Next, OCC and PCC use this database for demonstration.

Optimistic Locking (OCC)

It assumes that multi-user 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 committing data updates, each transaction will first check whether other transactions have modified the data after reading it. If other transactions have updates, the transaction being committed will be rolled back.

That is, "optimistic locking 🔒" believes that the user who takes the lock will most likely succeed, so it is enough to take the lock at the last step of the business operation that actually requires updating the data. This can avoid using the row locks defined by the database itself and avoid deadlocks.

UPDATE order_stock SET quantity = quantity - 1 WHERE oid = 1 AND quantity - 1 > 0;

Optimistic concurrency control is mostly used in environments with little data contention and conflicts. In such environments, the cost of occasionally rolling back a transaction is lower than the cost of locking data when reading it, so higher throughput can be achieved than other concurrency control methods.

Pessimistic locking PCC

It prevents a transaction from modifying data in a way that affects other users. If a transaction performs an operation that reads a row of data and applies a lock, other transactions can only perform operations that conflict with the lock when the transaction releases the lock.

This design adopts the "one lock 🔒 two queries 🔍 three updates" mode, which uses select ... for update keyword in the database to add a row-level lock 🔒 to the current transaction, first lock the data to be operated, and then execute the corresponding query data and perform the update operation.

BEGIN
SELECT quantity FROM order_stock WHERE oid = 1 FOR UPDATE;
UPDATE order_stock SET quantity = 2 WHERE oid = 1; 
COMMIT;

Another problem with MySQL is that all scanned rows will be locked during the execution of the select ... for update statement, which can easily cause problems. Therefore, if you use pessimistic locking in MySQL, make sure to use the index instead of a full table scan.

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

Advantages and disadvantages of OCC and PCC

OCC Advantages and Disadvantages

【advantage】

• Optimistic locking believes that the probability of data race between transactions is relatively small, so it is possible to proceed directly and lock only when committing, so no locks or deadlocks will occur;
• It can respond to transactions quickly, but as the concurrency increases, a large number of rollbacks will occur;
• High efficiency, but the locking force must be controlled.

【shortcoming】

• If you simply do this, you may still encounter unexpected results. For example, two transactions both read a row from the database, and then write it back to the database after modification. This will cause problems.
• As the concurrency increases, a large number of rollbacks will occur.
  

PCC Advantages and Disadvantages

【advantage】

The conservative strategy of "lock first and then access" ensures the security of data processing;

【shortcoming】

• Relying on database locks, which is inefficient;
• The locking mechanism will cause additional overhead to the database and increase the chance of deadlock;
• This reduces parallelism. If a transaction locks a row of data, other transactions must wait for the transaction to be completed before they can process that row of data.

The above is the details of pessimistic locking and optimistic locking in MySQL. For more information about MySQL pessimistic locking and optimistic locking, please pay attention to other related articles on 123WORDPRESS.COM!