首页 » 信息技术 »

sqlite3的事务模式

2020年10月16日 / 3次阅读
SQLite

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

sqlite> insert into mod2 values (6,6,6);
Run Time: real 0.076 user 0.000768 sys 0.000456

这条insert会立即commit。

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

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,是一样的。

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 事务,后启动的那个会失败。

sqlite> begin immediate;
Run Time: real 0.000 user 0.000059 sys 0.000047
Error: database is locked

即不能有多个write transaction。如果一个deferred事务中执行了insert,这个事务也是write transaction,其它客户端再执行 begin immediate | exclusive ,都会失败。提示 database is locked。

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一旦开始,其它客户端都不能连接。

连接sqlite3数据库:不是指用.open打开这个动作,而是执行sql语句。

已经很明显了,操作sqlite数据库,在并发场景下,互斥和同步,是要程序自己去完成的!

本文链接:https://www.maixj.net/ict/sqlite3-trans-24037

相关文章

留言区


前一篇:
后一篇:
-->只要几分钟回答问卷,就能轻松赚现金!

栏目精选

云上小悟,麦新杰的独立博客

Ctrl+D 收藏本页

栏目


©Copyright 麦新杰 Since 2014 云上小悟独立博客版权所有 备案号:苏ICP备14045477号-1。云上小悟网站部分内容来源于网络,转载目的是为了整合信息,收藏学习,服务大家,有些转载内容也难以判断是否有侵权问题,如果侵犯了您的权益,请及时联系站长,我会立即删除。

go to top