Understanding Optimistic vs Pessimistic Locks: When and How to Use Them

Explanation

When encountering concurrency, it is necessary to ensure data accuracy, i.e., consistency with the expected result, which requires the use of locks. A lock controls program execution under concurrency to guarantee data changes as expected. Without locking, inconsistent data may occur probabilistically.

Optimistic Lock (CAS)

Overview

Optimistic locking assumes that conflicts are rare and performs lock checking only when updating data, typically by adding a version number that increments on each change; the version is used as a query condition during updates. Whether to use a transaction depends on whether the write operation affects a single row or multiple rows.

Note: Many online solutions use a timestamp as the version field, which is discouraged because timestamps lack sufficient granularity for high‑frequency requests; random strings are preferable.

Usage

# Example
update test set score = score + 1 where id = 1
# Optimized version (may have ABA issue):
select score as old_score from test where id = 1;
update test set score = score + 1 where id = 1 and score = old_score;
# Or add a version field to avoid ABA:
select version from test where id = 1;
update test set score = score + 1 where id = 1 and version = version;

Applicable Scenarios

Read‑heavy, write‑light : Low probability of affecting rows when writes are infrequent.

Allows some retries : Suitable when occasional retries are acceptable; excessive retries can degrade performance.

Advantages

Simple implementation : Can be realized directly in code without extra database operations. No deadlock risk : Unlike pessimistic locks, optimistic locks do not cause deadlocks. Higher performance without retries : In concurrent access, it does not block other transactions, offering better throughput when the business can tolerate occasional write failures.

Disadvantages

Concurrency conflicts : Adds an extra WHERE condition, guaranteeing only that data will eventually be correct, not that every SQL statement affects rows (>0). Lacks strong consistency : Strong consistency requires the data state to be consistent at all times; pessimistic locks verify at the write step, while optimistic locks only filter via WHERE. ABA problem : A value may change from A to B and back to A under high concurrency, making it unclear whether the situation fits business logic; using a dedicated version field that never repeats solves this.

Pessimistic Lock

Overview

Pessimistic locking assumes data will definitely conflict, using MySQL‑level locks. By blocking other transactions, it ensures strong consistency, meaning only one transaction can modify or access shared resources at any time. It requires participation of a transaction.

Usage

Add FOR UPDATE to the query within a transaction.

# If three statements are executed without commit, the following update will block until commit:
update test set score = score + 1 where id = 1;
# Transaction example:
start transaction;
select * from test where id = 1 for update;
update test set score = score + 1 where id = 1;
commit;

Applicable Scenarios

Write‑heavy : When many writes occur and data correctness is paramount, pessimistic locking is appropriate.

Advantages

Strong consistency : Combined with transactions, consistency is guaranteed. Simple implementation : Using FOR UPDATE in a transaction requires minimal developer effort.

Disadvantages

Deadlock risk : Improper use can lead to deadlocks when multiple transactions wait on each other. Lower performance : Locking resources for the entire transaction can cause other transactions to block.

Simulation Implementation

Preparation

# Create a very simple table and insert a row
CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `score` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `test` (`id`, `score`) VALUES (1, 0);

Requirement Simulation

Query the row with id=1 in the test table; if score is 0, increment it, otherwise stop.

Implementation Without Lock

To improve performance, native PDO is used to operate MySQL.

// Connect to database
$pdo = new \PDO("mysql:host=127.0.0.1;port=3306;dbname=temp;", 'root', 'root');
$pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
$pdo->query('set names utf8mb4');

// Query
$query = $pdo->query('select score from test');
$query->setFetchMode(\PDO::FETCH_ASSOC);
$res = $query->fetchALL();

if ($res[0]['score'] == 0) {
    $res = $pdo->exec('update test set score = score + 1 where id = 1');
    var_dump($res);
}

Concurrency Simulation

Using a load‑testing tool with 500 concurrent requests, the score value reached 3, confirming that concurrency caused results to deviate from expectations.

Optimistic Lock Solution (Ignoring ABA Issue)

# Update with optimistic lock ignoring ABA
update test set score = score + 1 where id = 1 and score = 0;

Pessimistic Lock Solution

$pdo = new \PDO("mysql:host=127.0.0.1;port=3306;dbname=temp;", 'root', 'root');
$pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
$pdo->query('set names utf8mb4');

$redis = new Redis;
$redis->connect('127.0.0.1', 6379);

try {
    $pdo->beginTransaction();

    $stmt = $pdo->prepare("select * from test where id = 1 for update");
    $stmt->execute();
    $res = $stmt->fetch(\PDO::FETCH_ASSOC);

    if ($res['score'] == 0) {
        $stmt = $pdo->prepare("UPDATE test SET score = (score + 1) where id  = 1");
        $stmt->execute();
        $pdo->commit();
        $redis->incr('commit');
    } else {
        $redis->incr('rollback');
        $pdo->rollBack();
    }
} catch (PDOException $e) {
    $pdo->rollBack();
}

// Close connection
$pdo = null;

After one round of 500‑thread load testing, the Redis counter showed 1 commit and 499 rollbacks, indicating that only the first transaction succeeded while the others were queued, preventing simultaneous reads of score=0.

Reference: https://www.cnblogs.com/phpphp/p/17983522