How I Fixed Persistent Account Balance Mismatches with Pessimistic Locks and Hibernate

When taking over a new financial project, repeated account balance mismatches appeared. The root cause was hotspot accounts: multiple services or threads updating the same account record concurrently, causing one update to overwrite another.

Typical Solutions

Single‑service thread lock

Cluster distributed lock

Cluster database pessimistic lock

The project used a database‑level pessimistic lock, so the investigation focused on its correct usage.

What Is a Pessimistic Lock?

A pessimistic lock assumes data will be modified and locks the rows for the duration of a transaction, usually via SELECT ... FOR UPDATE. This works only with InnoDB and must be executed inside a transaction block.

set autocommit=0;
-- start transaction
BEGIN;
-- lock the row
SELECT status FROM t_goods WHERE id=1 FOR UPDATE;
-- create order
INSERT INTO t_orders (id, goods_id) VALUES (NULL, 1);
-- update goods status
UPDATE t_goods SET status=2 WHERE id=1;
-- commit
COMMIT;

Because autocommit is disabled, the transaction is explicitly managed with BEGIN and COMMIT. The FOR UPDATE statement locks the selected row so other transactions must wait.

Initial Bug: Lock After Calculation

Although most code locked the row before calculating the new balance, one place performed the balance calculation outside the lock and only locked the row before the final update. This allowed another thread to overwrite the balance, reproducing the mismatch.

Recurring Issues After a Month

Even after fixing the lock placement, occasional mismatches resurfaced. Investigation revealed two major difficulties.

Difficulty 1: Massive Data Without Indexes

The account table contained tens of millions of rows without an index on the query columns, making full‑table scans impossible. Two SQL‑optimization techniques were applied:

Limit the result set with LIMIT Use efficient pagination by filtering on the primary key

Original inefficient query:

SELECT id, name, age FROM user LIMIT 1000000,20;

Optimized version:

SELECT id, name, age FROM user WHERE id > 1000000 LIMIT 20;

Further improvement using BETWEEN:

SELECT id, name, age FROM user WHERE id BETWEEN 1000000 AND 1000020;

Difficulty 2: Excessive Log Volume

Detailed application logs generated several gigabytes per day. To locate relevant entries, grep was used: grep 123 info.log After narrowing down the timestamp, the matching lines were saved to a temporary file for offline analysis:

grep '2021-11-17 19:23:23' info.log > temp.log

Deeper Bug: Hibernate Session Caching

Even with the corrected lock order, the issue persisted in production. The suspicion turned to Hibernate’s first‑level cache. The deprecated Session.get(..., LockMode) method returns the cached instance if it already exists, bypassing the database lock.

Deprecated Method <code>@Deprecated Object get(Class clazz, Serializable id, LockMode lockMode)</code> LockMode parameter should be replaced with LockOptions

Because the entity was already present in the session, the subsequent lock call operated on a stale instance. Adding session.clear() before loading forced a fresh database fetch.

public T findAndLock(Class cls, String primaryKey) throws DataAccessException {
    Session session = getHibernateTemplate().getSessionFactory().getCurrentSession();
    session.clear();
    Object object = session.load(cls, primaryKey, LockOptions.UPGRADE);
    return (T) object;
}

After inserting the clear operation, the unit test reproduced the race condition, confirming the root cause.

Final Resolution

The production fix replaced the Hibernate‑based query with a native SQL SELECT ... FOR UPDATE that retrieves only the primary key, checks its existence, and then proceeds with the business logic. This eliminates the caching side‑effect and guarantees the lock is held during balance calculation.

Conclusion

The case demonstrates that even a seemingly simple pessimistic lock can involve many layers: transaction isolation, Hibernate caching, Spring transaction management, multithreading, Linux tooling, SQL optimization, and thorough log analysis. Understanding each layer helped locate and eradicate the elusive bug.