Merge ~r00ta/maas:MAASENG-2764 into maas:master

[Jacopo Rota]

feat: set transaction isolation level to REPEATABLE READ in maasapiserver

The transaction level for the django sessions is set to REPEATABLE READ. This MP aims to implement the same strategy for maasapiserver.

Jira link: https://warthogs.atlassian.net/browse/MAASENG-2764

720d802... by Jacopo Rota on 2024-02-23

MAASENG-2764: set transaction isolation level to REPEATABLE READ in maasapiserver

[Björn Tillenius (bjornt)]

+1

review: Approve

[MAAS Lander (maas-lander)]

UNIT TESTS
-b MAASENG-2764 lp:~r00ta/maas/+git/maas into -b master lp:~maas-committers/maas

STATUS: SUCCESS
COMMIT: 720d802632f5cc06960f89fe07e909884efec439

review: Approve

diff --git a/src/maasapiserver/common/db/__init__.py b/src/maasapiserver/common/db/__init__.py
index 128ae7e..b3ca6c2 100644
--- a/src/maasapiserver/common/db/__init__.py
+++ b/src/maasapiserver/common/db/__init__.py
@@ -6,4 +6,6 @@ from maasapiserver.settings import DatabaseConfig
 class Database:
     def __init__(self, config: DatabaseConfig, echo: bool = False):
         self.config = config
-        self.engine = create_async_engine(config.dsn, echo=echo)
+        self.engine = create_async_engine(
+            config.dsn, echo=echo, isolation_level="REPEATABLE READ"
+        )
diff --git a/src/tests/maasapiserver/common/api/test_db.py b/src/tests/maasapiserver/common/api/test_db.py
index 5499504..39bb43b 100644
--- a/src/tests/maasapiserver/common/api/test_db.py
+++ b/src/tests/maasapiserver/common/api/test_db.py
@@ -3,7 +3,16 @@ from typing import AsyncIterator
 from fastapi import Depends, FastAPI
 from httpx import AsyncClient
 import pytest
-from sqlalchemy import Column, insert, Integer, MetaData, select, Table
+from sqlalchemy import (
+    Column,
+    insert,
+    Integer,
+    MetaData,
+    select,
+    Table,
+    Text,
+    update,
+)
 from sqlalchemy.ext.asyncio import AsyncConnection
 
 from maasapiserver.common.api.base import API, Handler, handler
@@ -19,6 +28,7 @@ TestTable = Table(
     "testing",
     METADATA,
     Column("id", Integer, primary_key=True),
+    Column("name", Text),
 )
 
 
@@ -35,7 +45,7 @@ async def insert_app(
         async def success(
             self, conn: AsyncConnection = Depends(db_conn)
         ) -> None:
-            await conn.execute(insert(TestTable).values(id=42))
+            await conn.execute(insert(TestTable).values(id=42, name="default"))
 
         @handler(path="/failure", methods=["GET"])
         async def fail(self, conn: AsyncConnection = Depends(db_conn)) -> None:
@@ -79,3 +89,32 @@ class TestDBSession:
         async with db_connection.begin():
             ids = await db_connection.scalars(select(TestTable.c.id))
         assert list(ids) == []
+
+    async def test_isolation_level(
+        self,
+        insert_client: AsyncClient,
+        db: Database,
+        db_connection: AsyncConnection,
+    ) -> None:
+        response = await insert_client.get("/insert/success")
+        assert response.status_code == 200
+
+        second_db_connection = await db.engine.connect()
+
+        async with db_connection.begin():
+            async with second_db_connection.begin():
+                assert (
+                    await db_connection.execute(
+                        select(TestTable.c.name).where(TestTable.c.id == 42)
+                    )
+                ).first().name == "default"
+                await second_db_connection.execute(
+                    update(TestTable)
+                    .where(TestTable.c.id == 42)
+                    .values(name="new value")
+                )
+            assert (
+                await db_connection.execute(
+                    select(TestTable.c.name).where(TestTable.c.id == 42)
+                )
+            ).first().name == "default"