python sqlite "BEGIN TRANSACTION"和 "COMMIT"命令

Question

如果我想通过 python 在我的数据库中启动一个事务，我是否必须像这样显式地执行 sql 命令“BEGIN TRANSACTION”:

import sqlite3

conn = sqlite3.connect(db)
c = conn.cursor()

c.execute('BEGIN TRANSACTION;')
##... some updates on the database ...
conn.commit() ## or c.execute('COMMIT'). Are these two expressions the same?

当我建立连接或当我开始事务时，数据库是否被锁定以防止来自其他客户端的更改，或者两者都没有？

Answer 1

只有事务才会锁定数据库。

然而，Python 试图变得聪明并且 automatically begins transactions :

By default, the sqlite3 module opens transactions implicitly before a Data Modification Language (DML) statement (i.e. INSERT/UPDATE/DELETE/REPLACE), and commits transactions implicitly before a non-DML, non-query statement (i. e. anything other than SELECT or the aforementioned).

So if you are within a transaction and issue a command like CREATE TABLE ..., VACUUM, PRAGMA, the sqlite3 module will commit implicitly before executing that command. There are two reasons for doing that. The first is that some of these commands don’t work within transactions. The other reason is that sqlite3 needs to keep track of the transaction state (if a transaction is active or not).

You can control which kind of BEGIN statements sqlite3 implicitly executes (or none at all) via the isolation_level parameter to the connect() call, or via the isolation_level property of connections.

If you want autocommit mode, then set isolation_level to None.

Otherwise leave it at its default, which will result in a plain “BEGIN” statement, or set it to one of SQLite’s supported isolation levels: “DEFERRED”, “IMMEDIATE” or “EXCLUSIVE”.