為什么SQLite似乎沒有鎖定我的表/行？

我正在修補一個記憶體中的 sqlite 資料庫，試圖將我的頭腦圍繞在并發性上。這是使用 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())

輸出

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

在這里，我使用兩個連接將資料寫入同一條記錄。我希望輸出是“A112”，因為我認為第一個連接會鎖定記錄，直到它提交，但令我驚訝的是輸出是“A121”。

SQLite 實際上沒有鎖定表還是我誤解了正在發生的事情？（使用其他資料庫（如 PostgreSQL）會得到相同的結果嗎？）

uj5u.com熱心網友回復：

如果您有多個連接，我實際上希望代碼出錯，因為您正在創建死鎖。您遇到了 SQLAlchemy 的實作怪癖。

從檔案：

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

每個執行緒維護一個連接的池。

[...]

當使用基于記憶體的資料庫時，SQLite 方言會自動使用 SingletonThreadPool。

換句話說，當您打開一個:memory:資料庫時，SQLAlchemy 不會使用 SQLite 的共享快取模式在不同連接之間共享記憶體資料庫，而是創建一個連接并通過它匯集所有連接物件。

您可以通過傳遞poolclass=SingletonThreadPool到create_engine來查看檔案連接上的相同行為，或者您可以查看使用多個連接到 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'")

標籤：sql sqlite 并发 sqlalchemy

上一篇：根據兩列的值添加或減去值

下一篇：同一列中的AUTOINCREMENT字符和整數