Implementing Distributed Read‑Write Locks with MySQL

1. Business Scenario

For a resource X, two operations exist: W (write) and R (read). The operations run on distributed nodes, W must be exclusive (only one writer at a time), R can be shared (multiple readers), and W and R are mutually exclusive.

In plain terms, if a W operation is in progress, any incoming R request must wait or fail, and vice‑versa.

On a single JVM this could be solved with ReadWriteLock , but the challenge is when the actors are on different JVMs across nodes.

Example from a P2P lending platform: a borrower repays a loan (W operation) and investors may transfer their claim (R operation). To keep the financial data consistent, these two actions must be mutually exclusive.

2. Solution Idea

MySQL guarantees that concurrent UPDATE statements on the same row are serialized using internal mutexes. We can exploit this to implement a read‑write lock.

2.1 Create the lock table

create table t_read_write_lock(
    resource_id varchar(50) primary key not null comment 'mutual‑exclusive resource id',
    w_count int not null default 0 comment 'number of active W operations',
    r_count int not null default 0 comment 'number of active R operations',
    version bigint not null default 0 comment 'version, incremented on each update'
);

The three important columns are:

resource_id : identifier of the locked resource (e.g., loan id).

w_count : current number of active write locks.

r_count : current number of active read locks.

2.2 W‑operation process

1. Query t_read_write_lock by resource_id; if not exists, insert a row (resource_id is PK).
2. If w_count == 0 && r_count == 0 proceed, otherwise abort.
3. Acquire lock:
   {
       3.1 Begin transaction
       3.2 int count = (update t_read_write_lock set w_count=1 where r_count=0)
       3.3 Commit transaction
   }
4. If count == 1 continue, else abort.
5. Execute business logic.
6. Release lock:
   {
       6.1 Begin transaction
       6.2 update t_read_write_lock set w_count=0 where w_count=1
       6.3 Commit transaction
   }

If the system crashes after step 5, the write lock remains set, causing a dead‑lock for subsequent reads.

2.3 Adding a lock‑log table

create table t_lock_log(
    id bigint primary key auto_increment comment 'PK',
    resource_id varchar(50) not null comment 'mutual‑exclusive resource id',
    lock_type smallint default 0 comment '0: W lock, 1: R lock',
    status smallint default 0 comment '0: acquired, 1: business done, 2: released',
    create_time bigint default 0 comment 'creation timestamp',
    version bigint not null default 0 comment 'optimistic‑lock version'
);

The log records the lock acquisition and its lifecycle, enabling safe recovery.

2.4 Improved W‑operation with logging

1. Query/insert lock record as before.
2. Check w_count == 0 && r_count == 0.
3. Acquire lock:
   {
       3.1 Begin transaction
       3.2 int count = (update t_read_write_lock set w_count=1 where r_count=0)
       3.3 If count == 1 insert into t_lock_log(resource_id, lock_type, status, create_time) values (#{resource_id}, 0, 0, CURRENT_TIME)
       3.4 Commit transaction
   }
4. If count != 1 abort.
5. Execute business logic, then update the log status to 1 (business done) with optimistic‑lock check on status=0.
6. Release lock and set log status to 2.
   {
       6.1 Begin transaction
       6.2 update t_read_write_lock set w_count=0 where w_count=1 and resource_id=#{resource_id}
       6.3 update t_lock_log set status=2 where id=#{log_id}
       6.4 Commit transaction
   }

2.5 Dead‑lock handling job

If a lock remains unreleased for more than 10 minutes, a background job scans t_lock_log for records with status 0 or 1 whose create_time is older than 10 minutes, then safely releases the corresponding lock using optimistic‑lock checks.

1. SELECT * FROM t_lock_log WHERE status IN (0,1) AND create_time + 600000 < CURRENT_TIME;
2. For each record:
   {
       2.1 Begin transaction
       2.2 if lock_type == 0 then
               update t_lock_log set status=2 where id=#{id} and version=#{version};
               update t_read_write_lock set w_count=0 where w_count=1 and resource_id=#{resource_id};
           else
               update t_lock_log set status=2 where id=#{id} and version=#{version};
               update t_read_write_lock set r_count=r_count-1 where r_count-1>=0 and resource_id=#{resource_id};
       2.3 Commit transaction
   }

2.6 R‑operation process

1. Query/insert lock record.
2. Ensure w_count == 0.
3. Acquire lock:
   {
       3.1 Begin transaction
       3.2 int count = (update t_read_write_lock set r_count=r_count+1 where w_count=0)
       3.3 If count == 1 insert into t_lock_log(resource_id, lock_type, status, create_time) values (#{resource_id}, 1, 0, CURRENT_TIME)
       3.4 Commit transaction
   }
4. If count != 1 abort.
5. Execute business logic, then set log status to 1 with optimistic‑lock check.
6. Release lock:
   {
       6.1 Begin transaction
       6.2 update t_read_write_lock set r_count=r_count-1 where r_count-1>=0 and resource_id=#{resource_id}
       6.3 update t_lock_log set status=2 where id=#{log_id}
       6.4 Commit transaction
   }

3. Summary

The article demonstrates how to implement a distributed read‑write lock using MySQL tables, a lock‑log table, and a periodic cleanup job to avoid dead‑locks, providing a practical solution that can be wrapped in Spring AOP for generic use.