Python sqlite3 的事务控制

官方文档的描述：

Controlling 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, thesqlite3 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 pysqlite 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.

(译文：你能控制sqlite3默认执行的BEGIN语句类型（或者什么类型都不），这是通过设置connect()函数的isolation_level 参数，或connection对象的isolation_level属性实现的。)

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

(译文：如果想使用自动提交模式，设置isolation_level为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”.

(译文：不设置isolation_level（使用默认）将会执行朴素的BEGIN语句（即下文sql语句图中，BEGIN或BEGIN TRANSACTION），或者设置为“DEFERRED”, “IMMEDIATE” 或“EXCLUSIVE”(即BEGIN DEFERRED/IMMEDIATE/EXCLUSIVE TRANSACTION）。）

isolation_level控制的是什么

从上文我们看到，这个变量与BEGIN语句的模式有关，可选值为 “None“，“空“(不设置)，“DEFERRED”, “IMMEDIATE” ,“EXCLUSIVE”

设置为None即自动提交，即每次写数据库都提交。
官网文档前两段写的是智能提交，在某些语句自动开启事务，执行某些语句前自动commit。

后边四个是什么？

后来我找到sqlite官方的这个图：
https://www.sqlite.org/lang_transaction.html 
https://grox.net/doc/sqlite/lang_transaction.html

注：原文中begin-stmt应该是begin-statement的缩写

这是描述sql语句语法的图，即：

BEGIN TRANSACTION
BEGIN
BEGIN DEFERRED TRANSACTION
BEGIN DEFERRED
...

情况就明了了，isolation_level决定开启事务时使用的是BEGIN TRANSACTION, BEGIN DEFERRED TRANSACTION, BEGIN IMMEDIATE TRANSACTION, BEGIN EXCLUSIVE TRANSACTION中的那种。

注：这几种类型的意义http://sqlite.1065341.n5.nabble.com/WAL-difference-between-IMMEDIATE-and-DEFERRED-transaction-td86669.html
https://www.sqlite.org/lockingv3.html#excl_lock

我是这么认为的，immediate 是begin语句处获得PENDING锁，deferred是获取RESERVED锁，update，delete，insert等写语句出现时才获得PENDING锁，exclusive是获取EXCLUSIVE排他锁

isolation_level为None是开启自动commit功能，非None是设置BEGIN的类型，开启智能commit。

我理解为：1.BEGIN是自动开启的 2.设为None每次写数据库都会自动commit 3.设为其他会在某些语句前自动commit，其他地方想立即commit要手动执行。

例子来了！

智能commit

import sqlite3con = sqlite3.connect(":memory:")cur = con.cursor()cur.execute("create table people (num, age)")num = 1 age = 2 * num while num <= 1000000:    cur.execute("insert into people values (?, ?)", (num, age))    num += 1    age = 2 * num cur.execute("select count(*) from people")print cur.fetchone()

保存为test.py执行之

# time python test.py
(1000000,)

real    0m6.537s
user    0m6.440s
sys     0m0.086s

自动commit

import sqlite3con = sqlite3.connect(":memory:",isolation_level=None)cur = con.cursor()cur.execute("create table people (num, age)")num = 1 age = 2 * num while num <= 1000000:    cur.execute("insert into people values (?, ?)", (num, age))    num += 1    age = 2 * num cur.execute("select count(*) from people")print cur.fetchone()

 执行之

# time python test.py
(1000000,)

real    0m10.693s
user    0m10.569s
sys     0m0.099s

智能commit用时6秒，自动commit用时10秒 （例子是写内存，如果写文件速度会更慢，建议改为写100条数据）

智能commit
优点：速度快，单进程情况下运行良好
缺点：多个控制流并发操作数据库时，这边写完了，另一边可能读不出来
克服缺点：每次写完数据，手动执行commit

自动commit
优点：每次写数据库都能保证确实写入了，防止并发操作数据库时出现逻辑问题
缺点：太慢了！！！
克服缺点：批量操作前手动BEGIN TRANSACTION，操作后手动COMMIC

克服缺点的例子

智能commit时实现即时commit

# coding:utf-8import sqlite3con = sqlite3.connect(":memory:")cur = con.cursor()cur.execute("create table people (num, age)")num = 1 age = 2 * num while num <= 1000000:    cur.execute("insert into people values (?, ?)", (num, age))    con.commit()  # 关键在这里    num += 1    age = 2 * num cur.execute("select count(*) from people")print cur.fetchone()

time python test.py
(1000000,)

real    0m20.797s
user    0m20.611s
sys     0m0.156s

自动commit时阻止即时commit

# coding:utf-8import sqlite3con = sqlite3.connect(":memory:",isolation_level=None)cur = con.cursor()cur.execute("create table people (num, age)")num = 1 age = 2 * num cur.execute("BEGIN TRANSACTION") # 关键点while num <= 1000000:    cur.execute("insert into people values (?, ?)", (num, age))    num += 1    age = 2 * num cur.execute("COMMIT")  #关键点cur.execute("select count(*) from people")print cur.fetchone()

 # time python test.py
(1000000,)

real    0m6.649s
user    0m6.555s
sys     0m0.076s

这次，智能commit用时20秒（性能下降很多），自动commit用时6秒 ，完全反过来了

注：sqlite API中有个executemany()函数，和循环execute是一样的，自动commit情况下执行慢的话，需要手动开启事务，提交commit。
http://stackoverflow.com/questions/35013453/apsw-or-sqlite3-very-slow-insert-on-executemany