add retry for db

2 files changed, 51 insertions, 3 deletions

diff --git a/server/services/get_data.py b/server/services/get_data.py
index 3014ea1..fbbf12f 100644
--- a/server/services/get_data.py
+++ b/server/services/get_data.py
@@ -4,6 +4,8 @@ from db import SessionLocal
 from models.market import MarketDataDB
 from gridstatus import ISONE, MISO, NYISO
 from typing import Dict, Type
+from sqlalchemy.exc import OperationalError
+import time
 
 MARKET_CLASSES: Dict[str, Type] = {
     "ISONE": ISONE,
@@ -108,7 +110,21 @@ def update_market_data():
                 )
                 db.add(entry)
 
-    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 Exception("Database is locked, and retries exhausted.")
+            else:
+                raise e
+
     db.close()
 
 
diff --git a/server/services/process_bids.py b/server/services/process_bids.py
index 40a31cd..edac780 100644
--- a/server/services/process_bids.py
+++ b/server/services/process_bids.py
@@ -5,6 +5,8 @@ from models.bid import Bid
 from models.auth import User
 from gridstatus import ISONE, NYISO, MISO
 from zoneinfo import ZoneInfo
+from sqlalchemy.exc import OperationalError
+import time
 
 MARKET_ISOS = {
     "ISONE": ISONE(),
@@ -87,7 +89,22 @@ def process_bids():
                 bid.pnl = None
                 print(f"Bid {bid.id}: Failed (price too low)")
 
-            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 Exception(
+                                "Database is locked, and retries exhausted."
+                            )
+                    else:
+                        raise e
 
         except Exception as e:
             print(f"Error processing submitted bid {bid.id}: {e}")
@@ -122,7 +139,22 @@ def process_bids():
             bid.pnl = pnl
             print(f"Bid {bid.id} Settled | PnL: ${pnl:.2f}")
 
-            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 Exception(
+                                "Database is locked, and retries exhausted."
+                            )
+                    else:
+                        raise e
 
         except Exception as e:
             print(f"Error settling executed bid {bid.id}: {e}")