In the following code, conn1 first obtains a PENDING lock on the database but fails to write (because conn2 is holding a SHARED lock).

However, when conn2 commit()s, conn1 releases its PENDING lock.

Why is this the case?

import sqlite3

db = "file:db?mode=memory&cache=shared"

conn = sqlite3.connect(db)
conn1 = sqlite3.connect(db)
conn2 = sqlite3.connect(db)

# Initialize
conn.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);")
conn.commit()

conn1.execute("BEGIN")
conn2.execute("BEGIN")
conn1.execute("SELECT * FROM users")
conn2.execute("SELECT * FROM users")
# conn1/conn2 have SHARED locks now

# Will fail
try:
    conn1.execute("INSERT INTO users (name) VALUES (?)", ("conn1",))
except Exception as e:
    print('conn1: failed to insert:',e)

# conn1 now has a PENDING lock, nobody else can read

try:
    conn.execute("SELECT * FROM users")
except Exception as e:
    print('conn: failed to select:')

# conn2 still has its shared lock, so it can read
conn2.execute("SELECT * FROM users")

# Strangely, if conn2 calls commit(), conn1 drops its PENDING Lock
conn2.commit()

# Why did conn1 drop its PENDING lock? Note: nothing was written
print(conn.execute("SELECT * FROM users").fetchall())