A Brief Analysis of Pessimistic Locking and Optimistic Locking

In relational databases, pessimistic concurrency control (pessimistic locking) and optimistic concurrency control (optimistic locking) are the main solutions adopted for resource concurrency control.

Whether it is pessimistic locking or optimistic locking, these are concepts defined by people and can be considered a kind of thought. In fact, the concepts of optimistic and pessimistic locking are not only present in relational database systems, but also in similar concepts in systems like memcache, hibernate, tair, etc.

Different concurrency control methods should be chosen for different business scenarios. Moreover, they should not be confused with the locking mechanisms provided in the data (row locks, table locks, exclusive locks, shared locks).

Let’s understand pessimistic locks and optimistic locks separately, starting with a simple design of a product table defined as follows:

CREATE TABLE `products` (
	`id` INT unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
	`quantity` INT unsigned COMMENT 'Stock',
	PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Pessimistic Lock#

When we want to modify a piece of data in a database, the best way to avoid simultaneous modifications by others is to directly lock that data to prevent concurrency.

This method of locking the data before modification using the database's locking mechanism is called pessimistic concurrency control (also known as "pessimistic locking," Pessimistic Concurrency Control, abbreviated as "PCC").

This design adopts the "lock, check, update" pattern, which uses the database's built-in select ... for update keyword to add a row-level lock to the data to be operated on before executing the corresponding query and update operation.

BEGIN;
SELECT quantity FROM products WHERE id = 1 FOR UPDATE;
UPDATE products SET quantity = quantity - 1 WHERE id = 1; 
COMMIT;

In MySQL, InnoDB defaults to row-level locking. Row-level locks are index-based; if an SQL statement does not hit an index, it will not use row-level locks and will lock the entire table. Therefore, in select ... for update, try to minimize the query range and hit the index.

Pessimistic concurrency control is mainly used in environments with intense data competition, and in situations where the cost of using locks to protect data during concurrent conflicts is lower than the cost of rolling back transactions.

Optimistic Lock#

Optimistic locking (Optimistic Concurrency Control, abbreviated as "OCC") assumes that data generally will not cause conflicts, so it only checks for conflicts when the data is submitted for updates. If a conflict is found, it returns an error message to the user, allowing the user to decide how to proceed.

Optimistic locking does not use the locking mechanisms provided by the database. A common way to implement optimistic locking is to record the data version. The data version can be implemented using a version number or a timestamp.

SELECT quantity, version FROM products WHERE id = 1;
UPDATE products SET quantity = quantity - 1, version = version + 1
 WHERE id = 1 AND version = original_version_number;

When using a version number, a version number can be specified during data initialization, and each time the data is updated, the version number is incremented by 1. It checks whether the current version number is the latest version of the data.

However, using version numbers in high-concurrency websites means that only one thread can successfully modify the data, leading to many business errors, which is not user-friendly.

Therefore, we can reduce the strength of optimistic locking based on business conditions to maximize throughput and improve concurrency. For example, in the case of ordering products, as long as there is stock, an order can be placed.

UPDATE products SET quantity = quantity - 1 WHERE id = 1 AND quantity - 1 > 0;

Optimistic concurrency control is mostly used in environments with less data competition, where the cost of occasionally rolling back transactions is lower than the cost of locking data while reading, thus achieving higher throughput than other concurrency control methods.

Summary of Differences#

Optimistic locking does not actually lock, making it more efficient. However, if the granularity of the lock is not well controlled, the probability of update failure will be relatively high, leading to business failures.
Pessimistic locking relies on database locks, making it less efficient. The probability of update failure is relatively low.

Reference Links#

Acknowledgments#

Thank you for reading, let’s explore knowledge and grow together.