Thread safety: Depcache shared database cursors across Gunicorn threads

[JasonWildMe]

Summary

The dtool depcache (DependencyCache / DependencyCacheTable) shares database cursors across all Gunicorn worker threads. When two threads execute queries concurrently on the same cursor, one thread's results can be silently returned to the other.

Details

Location: wbia/dtool/depcache_table.py (lines 334, 350, 3081)

The depcache uses self.db.cur — a shared cursor object:

self.db.cur.execute(command)
for rowid, cfgdict in self.db.cur.fetchall():
    ...

With 16 concurrent Gunicorn threads sharing the same ibs object (and thus the same depcache and self.db):

1. Thread A: cur.execute(query_for_annotation_chips) → starts fetching results
2. Thread B: cur.execute(query_for_image_thumbs) → overwrites cursor state
3. Thread A: cur.fetchall() → receives Thread B's results

Impact

• Silent data corruption: Wrong query results returned to callers
• Wrong computation results: Depcache may serve incorrect chips, features, or other computed properties
• Hard to reproduce: Only triggers under concurrent load with overlapping depcache queries

Affected Components

• wbia/dtool/depcache_table.py — DependencyCacheTable methods that use self.db.cur
• wbia/dtool/depcache_control.py — DependencyCache orchestration layer
• wbia/dtool/sql_control.py — SQLDatabaseController connection management

Proposed Fix

Use thread-local cursors instead of a shared cursor:

import threading

class SQLDatabaseController:
    def __init__(self, ...):
        self._local = threading.local()

    @property
    def cur(self):
        if not hasattr(self._local, 'cursor'):
            self._local.cursor = self._connection.cursor()
        return self._local.cursor

Alternatively, use SQLAlchemy's scoped_session which provides thread-local sessions out of the box.

Environment

• Gunicorn gthread: 1 worker × 16 threads
• All threads share the same ibs → same depcache → same self.db → same cursor
• SQLite with WAL mode allows concurrent reads but shared cursor negates this