Using Pessimistic and Optimistic Locks to Prevent Over‑Issuance in a Red‑Packet System

Overview

The previous article implemented the red‑packet feature with SSM + MySQL but suffered from over‑issuance under high concurrency. This article introduces a pessimistic‑lock solution to fix the logical error and evaluates data consistency and performance.

Over‑Issuance Problem Analysis

When multiple users grab a red packet simultaneously, each thread reads the same stock value update T_RED_PACKET set stock = stock - 1 where id = #{id} , causing the stock to be decremented multiple times and leading to over‑issuance.

Database‑Lock Solutions

Using Pessimistic Lock (FOR UPDATE)

Thread 1 queries the red‑packet with an exclusive lock: select id, user_id as userId, amount, send_date as sendDate, total, unit_amount as unitAmount, stock, version, note from T_RED_PACKET where id = #{id} for update

It then executes the business logic (decreaseRedPacket and grabRedPacket), updates the stock, and commits the transaction.

Thread 2 attempts the same query; if Thread 1 has not released the lock, Thread 2 waits.

Thread 3 behaves like Thread 2, and so on.

Using Optimistic Lock (Version Field)

Add a version column (or timestamp) to the red‑packet table.

Thread 1 reads the record, then updates it with a conditional statement: update T_RED_PACKET set stock = stock - 1, version = version + 1 where id = #{id} and version = #{version}

If the update fails, it means another thread has modified the row; the operation can be retried.

Summary

1. Pessimistic lock guarantees exclusive access but reduces throughput; optimistic lock is preferable when read contention is high.

2. Optimistic lock may fail repeatedly under heavy write load; in such cases, pessimistic lock provides stronger consistency.

Pessimistic Lock – Conceptual Description

A pessimistic lock assumes data conflicts and acquires a lock for every operation, relying on the database’s built‑in locking mechanisms (shared lock S and exclusive lock X).

Shared Lock (S‑lock)

Multiple transactions can acquire a shared lock on the same resource, allowing reads but blocking writes until all shared locks are released.

select * from table lock in share mode;

Exclusive Lock (X‑lock)

An exclusive lock permits a single transaction to read and write the resource; other transactions are blocked entirely. Adding for update to a query acquires an X‑lock.

select * from table for update;

Code Refactoring

Analysis

To avoid affecting the previous version, a new DAO method and MyBatis mapper are added. The service layer only switches to the new DAO method; no additional service interface is required.

New DAO Interface Method

/**
 * Retrieve red‑packet information with pessimistic lock.
 * @param id – red‑packet id
 * @return RedPacket object
 */
public RedPacket getRedPacketForUpdate(Long id);

Mapper XML Configuration

<!-- Query red‑packet with FOR UPDATE -->
<select id="getRedPacketForUpdate" parameterType="long" resultType="com.artisan.redpacket.pojo.RedPacket">
    select id, user_id as userId, amount, send_date as sendDate, total, unit_amount as unitAmount, stock, version, note
    from T_RED_PACKET
    where id = #{id} for update
</select>

The for update clause acquires a row‑level exclusive lock, ensuring that only one transaction can modify the record at a time.

Service Layer Invocation

The service now calls redPacketDao.getRedPacketForUpdate(id) before performing the grab logic.

Data Restoration and Testing

After resetting the data in T_RED_PACKET and T_USER_RED_PACKET , the application is started and accessed via http://localhost:8080/ssm_redpacket/grap.jsp .

Statistical Report – Consistency Check

SELECT a.id, a.amount, a.stock
FROM T_RED_PACKET a
WHERE a.id = 1
UNION ALL
SELECT max(b.user_id), sum(b.amount), count(*)
FROM T_USER_RED_PACKET b
WHERE b.red_packet_id = 1;

The query confirms that the total amount distributed matches the remaining stock, indicating that over‑issuance has been eliminated.

Performance Data

SELECT (
    UNIX_TIMESTAMP(max(a.grab_time)) - UNIX_TIMESTAMP(min(a.grab_time))
) AS lastTime
FROM T_USER_RED_PACKET a;

Performance testing shows that without locks, 20,000 red packets are grabbed in 190 seconds (with occasional over‑issuance). With the pessimistic lock, the same workload takes about 275 seconds, highlighting the trade‑off between consistency and throughput.

Notes

Using pessimistic lock on a low‑spec host caused the processing time to increase from 190 s to 275 s. Excessive locking can degrade database performance, so the lock strategy should match the business scenario.

Investigation of Performance Degradation

When a thread holds a lock, other threads are suspended, consuming CPU resources for context switching. In high‑concurrency scenarios, many threads repeatedly block and resume, leading to significant overhead.

This blocking behavior is why pessimistic locks often result in poorer performance compared to optimistic locks, which avoid such thread suspension.

Code Repository

Full source code: https://github.com/yangshangwei/ssm_redpacket

PS: If you find this sharing useful, feel free to like and repost.