Why doesn’t SQLite appear to lock my table / row?

I’m tinkering with an in-memory sqlite database, trying to wrap my head around concurrency. Here’s an example using SQLAlchemy

from sqlalchemy import create_engine, text

# Make the engine
engine = create_engine("sqlite+pysqlite:///:memory:", future=True, echo=True)

# Do stuff
with engine.connect() as conn1, engine.connect() as conn2:
    conn1.execute(text("CREATE TABLE leagues (id INTEGER PRIMARY KEY, name VARCHAR(255) NOT NULL);"))
    conn1.execute(text("INSERT INTO leagues (name) VALUES ('A')"))

    conn1.execute(text("UPDATE leagues SET name = name || '1'"))  # append 1 to the name
    conn2.execute(text("UPDATE leagues SET name = name || '2'"))  # append 2 to the name
    conn1.execute(text("UPDATE leagues SET name = name || '1'"))  # append 1 to the name

    conn1.commit()
    conn2.commit()

    result = conn2.execute(text("SELECT name FROM leagues"))
    print(result.all())

OUTPUT

2021-10-27 16:19:25,472 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-10-27 16:19:25,473 INFO sqlalchemy.engine.Engine CREATE TABLE leagues (id INTEGER PRIMARY KEY, name VARCHAR(255) NOT NULL);
2021-10-27 16:19:25,473 INFO sqlalchemy.engine.Engine [generated in 0.00058s] ()
2021-10-27 16:19:25,474 INFO sqlalchemy.engine.Engine INSERT INTO leagues (name) VALUES ('A')
2021-10-27 16:19:25,474 INFO sqlalchemy.engine.Engine [generated in 0.00026s] ()
2021-10-27 16:19:25,474 INFO sqlalchemy.engine.Engine UPDATE leagues SET name = name || '1'
2021-10-27 16:19:25,474 INFO sqlalchemy.engine.Engine [generated in 0.00008s] ()
2021-10-27 16:19:25,475 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-10-27 16:19:25,475 INFO sqlalchemy.engine.Engine UPDATE leagues SET name = name || '2'
2021-10-27 16:19:25,475 INFO sqlalchemy.engine.Engine [generated in 0.00013s] ()
2021-10-27 16:19:25,475 INFO sqlalchemy.engine.Engine UPDATE leagues SET name = name || '1'
2021-10-27 16:19:25,475 INFO sqlalchemy.engine.Engine [cached since 0.0005372s ago] ()
2021-10-27 16:19:25,475 INFO sqlalchemy.engine.Engine COMMIT
2021-10-27 16:19:25,475 INFO sqlalchemy.engine.Engine COMMIT
2021-10-27 16:19:25,475 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-10-27 16:19:25,475 INFO sqlalchemy.engine.Engine SELECT name FROM leagues
2021-10-27 16:19:25,475 INFO sqlalchemy.engine.Engine [generated in 0.00011s] ()
[('A121',)]
2021-10-27 16:19:25,475 INFO sqlalchemy.engine.Engine ROLLBACK

Here, I use two connections to write data to the same record. I would expect the output to be “A112”, because I thought the first connection would have a lock on the record until it COMMITs, but to my surprise the output is “A121”.

Does SQLite actually not lock the table or am I misunderstanding what’s happening? (Would I get the same result with another database like PostgreSQL?)

Answer

I’d actually expect the code to error out if you have multiple connections since you’re creating a deadlock. You’re running into an implementation quirk of SQLAlchemy.

From the documentation:

class sqlalchemy.pool.SingletonThreadPool(creator, pool_size=5, **kw)

A Pool that maintains one connection per thread.

[…]

SingletonThreadPool is used by the SQLite dialect automatically when a memory-based database is used.

In other words, when you open a :memory: database, rather than using SQLite’s shared cache mode that would enable sharing a memory database between different connections, SQLAlchemy creates a single connection and funnels all connections objects through it.

You can see the same behavior on a file connection by passing poolclass=SingletonThreadPool to create_engine, or you can see the error with a memory database using multiple connections to the same database outside of SQLAlchemy:

import sqlite3

db1 = sqlite3.connect("file::memory:?cache=shared", uri=True)
db2 = sqlite3.connect("file::memory:?cache=shared", uri=True)

conn1 = db1.cursor()
conn2 = db2.cursor()

conn1.execute("CREATE TABLE leagues (id INTEGER PRIMARY KEY, name VARCHAR(255) NOT NULL);")
conn1.execute("INSERT INTO leagues (name) VALUES ('A')")

conn1.execute("UPDATE leagues SET name = name || '1'")
# The next line will error out, since the db is locked by conn1
conn2.execute("UPDATE leagues SET name = name || '2'")