When Real-Time Systems Stall: Debugging Database Locks in FastAPI

Building real-time systems is a balancing act. You're constantly pushing for speed and responsiveness, often juggling hundreds of concurrent connections. I learned this firsthand while working on a real-time attendance management system. This system used FastAPI and WebSockets to track GPS-verified student attendance, aiming for sub-50ms sync for over 300 concurrent users. It worked great, mostly.

Then, the complaints started rolling in.

The Problem

Users reported intermittent issues. Sometimes, their attendance status wouldn't update immediately. Other times, their WebSocket connection would drop for a few seconds, reconnect, and then finally show their updated status. This wasn't a constant problem, but it definitely flared up during peak times – specifically, right at the start of a class when a large number of students would all try to log in and get their attendance marked simultaneously.

That sub-50ms sync we were proud of? It wasn't holding up consistently when it mattered most.

The Investigation

My first instinct was to blame the usual suspects in a real-time system: the WebSocket server, network infrastructure, or perhaps even Nginx configuration. I dove into the logs:

• Nginx Access Logs: No significant errors indicating connection failures at the proxy level.
• FastAPI/Gunicorn Logs: Nothing immediately jumped out regarding WebSocket connection errors or application crashes. The server itself seemed stable.
• System Metrics: CPU, memory, and network usage on the Linux VPS were elevated during peak times, but not critically saturated.

However, deeper in the FastAPI application logs, I started noticing something specific. Occasional sqlalchemy.exc.TimeoutError or OperationalError messages began appearing. These weren't causing the entire application to crash, but they correlated with the times users reported issues. The errors pointed directly to database operations failing or taking too long.

This shifted my focus. The problem wasn't the WebSockets themselves, or the network. It was the database struggling to keep up under concurrent write load.

The Root Cause

The TimeoutError messages were the smoking gun. Our system used SQLAlchemy with a PostgreSQL backend. During peak attendance marking, multiple WebSocket handlers would try to update student locations and attendance records almost simultaneously. While PostgreSQL is robust, our application's interaction with it was creating contention.

Here's what was happening:

The attendance marking process involved a few steps within a single conceptual transaction:

1. Fetching student data: SELECT * FROM students WHERE id = X
2. Updating student's last seen location and timestamp: UPDATE students SET last_seen_latitude = ..., last_seen_longitude = ..., last_seen_timestamp = ... WHERE id = X
3. Checking for an active session and if attendance was already marked: SELECT * FROM sessions WHERE ... and SELECT * FROM attendance_records WHERE student_id = X AND session_id = Y
4. Inserting a new attendance record if needed: INSERT INTO attendance_records (...) VALUES (...)

When many students tried to do this at once, step 2 – the UPDATE on the students table – was the biggest culprit. Even though it was a row-level lock, if hundreds of different student rows were being updated, and then subsequent checks and inserts were happening, the default transaction isolation level and the sequence of operations meant locks were held longer than necessary. Other transactions would then queue up, waiting for locks to release, eventually timing out and causing the intermittent connection drops as the WebSocket handler failed to complete its database operation in time.

The Solution

The fix involved two main strategies: minimizing the scope of transactions and leveraging more atomic database operations.

First, I looked at the most frequent write: updating a student's last_seen_location and last_seen_timestamp. This is a constant stream of updates for active users. It's important, but it doesn't need to be part of the same transaction as the critical attendance marking logic. We could update this with a lightweight, fire-and-forget approach.

Second, for the attendance marking itself, which absolutely required transactional integrity, I needed to ensure it was as efficient and concurrency-safe as possible.

Here's how I refactored the database interactions:

1. Atomic Location Updates

Instead of fetching the Student object, modifying its attributes, and then committing, which involves loading the object into memory and potentially holding a lock for longer, I switched to a direct UPDATE statement using SQLAlchemy's session.execute.

# models.py (Simplified)
from sqlalchemy import Column, Integer, String, Float, DateTime, ForeignKey, Boolean
from sqlalchemy.orm import declarative_base, Session
from datetime import datetime

Base = declarative_base()

class Student(Base):
    __tablename__ = "students"
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String)
    last_seen_latitude = Column(Float)
    last_seen_longitude = Column(Float)
    last_seen_timestamp = Column(DateTime, default=datetime.utcnow)

class AttendanceRecord(Base):
    __tablename__ = "attendance_records"
    id = Column(Integer, primary_key=True, index=True)
    student_id = Column(Integer, ForeignKey("students.id"), unique=False) # Unique constraint handled at DB level
    session_id = Column(Integer, unique=False) # Unique constraint handled at DB level
    marked_at = Column(DateTime, default=datetime.utcnow)
    is_present = Column(Boolean, default=False)

    # Add a unique constraint to prevent duplicate attendance records for a student in a session
    __table_args__ = (UniqueConstraint('student_id', 'session_id', name='_student_session_uc'),)

