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 inIn-Memory Databases. After updating from Python 3.10 to 3.11 our tests started failing.

The minimal reproduction case:

importsqlite3importpytestdefwrite_value():withsqlite3.connect("file:test?mode=memory&cache=shared",uri=True)asdb:db.execute("insert into Test (value) values ('hello')")@pytest.fixturedefdb():conn_1=sqlite3.connect("file:test?mode=memory&cache=shared",uri=True)conn_1.execute("create table Test (value text, unique(value))")yieldwrite_valueconn_1.close()deftest_hello_1(db):db()deftest_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 (callinggc.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