Транзакции с Python sqlite3

Ответ 1

API-интерфейс Python DB пытается быть умным, а начинается и совершает транзакции автоматически.

Я бы рекомендовал использовать драйвер DB, который не использует API-интерфейс Python, например apsw.

Ответ 2

Для тех, кто хотел бы работать с библиотекой sqlite3 независимо от ее недостатков, я обнаружил, что вы можете сохранять некоторый контроль над транзакциями, если будете выполнять эти две вещи:

1. set Connection.isolation_level = None (согласно документам это означает режим автоматической фиксации)
2. вообще избегайте использования executescript, потому что согласно документам он "сначала выдает оператор COMMIT" - то есть проблемы. На самом деле я обнаружил, что это мешает любым вручную установленным транзакциям

Итак, для меня работает следующая адаптация вашего теста:

import sqlite3

sql = sqlite3.connect("/tmp/test.db")
sql.isolation_level = None
c = sql.cursor()
c.execute("begin")
try:
    c.execute("update test set i = 1")
    c.execute("fnord")
    c.execute("update test set i = 0")
    c.execute("commit")
except sql.Error:
    print("failed!")
    c.execute("rollback")

Ответ 3

Per документы,

Объекты подключения могут использоваться в качестве контекстных менеджеров, которые автоматически совершать или откатывать транзакции. В случае исключения, транзакция откатывается; в противном случае транзакция совершается:

Поэтому, если вы позволите Python выйти из оператора with при возникновении исключения, транзакция будет отброшена назад.

import sqlite3

filename = '/tmp/test.db'
with sqlite3.connect(filename) as conn:
    cursor = conn.cursor()
    sqls = [
        'DROP TABLE IF EXISTS test',
        'CREATE TABLE test (i integer)',
        'INSERT INTO "test" VALUES(99)',]
    for sql in sqls:
        cursor.execute(sql)
try:
    with sqlite3.connect(filename) as conn:
        cursor = conn.cursor()
        sqls = [
            'update test set i = 1',
            'fnord',   # <-- trigger error
            'update test set i = 0',]
        for sql in sqls:
            cursor.execute(sql)
except sqlite3.OperationalError as err:
    print(err)
    # near "fnord": syntax error
with sqlite3.connect(filename) as conn:
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM test')
    for row in cursor:
        print(row)
        # (99,)

дает

(99,)

как ожидалось.

Ответ 4

Здесь я думаю, что это происходит, основываясь на моем чтении связок Python sqlite3, а также официальных документах Sqlite3. Короткий ответ заключается в том, что если вы хотите иметь правильную транзакцию, вы должны придерживаться этой идиомы:

with connection:
    db.execute("BEGIN")
    # do other things, but do NOT use 'executescript'

Вопреки моей интуиции, with connection не вызывает BEGIN при вводе области. Фактически это ничего не делает в __enter__. Это действует только тогда, когда вы __exit__ область, выбираете либо COMMIT, либо ROLLBACK в зависимости от того, выходит ли область из обычного или с исключением.

Поэтому правильная вещь - всегда указывать начало транзакций с помощью BEGIN. Это делает isolation_level нерелевантным в транзакциях, потому что, к счастью, он имеет эффект только в включен режим автосохранения, а режим автосохранения всегда подавляется в блоках транзакций.

Другой quirk - executescript, который всегда выдает COMMIT перед запуском script. Это может легко испортить транзакции, поэтому ваш выбор - либо

• используйте только одну executescript внутри транзакции и ничего больше или
• полностью избегать executescript; вы можете вызвать execute столько раз, сколько хотите, с учетом ограничения одного оператора за execute.

Ответ 5

Вы можете использовать соединение в качестве менеджера контекста. Затем он автоматически откатит транзакции в случае исключения или передаст их в противном случае.

try:
    with con:
        con.execute("insert into person(firstname) values (?)", ("Joe",))

except sqlite3.IntegrityError:
    print("couldn't add Joe twice")

См. https://docs.python.org/3/library/sqlite3.html#using-the-connection-as-a-context-manager

Ответ 6