# app.py (Simplified WebSocket handler logic)
from sqlalchemy import update, insert
from sqlalchemy.exc import IntegrityError
from sqlalchemy.dialects.postgresql import insert as pg_insert
from sqlalchemy import UniqueConstraint # For model definition

# ... (dependency injection for db: Session, etc.)

async def handle_location_and_attendance(student_id: int, lat: float, lon: float, db: Session):
    # Step 1: Update last_seen_location atomically
    # This is a lightweight, non-blocking update that holds locks for minimal time.
    try:
        db.execute(
            update(Student)
            .where(Student.id == student_id)
            .values(
                last_seen_latitude=lat,
                last_seen_longitude=lon,
                last_seen_timestamp=datetime.utcnow()
            )
        )
        db.commit()
    except Exception as e:
        db.rollback()
        print(f"Error updating student {student_id} location: {e}")
        return

    # Step 2: Check and mark attendance in a separate, robust transaction
    await check_and_mark_attendance(student_id, lat, lon, db)


async def check_and_mark_attendance(student_id: int, lat: float, lon: float, db: Session):
    # In a real system, this would involve complex geofencing logic
    # For simplicity, assume we determine an active session ID here.
    active_session_id = get_active_active_session_id(db, student_id, lat, lon)

    if active_session_id is None:
        return # No active session, nothing to mark

    try:
        # Use INSERT ... ON CONFLICT (UPSERT) for atomic attendance marking.
        # This handles concurrent attempts to mark attendance for the same student/session
        # without explicit SELECTs that could lead to race conditions.
        # This example uses PostgreSQL's specific INSERT syntax via pg_insert.
        # For other DBs, you might need a different approach or conditional insert.
        insert_stmt = pg_insert(AttendanceRecord).values(
            student_id=student_id,
            session_id=active_session_id,
            marked_at=datetime.utcnow(),
            is_present=True
        )

        on_conflict_stmt = insert_stmt.on_conflict_do_nothing(index_elements=['student_id', 'session_id'])

        db.execute(on_conflict_stmt)
        db.commit()
        print(f"Student {student_id} marked present for session {active_session_id}")

    except IntegrityError:
        db.rollback() # Should not happen with on_conflict_do_nothing, but good practice
        print(f"Student {student_id} already marked present for session {active_session_id}")
    except Exception as e:
        db.rollback()
        print(f"Error marking attendance for student {student_id}: {e}")

# Helper function (simplified for blog post)
def get_active_active_session_id(db: Session, student_id: int, lat: float, lon: float) -> int | None:
    # This would involve querying a 'sessions' table and comparing coordinates
    # with session geofences. For this example, we'll return a fixed ID.
    # Ensure this lookup is also optimized with appropriate indexes.
    return 1 # Assume session ID 1 is currently active

2. INSERT ... ON CONFLICT for Attendance

For attendance marking, the INSERT ... ON CONFLICT DO NOTHING (often called UPSERT) pattern in PostgreSQL was crucial. By defining a unique constraint on (student_id, session_id) in the AttendanceRecord model, I could attempt an insert. If a record already existed for that student and session, the ON CONFLICT DO NOTHING clause would simply skip the insert, preventing duplicate records and race conditions without requiring an explicit SELECT query first. This significantly reduced the window for contention.

After these changes, the TimeoutError messages vanished from the logs. The system maintained its sub-50ms sync even during peak times. Users reported no more connection drops or delayed updates. The fix worked.

Lessons Learned

1. The Database is Often the Bottleneck: Even with highly performant, asynchronous frameworks like FastAPI and WebSockets, the database remains a synchronous point of contention. It's often the first place to look when a real-time system struggles under load.
2. Transaction Scope Matters: Keep your database transactions as short and focused as possible. Don't bundle unrelated operations into a single transaction if they don't share the same atomicity requirements. Separate frequent, lightweight updates from less frequent, critical ones.
3. Atomic Operations are Powerful: SQLAlchemy's ability to execute direct UPDATE and INSERT statements, especially with features like synchronize_session=False or ON CONFLICT, can be far more efficient for high-frequency operations than loading objects, modifying them, and then committing. It minimizes the time locks are held.
4. Understand Database Concurrency: Knowing your database's locking mechanisms, transaction isolation levels, and features like UPSERT is critical. They are powerful tools for building resilient, high-performance applications that can handle concurrent access without falling over.