python 与sqlite3

主旨

本文主要是为了加强大家对sqlite3数据库在多线程使用环境下理解。新版本的sqlite3支持多线程（暂时不知如何界定新版本），只要使用check_same_thread=False

正确的使用方法

1.最简单在python 多线程中使用sqlite3的方法是每个线程创建一个数据库的连接。

2.参考SQLAIchemy一样弄个连接池,或者直接使用它。

3.其他方式

写本文的原因

目前维护的kiosk机器端代码，在微信支付时更新支付购物车信息时，总是报错

cannot start a transaction within a transaction 或者 no transaction is active

经仔细查证，发现此处代码如下：

#coding: utf-8import sqlite3import timeimport threadimport threadingdef update_f1(information):    # print information    # for filed in information.keys():    #     print filed    # for key in information:    #     print key    updates = ", ".join(["`" + field + "`" + '=:' + field for field in information.keys() if field != 'name'])    where = ' WHERE name == :name'    values = information    query = 'UPDATE firms SET ' + updates + where    print query    print "exec"    try:        c.execute(query, values)        time.sleep(3)        # raise TypeError, "HEHE"        conn.commit()    except Exception, e:        print e    print "commit"lock = threading.Lock()conn = sqlite3.connect('test.db', isolation_level="IMMEDIATE", timeout=60, check_same_thread=False)c = conn.cursor()# c.execute("create table firms (founded, hq, name, rev, size, type)")# c.execute("insert into firms ( name ) values (?) ", ("bar", ))# conn.commit()thread.start_new_thread(update_f1, (dict(name='bar', founded='1062', rev='7 MILLION DOLLARS!'), ))print c.execute('select * from firms').fetchall()conn.close()time.sleep(1000)

看如上代码，发现在主线程和其他线程都有对数据库的操作,而且使用同一个连接，于是很容易猜测出估计是多线程中commit的问题。

铺垫

isolation in sqlite

sqlite3对sql语句的理解

多线程中使用sqlite

python2.7 sqlite3接口

isolation与读写

在数据库中读和写是利用两个不同的数据库连接，读者只能看到写着commit的事务。不管读者写者是在一个线程，不同的线程还是相同的进程，不同的进程。这是常用和预期的数据库行为。

可以有多个线程同时连接着数据库，而且他们可以写数据库文件，只是必须轮流操作。

isolation与并发

transations 与isolation

sqlite对sql的理解

begin-stmt:

commit-stmt:

rollback-stmt:

在一个事务完成之前，数据库不会做任何改变。除了select语句，其他sql命令会自动的开始一个事务，如果没有已经生效的语句。自动开始的事务会自动commit在最后的query结束时。

事务也可以手动的使用Begin命令启动，这样的事务会保持直到下一个commit或者rollback命令。在数据库关闭时或者错误发生时（具体参考rollbakc冲突规定），会自动的rollback。

END TRANSACTION 就是COMMIT的别名。

事务可以是deffered, immediate,或者exclusive。默认的事务行为是deffered。

Deferred means that no locks are acquired on the database until the database is first accessed. Thus with a deferred transaction, the BEGIN statement itself does nothing to the filesystem. Locks are not acquired until the first read or write operation. The first read operation against a database creates a SHARED lock and the first write operation creates a RESERVED lock. Because the acquisition of locks is deferred until they are needed, it is possible that another thread or process could create a separate transaction and write to the database after the BEGIN on the current thread has executed. If the transaction is immediate, then RESERVED locks are acquired on all databases as soon as the BEGIN command is executed, without waiting for the database to be used. After a BEGIN IMMEDIATE, no other database connection will be able to write to the database or do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE. Other processes can continue to read from the database, however. An exclusive transaction causes EXCLUSIVE locks to be acquired on all databases. After a BEGIN EXCLUSIVE, no other database connection except for read_uncommitted connections will be able to read the database and no other connection without exception will be able to write the database until the transaction is complete.

如果事务类型是immediate，在begin语句开始时，数据库的reserved锁会被马上获取。其他数据库的连接已经无法写数据库或者做一个 BEGIN IMMEDIATE or BEGIN EXCLUSIVE。其他进程只能够从这个数据库读取数据。

