SQLite的事务模式

2020年11月2日 / 5次阅读 / Last Modified 2020年11月2日
SQLite

sqlite3在执行SQL语句的时候,如果不是显示的包含在transaction内(即begin...commit),就是auto commit状态。即,每一条DML语句都会默认立即commit。有人也说这是sqlite3的隐式事务,而用begin开始用commit结束的,是显示事务。

注意python自带的sqlite3模块接口,不是这样的,必须要调用commit接口。

sqlite3有3种事务模式:DEFERRED, IMMEDIATE, or EXCLUSIVE.

DEFERRED

DEFERRED means that the transaction does not actually start until the database is first accessed. Internally, the BEGIN DEFERRED statement merely sets a flag on the database connection that turns off the automatic commit that would normally occur when the last statement finishes. This causes the transaction that is automatically started to persist until an explicit COMMIT or ROLLBACK or until a rollback is provoked by an error or an ON CONFLICT ROLLBACK clause. If the first statement after BEGIN DEFERRED is a SELECT, then a read transaction is started. Subsequent write statements will upgrade the transaction to a write transaction if possible, or return SQLITE_BUSY. If the first statement after BEGIN DEFERRED is a write statement, then a write transaction is started.

DEFERRED表示直到commit才真正启动transaction,或者rollback取消。

transaction就是事务,分两种:read or write transaction。

DEFFERED是默认的,写成 begin 或 begin deffered,是一样的。python sqlite3模块的接口默认就是这种模式。

IMMEDIATE

IMMEDIATE cause the database connection to start a new write immediately, without waiting for a write statement. The BEGIN IMMEDIATE might fail with SQLITE_BUSY if another write transaction is already active on another database connection.

两个客户端如果同时开始 begin immediate 事务,后启动的那个会失败。即不能同时有多个write transaction。如果一个deferred事务,执行到了insert,这个事务也变成了write transaction,其它客户端再执行 begin immediate | exclusive ,都会失败。提示 database is locked。

EXCLUSIVE

EXCLUSIVE is similar to IMMEDIATE in that a write transaction is started immediately. EXCLUSIVE and IMMEDIATE are the same in WAL mode, but in other journaling modes, EXCLUSIVE prevents other database connections from reading the database while the transaction is underway.

exclusive最厉害,这样的transaction一旦开始,其它客户端都不能连接。

连接sqlite数据库,不是指用open打开一个数据库,而是执行SQL语句。

使用SQLite数据库,需要应用程序自己去考虑并发时的同步互斥操作。

-- EOF --

本文链接:https://www.pynote.net/archives/2706

留言区

电子邮件地址不会被公开。 必填项已用*标注


前一篇:
后一篇:

More


©Copyright 麦新杰 Since 2019 Python笔记

go to top