Repository: Chia-Network/chia-blockchain
File: chia/util/db_wrapper.py (Python)

Code before:
        # SAVEPOINTs. An orphan SAVEPOINT (created but never released) causes
        # all subsequent SAVEPOINTs to nest inside it, making every RELEASE a
        # merge instead of a commit — trapping data in an uncommitted
        # transaction invisible to reader connections.
        #
        # aiosqlite queues SQL synchronously (put_nowait) before awaiting the
        # result, so the SAVEPOINT may be created on the background thread
        # even if our await is cancelled. The except/finally ensures we
        # ROLLBACK/RELEASE regardless.
        #
        # The SAVEPOINT creation itself is NOT shielded from cancellation —
        # protecting it would only delay the inevitable, since the caller's
        # writes after yield are unprotected and would be cancelled anyway.
        # The ROLLBACK/RELEASE cleanup IS protected (via
        # _suppress_task_cancellation) because it must complete to avoid
        # orphan savepoints even when _must_cancel is True.
        try:
            await self._write_connection.execute(f"SAVEPOINT {name}")
            yield
        except:
            try:
                with _suppress_task_cancellation():
                    await self._write_connection.execute(f"ROLLBACK TO {name}")
            except Exception:
                # ROLLBACK may fail if the SAVEPOINT was never created (e.g.
                # CancelledError interrupted execute before aiosqlite ran it).
                pass
            raise
        finally:
            # rollback to a savepoint doesn't cancel the transaction, it
            # just rolls back the state. We need to cancel it regardless
            try:
                with _suppress_task_cancellation():
                    await self._write_connection.execute(f"RELEASE {name}")
            except Exception:
                # RELEASE may fail if the SAVEPOINT was never created.
                pass

    @contextlib.asynccontextmanager
    async def writer(
        self,
        foreign_key_enforcement_enabled: bool | None = None,
    ) -> AsyncIterator[aiosqlite.Connection]:
        """
        Initiates a new, possibly nested, transaction. If this task is already
        in a transaction, none of the changes made as part of this transaction
        will become visible to others until that top level transaction commits.
        If this transaction fails (by exiting the context manager with an
        exception) this transaction will be rolled back, but the next outer
        transaction is not necessarily cancelled. It would also need to exit
        with an exception to be cancelled.

Review comment (bug):
it might be worth pointing out that there is still a failure case we can't handle, when the call to `ROLLBACK TO` is itself cancelled. The only thing we can do is to run it multiple times and hope for the best. Same thing with `RELEASE`

Code after:
        # SAVEPOINTs. An orphan SAVEPOINT (created but never released) causes
        # all subsequent SAVEPOINTs to nest inside it, making every RELEASE a
        # merge instead of a commit — trapping data in an uncommitted
        # transaction invisible to reader connections.
        #
        # aiosqlite queues SQL synchronously (put_nowait) before awaiting the
        # result, so the SAVEPOINT may be created on the background thread
        # even if our await is cancelled. The except/finally ensures we
        # ROLLBACK/RELEASE regardless.
        #
        # The SAVEPOINT creation itself is NOT shielded from cancellation —
        # protecting it would only delay the inevitable, since the caller's
        # writes after yield are unprotected and would be cancelled anyway.
        # The ROLLBACK/RELEASE cleanup IS protected (via
        # _suppress_task_cancellation) because it must complete to avoid
        # orphan savepoints even when _must_cancel is True.
        try:
            await self._write_connection.execute(f"SAVEPOINT {name}")
            yield
        except:
            try:
                with _suppress_task_cancellation():
                    await self._write_connection.execute(f"ROLLBACK TO {name}")
            except sqlite3.OperationalError:
                # Catches "no such savepoint" when the SAVEPOINT was never
                # created (e.g. CancelledError interrupted execute before
                # aiosqlite ran it). All other errors are propagated.
                pass
            raise
        finally:
            # rollback to a savepoint doesn't cancel the transaction, it
            # just rolls back the state. We need to cancel it regardless
            try:
                with _suppress_task_cancellation():
                    await self._write_connection.execute(f"RELEASE {name}")
            except sqlite3.OperationalError:
                # Catches "no such savepoint" when the SAVEPOINT was never
                # created. All other errors are propagated.
                pass

    @contextlib.asynccontextmanager
    async def writer(
        self,
        foreign_key_enforcement_enabled: bool | None = None,
    ) -> AsyncIterator[aiosqlite.Connection]:
        """
        Initiates a new, possibly nested, transaction. If this task is already
        in a transaction, none of the changes made as part of this transaction
        will become visible to others until that top level transaction commits.
        If this transaction fails (by exiting the context manager with an
        exception) this transaction will be rolled back, but the next outer