The Mystery of the Multiple Emails: A Reactive Java Surprise

During my work with asynchronous Java, specifically using Mono and Flux, I came across an unexpected behavior when triggering an email service. I noticed that the user was receiving the same email three times. After some investigation, I realized this was because we had three different environments — development, staging, and production — all of which were asynchronously updating the database. As a result, before the database check could confirm the state, the email was triggered multiple times from each environment, leading to multiple emails for the user (three in this case).

The Solution: Implementing a Database Locking Mechanism

To solve this problem, I implemented a Database Locking Mechanism that ensures that the email service triggers only once, regardless of the environment. This approach involves creating a lock system within the database to control when the email job is executed. Here is how I designed and implemented the solution:

Step 1: Creating the Lock Table

The first step was to create a table named job_lock that would keep track of which jobs are currently locked. This table helps ensure that only one instance can proceed with a job at a given time.

The table was created as follows:

• job_name: This field uniquely identifies the job that is being executed. It ensures that the same job cannot be executed by multiple instances at the same time.
• locked: This boolean field indicates whether the job is currently locked (i.e., being processed).

Step 2: Implementing the JobLockService

The core of the solution is the JobLockService class, which is responsible for managing the locking mechanism. The service uses methods to acquire, release, and handle rollbacks of locks, leveraging reactive programming principles to handle asynchronous execution.

Method 1: acquireLock(String jobName)

This method is used to acquire a lock for a specific job.

• Acquire Connection: A connection is created from the connectionFactory.
• Begin Transaction: The connection begins a transaction to ensure that all subsequent operations are performed atomically.
• Lock Acquisition (SELECT FOR UPDATE): The SELECT * FROM job_lock WHERE job_name = $1 FOR UPDATE statement is executed to attempt to acquire a lock. The FOR UPDATE clause ensures that only one instance can select and lock the row for a given job, thus preventing race conditions.
• Insert New Lock Record: If no existing lock record is found, a new lock record is inserted (INSERT INTO job_lock) with the job name and a locked status of true.
• Release Connection: The connection is closed after the operation.

Method 2: releaseLock(String jobName)

This method releases a lock once the job is completed successfully.

• Acquire Connection: A connection is created from the connectionFactory.
• Begin Transaction: The connection begins a transaction to ensure atomicity.
• Delete Lock Record: The lock record for the given job name is deleted from the job_lock table.
• Commit Transaction: The transaction is committed, ensuring that the lock release is finalized.
• Release Connection: The connection is closed after the operation.

Method 3: releaseLockAndRollback(String jobName)

This method is used to release a lock and roll back a transaction in case of errors during job execution.

• Acquire Connection: A connection is created from the connectionFactory.
• Rollback Transaction: The transaction is rolled back, undoing any changes that may have been made during the failed job execution.
• Delete Lock Record: The lock record for the given job name is deleted to ensure that other instances can proceed with the job.
• Release Connection: The connection is closed after the operation.

Key features of this approach:

1. Database Locking: When an instance attempts to acquire the lock, it executes a SELECT FOR UPDATE statement on the job_lock table. This locks the selected row, ensuring that only one instance can acquire the lock at a time, thus avoiding race conditions.
2. Unique Constraint: The job_lock table has a unique constraint on the job_name field. This prevents multiple instances from acquiring a lock for the same job. If another instance attempts to insert a lock record while the lock is already held, the database raises a unique constraint violation error, ensuring safety.
3. Deadlock Scenario Solution: In case of errors or deadlocks, the releaseLockAndRollback method ensures that the lock is released and the transaction is rolled back. This prevents deadlocks and ensures that the system remains consistent.

By implementing this Database Locking Mechanism using reactive programming, I was able to solve the issue of the email being sent three times across different environments. The locking mechanism ensured that only one instance could trigger the email service at any given time, preventing duplicate emails. This solution also provided safeguards against race conditions and ensured proper handling of deadlocks, making the system more robust and reliable.