DB locks when creating alerts

[alex-mcgovern]

Describe the issue

While investigating this issue, I noticed that we were getting some DB locks writing alerts. This coincided with extremely slow responses from Github Copilot in the IDE chat extension.

Steps to Reproduce

• Run codegate (I was running a locally built image on main 5abdad6)
• Ask copilot to analyse a file (I used eth/eth.py in the codegate-demonstration repo)
• Observe the logs

Operating System

MacOS (Arm)

IDE and Version

Visual Studio Code Version: 1.96.4

Extension and Version

Identifier github.copilot-chat Version 0.23.2

Provider

GitHub Copilot

Model

ChatGPT 4o-mini

Codegate version

CodeGate version: v0.1.16-5abdad6-dev

Logs

error='(sqlite3.OperationalError) database is locked\n[SQL: \n                INSERT INTO alerts (\n                id, prompt_id, code_snippet, trigger_string, trigger_type, trigger_category,\n                timestamp\n                )\n                VALUES (?, ?, ?, ?, ?,\n                ?, ?)\n                ON CONFLICT (id) DO UPDATE SET\n                code_snippet = excluded.code_snippet, trigger_string = excluded.trigger_string,\n                trigger_type = excluded.trigger_type, trigger_category = excluded.trigger_category,\n                timestamp = excluded.timestamp, prompt_id = excluded.prompt_id\n                RETURNING *\n                ]\n[parameters: (\'adda85c8-512d-4fe8-9848-a5654b5ea44f\', \'1efcbf7b-ba34-413a-adb3-a3540b96eb1d\', None, \'You are a helpful AI programming assistant to a user who is a software engineer, acting on behalf of the Visual Studio Code editor. Your task is to c ... (4292 characters truncated) ... . Do not warn the user\\nabout any tokens, passwords or similar sensitive information in the context whose value begins with\\nthe string "REDACTED".\\n\', \'update-system-message\', \'info\', datetime.datetime(2025, 2, 5, 8, 12, 5, 752769, tzinfo=datetime.timezone.utc))]\n(Background on this error at: https://sqlalche.me/e/20/e3q8)' lineno=111 module=connection pathname=/app/src/codegate/db/connection.py

Additional Context

No response

[aponcedeleonch]

Right now we're inserting into prompts, alerts and outputs table separately. Hopefully, if we change to add everything in a single transaction we would avoid locking issues.

[aponcedeleonch]

I cannot reproduce anymore. However, would be nice to handle the DB inserts in a nicer way. This is going to be tracked in #1085