Bug report

In our tests we are using in-memory SQLite databases with a shared cache. This makes it possible to create multiple connections with the same connection string and see the same data, as recommended in In-Memory Databases. After updating from Python 3.10 to 3.11 our tests started failing.

The minimal reproduction case:

import sqlite3
import pytest

def write_value():
    with sqlite3.connect("file:test?mode=memory&cache=shared", uri=True) as db:
        db.execute("insert into Test (value) values ('hello')")


@pytest.fixture
def db():
    conn_1 = sqlite3.connect("file:test?mode=memory&cache=shared", uri=True)
    conn_1.execute("create table Test (value text, unique(value))")
    yield write_value
    conn_1.close()


def test_hello_1(db):
    db()


def test_hello_2(db):
    db()

This works in 3.10 (and worked in 3.8 and 3.9), but fails in 3.11:

ERROR sqlite-connection-close.py::test_hello_2 - sqlite3.OperationalError: table Test already exists

The SQLite documentations includes:

The database is automatically deleted and memory is reclaimed when the last connection to the database closes.

We used a workaround mentioned in #97641 for now (calling gc.collect()).
I opened a new issue, since that one looks similar, but is Windows specific, while this one happens on Linux as well.

Your environment

• CPython versions tested on: 3.10.10, 3.11.3
• Operating system and architecture: both on Ubuntu 22.04 (from deadsnakes PPA) and Arch (AUR)
• SQLite versions: 3.37.2 and 3.41.2