Обычный .execute() работает, как ожидалось, с комфортным режимом автоматической фиксации по умолчанию и менеджером контекста with conn: ..., выполняющим автоматическое фиксацию ИЛИ отката - за исключением защищенного read-modify-write транзакции, которые объясняются в конце этого ответа.

Модуль sqlite3 нестандартный conn_or_cursor.executescript() не участвует в режиме автоматической фиксации (по умолчанию) (и поэтому не работает нормально с диспетчером контекста with conn: ...), но перенаправляет script скорее raw. Поэтому он просто совершает потенциально ожидающие транзакции автоматической фиксации при запуске, прежде чем "перейдет в исходное состояние".

Это также означает, что без "BEGIN" внутри script executescript() работает без транзакции и, таким образом, опция отката при ошибке или иначе.

Итак, с executescript() мы лучше используем явное BEGIN (так же, как создание вашей инициальной схемы script для инструмента командной строки sqlite "raw" ). И это взаимодействие показывает шаг за шагом, что происходит:

>>> list(conn.execute('SELECT * FROM test'))
[(99,)]
>>> conn.executescript("BEGIN; UPDATE TEST SET i = 1; FNORD; COMMIT""")
Traceback (most recent call last):
  File "<interactive input>", line 1, in <module>
OperationalError: near "FNORD": syntax error
>>> list(conn.execute('SELECT * FROM test'))
[(1,)]
>>> conn.rollback()
>>> list(conn.execute('SELECT * FROM test'))
[(99,)]
>>> 

script не достиг "COMMIT". И таким образом мы могли бы просмотреть текущее промежуточное состояние и принять решение об откате (или совершить все же)

Таким образом, рабочий откат с помощью excecutescript() выглядит следующим образом:

>>> list(conn.execute('SELECT * FROM test'))
[(99,)]
>>> try: conn.executescript("BEGIN; UPDATE TEST SET i = 1; FNORD; COMMIT""")
... except Exception as ev: 
...     print("Error in executescript (%s). Rolling back" % ev)
...     conn.executescript('ROLLBACK')
... 
Error in executescript (near "FNORD": syntax error). Rolling back
<sqlite3.Cursor object at 0x011F56E0>
>>> list(conn.execute('SELECT * FROM test'))
[(99,)]
>>> 

(Обратите внимание на откат через script здесь, потому что no .execute() взял на себя управление фиксацией)

И здесь примечание о режиме автоматической фиксации в сочетании с более сложной проблемой защищенной транзакции read-modify-write, которая сделала @Jeremie "Из всех многих, многих вещи, написанные об транзакциях в sqlite/python, это единственное, что позволяет мне делать то, что я хочу (иметь эксклюзивную блокировку чтения в базе данных)". в комментарии к примеру, который включал c.execute("begin"). Несмотря на то, что sqlite3 обычно не делает блокировку исключительной блокировки с блокировкой блокировки, за исключением продолжительности фактической записи, но более умные 5-ступенчатые блокировки для обеспечения достаточной защиты от перекрывающихся изменений.

Контекст автоматической фиксации with conn: еще не установлен или не запустил блокировку, достаточно сильную для защищенной read-modify-write в 5-ступенчатая блокировка схема sqlite3. Такая блокировка выполняется неявно только тогда, когда выдается первая команда модификации данных - поэтому слишком поздно. Только явный BEGIN (DEFERRED) (TRANSACTION) запускает желаемое поведение:

Первая прочитанная операция против базы данных создает блокировку SHARED и первая операция записи создает блокировку RESERVED.

Таким образом, защищенная транзакция чтения-изменения-записи, которая использует язык программирования в общем виде (а не специальное предложение SQL SQL UPDATE), выглядит следующим образом:

with conn:
    conn.execute('BEGIN TRANSACTION')    # crucial !
    v = conn.execute('SELECT * FROM test').fetchone()[0]
    v = v + 1
    time.sleep(3)  # no read lock in effect, but only one concurrent modify succeeds
    conn.execute('UPDATE test SET i=?', (v,))

После сбоя такая транзакция read-modify-write может быть повторена несколько раз.

Ответ 7

Это немного старый поток, но если это поможет, я обнаружил, что откат объекта соединения делает свое дело.