显式提交命令立即运行,即使有等待SELECT语句。然而,如果有等待写操作,提交命令将失败错误代码SQLITE_BUSY。

sqlite3多线程模式

• single-thread: 这种模式下，所有的mutexes都无效了并且sqlite在多线程中变得不安全

• multi-thread: 这种模式下，sqlite3可以在多线程中安全的使用，只要不在多个线程中同时使用一个数据库连接

  In this mode, SQLite can be safely used by multiple threads provided that no single database connection is used simultaneously in two or more threads.

• serialized: 这种模式下，sqlite3可以在多线程中没有限制的使用

以上的三种模式可以在编译时，连接数据库，或者程序运行时（也就是新连接创建时）选择。默认是serialized模式。

正文

有了以上基础，我们大概明白了，sqlite3的运行。是不是如果直接是serialized,就万事大吉了？可是python 里面我没找到。

于是我们开始试错,模拟sqlite3共享连接时的几种错误。

错误的单纯共享连接

import sqlite3import timeimport threadimport threadinglock = threading.Lock()conn = sqlite3.connect('test.db', isolation_level="IMMEDIATE", timeout=60, check_same_thread=False)c = conn.cursor()def update_f2(information):    # print information    # for filed in information.keys():    #     print filed    # for key in information:    #     print key    updates = ", ".join(["`" + field + "`" + '=:' + field for field in information.keys() if field != 'name'])    where = ' WHERE name == :name'    values = information    query = 'UPDATE firms SET ' + updates + where    print query    print "exec"    try:        time.sleep(3)        # raise TypeError, "HEHE"        c.execute(query, values)         time.sleep(3)        conn.commit()    except Exception, e:        print e    print "commit"for i in range(10):    thread.start_new_thread(update_f2, (dict(name='bar', founded='1062', rev='7 MILLION DOLLARS!'), ))update(dict(name='bar', founded='1062', rev='7 MILLION DOLLARS!'))# update({})print c.execute('select * from firms').fetchall()conn.close()time.sleep(1000)

这个是最容易出错的类型

错误的加锁

import sqlite3import timeimport threadimport threadinglock = threading.Lock()conn = sqlite3.connect('test.db', isolation_level="IMMEDIATE", timeout=60, check_same_thread=False)c = conn.cursor()def update(information):    updates = ", ".join(["`" + field + "`" + '=:' + field for field in information.keys() if field != 'name'])    where = ' WHERE name == :name'    values = information    query = 'UPDATE firms SET ' + updates + where    print query    print "exec"    try:        lock.acquire(True)        time.sleep(3)        c.execute(query, values)        # raise TypeError, "HEHE"        conn.commit()    except:        conn.rollback()        raise    finally:        lock.release()    print "commit"for i in range(10):    thread.start_new_thread(update_f1, (dict(name='bar', founded='1062', rev='7 MILLION DOLLARS!'), ))update(dict(name='bar', founded='1062', rev='7 MILLION DOLLARS!'))# update({})print c.execute('select * from firms').fetchall()conn.close()time.sleep(1000)

以上代码我们以为万事大吉，实际上加的锁只对那个函数有用，对c.execute('select * from firms').fetchall()没有加锁。所以采用这种加锁的思路的话，需要其他更好的实现。

正确的每个线程一个连接

import sqlite3import timeimport threadimport threadingdef update_normal(information):    updates = ", ".join(["`" + field + "`" + '=:' + field for field in information.keys() if field != 'name'])    where = ' WHERE name == :name'    values = information    query = 'UPDATE firms SET ' + updates + where    print query    conn = sqlite3.connect('test.db', isolation_level="IMMEDIATE", timeout=60, check_same_thread=False)    c = conn.cursor()    print "exec"    try:        c.execute(query, values)        time.sleep(3)        # raise TypeError, "HEHE"        conn.commit()    except:        conn.rollback()        raise    finally:        conn.close()    print "commit"for i in range(10):    thread.start_new_thread(update_normal, (dict(name='bar', founded='1062', rev='7 MILLION DOLLARS!'), ))update(dict(name='bar', founded='1062', rev='7 MILLION DOLLARS!'))# update({})print c.execute('select * from firms').fetchall()conn.close()time.sleep(1000)

其他资料

python 官方 多线程sqlite3