Incident Discovery

While managing my personal website, I encountered an unexpected issue where a single user appeared to have completed check-in twice in one day. This occurred during routine monitoring when I noticed duplicate entries in the check-in records.

The system architecture included a daily check-in mechanism with the following components:

• A boolean flag is_sign in user profile indicating daily status
• Scheduled task running at 8 AM to reset all flags to False
• Check-in endpoint verifying flag state before processing

Investigation Process

Initial examination revealed the core logic:

def handle_daily_checkin():
    user_identifier = authenticated_user.id
    
    if authenticated_user.has_checked_in_today:
        return create_response({
            'status': 0,
            'message': 'Already checked in today, return tomorrow at 8 AM'
        })
    else:
        # Process check-in logic
        pass

The system worked by checking the is_sign field on user profiles. Daily cron jobs would reset all value to False, and during check-in, the system would verify this field before executing the check-in process and then update it to True.

Accessing server logs from nginx revealed multiple consecutive POST requests from the same user within milliseconds, suggesting either browser lag or intentional rapid clicking behavior.

Root Cause Analysis

The problem stemmed from concurrent request handling without proper locking mechanisms. Using grequests for testing demonstrated the issue:

import grequests

def test_concurrent_requests():
    endpoints = [
        'http://192.168.48.129/api/sign',
        'http://192.168.48.129/api/sign',
        'http://192.168.48.129/api/sign',
        'http://192.168.48.129/api/sign',
        'http://192.168.48.129/api/sign',
        'http://192.168.48.129/api/sign',
    ]
    
    session_cookies = {'session': 'xxxxxxx'}
    async_requests = (grequests.post(url, cookies=session_cookies, data={'card_id': 1}) for url in endpoints)
    responses = grequests.map(async_requests)
    
    for response in responses:
        print(response.json())

Testing confirmed multiple successful check-ins occurred simultaneously. The reason only four requests succeeded was due to uWSGI configuration with processes = 4, limiting concurrent processing capacity.

The complete check-in sequence involves:

• Creating a check-in record entry
• Updating user credit balance
• Logging credit modification history
• Committing all changes

For different users, concurrent operations pose no issues since each operates on distinct data sets. However, single-user concurrency created the observed duplication problem.

Solution Implemantation

Using Flask-SQLAlchemy's locking capabilities, specifically the with_for_update() method:

from sqlalchemy import select

def handle_daily_checkin():
    user_identifier = authenticated_user.id
    
    # Acquire exclusive lock on user record
    locked_user = user_profile.query.filter_by(id=user_identifier).with_for_update().first()
    
    if locked_user.is_sign:
        return create_response({
            'status': 0,
            'message': 'Already checked in today, return tomorrow at 8 AM!'
        })
    else:
        # Continue with check-in process
        pass

The with_for_update() method implements database-level row locking. Key parameters include:

• read: Determines shared vs exclusive locking (exclusive needed here)
• nowait: Controls whether to wait or fail immediately when encountering locks
• of: Specifies which tables to lock (optional)

Re-running the concurrent test confirmed only the first request succeeds, while subsequent attempts properly detect the locked state and return appropriate error messages.

Technical Insights

This incident enhanced understanding of SQLAlchemy's transaction management and concurrency control mechanisms. Database-level locking proved essential for maintaining data integrity in high-concurrency scenarios involving single-user operations.