WAL & retry

2 files changed, 26 insertions, 3 deletions

diff --git a/server/api/bids.py b/server/api/bids.py
index c08c74b..c9d2ebd 100644
--- a/server/api/bids.py
+++ b/server/api/bids.py
@@ -1,5 +1,7 @@
 from fastapi import APIRouter, Depends, HTTPException
 from sqlalchemy.orm import Session
+from sqlalchemy.exc import OperationalError
+import time
 from db import SessionLocal
 from models.bid import Bid as BidModel
 from models.auth import User
@@ -111,6 +113,23 @@ def submit_bid(bid: BidCreate, db: Session = Depends(get_db)):
         pnl=None,
     )
     db.add(db_bid)
-    db.commit()
+    max_retries = 3
+
+    for attempt in range(max_retries):
+        try:
+            db.commit()
+            break
+        except OperationalError as e:
+            if "database is locked" in str(e).lower():
+                if attempt < max_retries - 1:
+                    time.sleep(2)
+                    continue
+                else:
+                    raise HTTPException(
+                        status_code=500,
+                        detail="Database is busy. Please try again later.",
+                    )
+            else:
+                raise
     db.refresh(db_bid)
     return db_bid
diff --git a/server/db.py b/server/db.py
index a73d08f..221d240 100644
--- a/server/db.py
+++ b/server/db.py
@@ -1,9 +1,13 @@
-from sqlalchemy import create_engine
+from sqlalchemy import create_engine, text
 from sqlalchemy.orm import sessionmaker, declarative_base
 
 # SQLite DB
 DATABASE_URL = "sqlite:///./virtual_energy.db"
 
-engine = create_engine(DATABASE_URL, connect_args={"check_same_thread": False})
+engine = create_engine(
+    DATABASE_URL, connect_args={"check_same_thread": False, "timeout": 30}
+)
+with engine.connect() as conn:
+    conn.execute(text("PRAGMA journal_mode=WAL;"))
 SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
 Base = declarative_base()