When to Use Optimistic vs. Pessimistic Locks in MySQL (and Redis)

Locking strategies in MySQL

MySQL supports two complementary locking models for concurrent data access:

Pessimistic lock assumes conflicts are likely. The row is locked before it is read or updated, preventing other transactions from modifying it until the lock is released. This guarantees consistency for write‑heavy or high‑conflict scenarios but adds lock overhead, can cause deadlocks, and reduces concurrency.

Optimistic lock assumes conflicts are rare. The row is read without a lock, and a version (or timestamp) column is checked when the update is issued. If the version has changed, the update fails and the application must retry. This works well for read‑heavy workloads, improves throughput, and avoids lock contention, at the cost of possible retries and extra CPU.

Implementation in MySQL

Pessimistic lock with InnoDB row‑level locks

BEGIN;  -- start transaction
SELECT number FROM ih_store
WHERE goods_id = '${goods_id}' AND sku_id = '${sku_id}'
FOR UPDATE;  -- acquire exclusive lock on the row
-- check stock, create order, decrement stock
COMMIT;  -- release lock

If the lock cannot be obtained, the statement blocks until the lock is released or an error is raised.

Optimistic lock using a version column

UPDATE ih_store
SET number = number - 1,
    version = version + 1
WHERE id = #{id}
  AND version = #{version};

The update succeeds only when the version read earlier matches the current version; otherwise the statement affects zero rows and the application should retry.

Flash‑sale scenario with a pessimistic lock (PHP)

<?php
include './mysql.php';
function build_order_no(){
    return date('ymd').substr(implode('', array_map('ord', str_split(substr(uniqid(),0,7),13))),0);
}
function insertLog($event,$type=0){
    global $conn;
    $sql = "INSERT INTO ih_log(event,type) VALUES('$event','$type')";
    mysqli_query($conn,$sql);
}
// start transaction
mysqli_query($conn,"BEGIN");
$sql = "SELECT number FROM ih_store WHERE goods_id='$goods_id' AND sku_id='$sku_id' FOR UPDATE";
$rs = mysqli_query($conn,$sql);
$row = $rs->fetch_assoc();
if($row['number']>0){
    $order_sn = build_order_no();
    $sql = "INSERT INTO ih_order(order_sn,user_id,goods_id,sku_id,price) VALUES('$order_sn','$user_id','$goods_id','$sku_id','$price')";
    $order_rs = mysqli_query($conn,$sql);
    $sql = "UPDATE ih_store SET number=number-{$number} WHERE sku_id='$sku_id'";
    $store_rs = mysqli_query($conn,$sql);
    if($store_rs){
        echo '库存减少成功';
        insertLog('库存减少成功');
        mysqli_query($conn,"COMMIT");
    }else{
        echo '库存减少失败';
        insertLog('库存减少失败');
        mysqli_query($conn,"ROLLBACK");
    }
}else{
    echo '库存不够';
    insertLog('库存不够');
    mysqli_query($conn,"ROLLBACK");
}
?>

This approach guarantees that only one transaction can modify the stock row at a time, but under extreme concurrency the waiting threads can exhaust database connections.

Optimistic lock using Redis WATCH

<?php
$redis = new Redis();
$redis->connect('127.0.0.1',6379);
$mywatchkey = $redis->get('mywatchkey');
$rob_total = 100; // total stock
if($mywatchkey <= $rob_total){
    $redis->watch('mywatchkey');          // monitor key
    $redis->multi();                     // start transaction
    $redis->set('mywatchkey',$mywatchkey+1);
    $rob_result = $redis->exec();        // commit if key unchanged
    if($rob_result){
        $redis->hSet('watchkeylist','user_'.mt_rand(1,9999),$mywatchkey);
        echo "抢购成功！";
        echo "剩余数量：".($rob_total-$mywatchkey-1);
        echo "用户列表：<pre>";
        var_dump($redis->hGetAll('watchkeylist'));
    }else{
        $redis->hSet('watchkeylist','user_'.mt_rand(1,9999),'meiqiangdao');
        echo "手气不好，再抢购！";
        exit;
    }
}
?>

Redis WATCH provides a lightweight optimistic‑locking mechanism: the transaction succeeds only if the watched key has not been modified by another client between watch and exec. This eliminates row‑level locking and scales better for high‑traffic flash‑sale events.

Guidelines for choosing a lock

Use pessimistic locks when the workload is write‑heavy, the conflict rate is high, or strict consistency is required.

Use optimistic locks for read‑heavy or high‑concurrency scenarios where conflicts are rare; be prepared to handle retries.

Locks are effective only inside a transaction and require the InnoDB storage engine.

Optimistic locking does not prevent dirty reads; additional isolation levels may be needed.