mcp server for sqlite, agent keeps writing queries that lock the db
built an mcp server that lets the agent run SELECT queries on a sqlite db (read-only, thank god). works great for simple queries but the agent keeps writing stuff like: SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE...) and it locks the db for like 5-10 seconds because the dataset is 2M rows and sqlite is single-threaded. tried adding a timeout but then the agent just retries the same slow query three times in a row. anyone else hit this? do i just need to teach it to write better queries in the prompt or is there a way to make sqlite less pathetic at concurrent reads lol
set a statement_timeout in your connection string, like 5s. agent learns real fast to write better queries when they start timing out
we did the timeout thing too but the agent just started writing even worse queries that finished under the limit but returned garbage. ended up having to add a row count check and reject anything over 10k rows before it even runs
lol had this exact problem in 2019 with a different kind of agent. set busy_timeout=5000 in your connection string and switch to WAL mode, makes a huge difference. also make sure you're not holding transactions open longer than you need 🙂
+1 on WAL mode, it's a lifesaver. we also added a read replica for the agent to query and it cut lock conflicts by like 90%. are you running read-only queries or does the agent need write access?