Adeniyi Olanrewaju

Posted on Oct 23

Beyond Database Locks: Managing Concurrent User Transactions

#database #api #backend #python

The Simultaneous Update Anomaly

Imagine this nightmare scenario:

Your user, Sarah, sells a product for $100.
Two buyers pay her at the exact same moment.
Two "credit alert" webhooks hit your server simultaneously.
Both payments are for the same amount, to the same user.

What happens?

Worker 1 checks Sarah's balance: $0.
Worker 2 also checks Sarah's balance: $0.
Worker 1 adds $100. New balance: $100.
Worker 2 adds $100. New balance: $100.

Result: Sarah only received $100 instead of $200! Money has vanished.

This isn't a multiple-user problem. This is a single-user concurrency problem, and it's every payment processor's worst nightmare. Your database's with_for_update() lock is useless here because the two transactions are operating on separate connections from different workers.

Why Your Database Can't Save You

Think of your database row lock as a lock on Sarah's account file in a filing cabinet.

Worker 1 walks up, opens the cabinet, and takes out Sarah's file.
Worker 2 walks up at the same time, opens the same cabinet, and takes out the same file.
Both make copies, update them, and put them back.

They never knew the other was there! This is the chaos of concurrent systems.

The Solution: The "One-Operation-Per-User" Lock

We need to put a "BUSY" sign on Sarah herself. Not just her file, but on the very idea of processing her transaction. We need to ensure that for any critical operation on a user, only one request is processed at a time.

Let me show you the exact weapon we need, a distributed user lock.

Code Deep Dive: Building the User Traffic Cop

Here's how we implement a lock that works across all your servers:

from threading import Lock
from .responses import http_response_400
from functools import wraps
from apis.utils import get_redis_counter

lock = Lock()
top_up_locks = {}

def one_deposit_at_a_time(f):
    @wraps(f)
    def decorated_function(*args, **kwargs):
        from apis.crud import get_user_by_account_number
        # get account number from request
        payload = request.get_json()
        user = get_user_by_account_number(payload["account_number"])
        user_id = user.id
        key = f"one_top_up:{user_id}"
        with lock:
            if key not in top_up_locks:
                top_up_locks[key] = Lock()
            top_up_lock = top_up_locks[key]
        with top_up_lock:
            # This is to get the counter to check if it exist (increment = False)
            one_top_up = get_redis_counter(key, ex_time=5, increment=False)
            if one_top_up:
                return http_response_400(
                    {
                        'status': 'failed',
                        'msg': 'One transaction at a time pls'
                    }
                )
            # This is to set the counter and set it to be released in 5 secs
            get_redis_counter(key, ex_time=5, increment=True)
            return f(*args, **kwargs)
    return decorated_function

Let's break down the example provided, step-by-step.

Step 1: The Tools We Need

We use two main tools to build our distributed lock:

A Global Dictionary (top_up_locks): This stores our lock objects for each user.
A Master Lock (lock): This is a safety lock to make sure we don't create two locks for the same user at the same time, a lock for our locks!

from threading import Lock
lock = Lock() # The master lock
top_up_locks = {} # The locker where we store all user locks

Step 2: The "Decorator" - A Reusable Shield

The code uses a decorator called @one_deposit_at_a_time. Think of a decorator as a wrapper. It lets you add extra logic (like a lock) to any function without changing the function's own code.

def one_deposit_at_a_time(f): # 'f' is the original function
    @wraps(f)
    def decorated_function(*args, **kwargs):
        # ... OUR LOCKING LOGIC GOES HERE ...
        # Once the lock is acquired, we run the original function
        return f(*args, **kwargs)
    return decorated_function

Step 3: Creating a Unique Key for Each User

Every user needs their own personal lock. We don't want to lock everyone because one user is making a deposit! We create a unique key based on the user's ID.

payload = request.get_json()
user = get_user_by_account_number(payload["account_number"])
user_id = user.id
key = f"one_top_up:{user_id}" # e.g., "one_top_up:user_789"

Step 4: The Two-Step Locking Dance

This is the genius part. We perform two locking steps to be perfectly safe.

Step 4a: Get (or Create) the User's Personal Lock

We use the master lock to safely look inside our global dictionary.

With the master lock held, we check: "Does a lock for user_789 already exist?"
If not, we create a new Lock() object and store it in the dictionary.
We then immediately release the master lock. We are now only dealing with the one user's lock.

with lock: # Hold the master lock very briefly
    if key not in top_up_locks:
        top_up_locks[key] = Lock() # Create a new lock for this user
    top_up_lock = top_up_locks[key] # Get a reference to it

Step 4b: Acquire the User's Personal Lock

Now we try to acquire the lock for user_789.

with top_up_lock: will wait here if another request for the same user is already holding the lock.
Once the first request is done, this one will acquire the lock and proceed.
This ensures that for this user, requests are processed one after the other, even across multiple workers.

with top_up_lock: # This waits here if the lock is already held
    # Now we are safe to check the database and perform the operation

What Happens Now When Two Payments Arrive:

TIMELINE:

T=0s: Payment A and Payment B arrive simultaneously for Sarah
T=0.1s: Payment A acquires the lock for user_sarah_123
T=0.1s: Payment B tries to acquire the lock → FAILS! Gets "please wait" message
T=1.1s: Payment A finishes processing, releases the lock
T=1.2s: Payment B automatically retries, acquires the lock, processes successfully
T=2.3s: Payment B finishes

Result: Sarah gets $200 correctly! The second payment was simply asked to wait its turn.

Conclusion: From Chaos to Control

The double deposit scenario reveals a fundamental truth: database transactions alone cannot protect you from distributed system problems. You need application-level coordination.

By implementing a simple user-based distributed lock, you transform your system from one that loses money under pressure to one that calmly queues transactions and processes them with perfect accuracy.

Next time two credit alerts arrive at once, your system will handle them with the grace of a seasoned traffic director: "You first, then you." No panic, no lost money, just rock-solid reliability.

DEV Community