python 验证 sqlite数据库隔离级别

sqlite3支持三种数据库锁级别，也叫隔离级别。下面代码中我们对数据库中task表进行更新，然后由另一个线程读取这个表的数据

import logging
import sqlite3
import sys
import threading
import time

logging. basicConfig (
    level = logging. DEBUG ,
    format = '%(asctime)s (%(threadName)-10s) %(message)s' ,
)
db_filename = 'todo.db'
isolation_level = sys. argv [ 1 ]
def writer ( ):
    my_name = threading. currentThread ( ). name
    with sqlite3. connect (db_filename ,isolation_level =isolation_level ) as conn:
        cursor =conn. cursor ( )
        cursor. execute ( 'update task set priority=priority+1' )
        logging. debug ( 'waiting to synchronize' )
        ready. wait ( ) # synchronize threads
        time. sleep ( 1 )
        conn. commit ( )
        logging. debug ( 'CHANGES COMMITTED' )
    return
def reader ( ):
    my_name = threading. currentThread ( ). name
    with sqlite3. connect (db_filename ,isolation_level =isolation_level ) as conn:
        cursor =conn. cursor ( )
        logging. debug ( 'waiting to synchronize' )
        ready. wait ( ) #synchronize threads
        cursor. execute ( 'select * from task' )
        logging. debug ( 'SELECT EXECUTED' )
        results =cursor. fetchall ( )
        logging. debug ( 'result fetched' )
    return

if __name__ == '__main__':
    ready = threading. Event ( )
    threads = [
        threading. Thread (name = 'Reader 1' ,target =reader ) ,
        threading. Thread (name = 'Reader 2' ,target =reader ) ,
        threading. Thread (name = 'Writer 1' ,target =writer ) ,
        threading. Thread (name = 'Writer 2' ,target =writer ) ,
    ]
    [t. start ( ) for t in threads ]
    time. sleep ( 1 )
    logging. debug ( 'setting ready' )
    ready. set ( )
    [t. join ( ) for t in threads ]

以下程序演示了，分别使用不同的锁级别操作数据库的结果。
1.使用延迟锁：这种模式是sqlite3的默认模式，也就是只在发生改变的时候才会锁上被更新的记录。

$ python sqlite3_isolation_levels. py DEFERRED

其输出结果为：

2011-12-13 11:19:38,183 (Reader 1   ) waiting to synchronize
2011-12-13 11:19:38,183 (Reader 2   ) waiting to synchronize
2011-12-13 11:19:38,187 (Writer 1   ) waiting to synchronize
2011-12-13 11:19:39,184 (MainThread ) setting ready
2011-12-13 11:19:39,186 (Reader 1   ) SELECT EXECUTED
2011-12-13 11:19:39,186 (Reader 2   ) SELECT EXECUTED
2011-12-13 11:19:39,187 (Reader 1   ) result fetched
2011-12-13 11:19:39,187 (Reader 2   ) result fetched
2011-12-13 11:19:40,243 (Writer 1   ) CHANGES COMMITTED
2011-12-13 11:19:40,316 (Writer 2   ) waiting to synchronize
2011-12-13 11:19:41,368 (Writer 2   ) CHANGES COMMITTED

2.使用“立即锁”：这种模式下一但要更新数据库，会立即锁上这条记录，直到事务提交才会打开锁。

$ python sqlite3_isolation_levels. py IMMEDIATE

其输出结果为：

2011-12-13 11:27:04,053 (Reader 1   ) waiting to synchronize
2011-12-13 11:27:04,053 (Reader 2   ) waiting to synchronize
2011-12-13 11:27:04,058 (Writer 1   ) waiting to synchronize
2011-12-13 11:27:05,055 (MainThread ) setting ready
2011-12-13 11:27:05,056 (Reader 1   ) SELECT EXECUTED
2011-12-13 11:27:05,058 (Reader 1   ) result fetched
2011-12-13 11:27:05,058 (Reader 2   ) SELECT EXECUTED
2011-12-13 11:27:05,058 (Reader 2   ) result fetched
2011-12-13 11:27:06,111 (Writer 1   ) CHANGES COMMITTED
2011-12-13 11:27:06,188 (Writer 2   ) waiting to synchronize
2011-12-13 11:27:07,244 (Writer 2   ) CHANGES COMMITTED

3.使用“排他锁”：这种锁会对所有的读写操作都上锁。这种锁一般用于对数据库性能要求较高的情况，因为一旦上锁，这个数据库连接就只能为一个使用者使用。

$ python sqlite3_isolation_levels. py EXCLUSIVE

其输出结果为：

2011-12-13 11:32:37,276 (Reader 1   ) waiting to synchronize
2011-12-13 11:32:37,276 (Reader 2   ) waiting to synchronize
2011-12-13 11:32:37,278 (Writer 1   ) waiting to synchronize
2011-12-13 11:32:38,279 (MainThread ) setting ready
2011-12-13 11:32:39,336 (Writer 1   ) CHANGES COMMITTED
2011-12-13 11:32:39,367 (Reader 2   ) SELECT EXECUTED
2011-12-13 11:32:39,368 (Reader 1   ) SELECT EXECUTED
2011-12-13 11:32:39,368 (Reader 2   ) result fetched
2011-12-13 11:32:39,371 (Reader 1   ) result fetched
2011-12-13 11:32:39,398 (Writer 2   ) waiting to synchronize
2011-12-13 11:32:40,453 (Writer 2   ) CHANGES COMMITTED

当然我们也可以把锁级别设置为None，这样就是所谓的自动提交模式。我们对上面的代码修改一下，把isolation_level的值设置为None，然后去掉conn.commit()。这样每次的数据库修改会自动提交到数据库。代码如下：

import logging
import sqlite3
import sys
import threading
import time

logging. basicConfig (
    level = logging. DEBUG ,
    format = '%(asctime)s (%(threadName)-10s) %(message)s' ,
)
db_filename = 'todo.db'

def writer ( ):
    my_name = threading. currentThread ( ). name
    with sqlite3. connect (db_filename ,isolation_level = None ) as conn:
        cursor =conn. cursor ( )
        cursor. execute ( 'update task set priority=priority+1' )
        logging. debug ( 'waiting to synchronize' )
        ready. wait ( ) # synchronize threads
        time. sleep ( 1 )
        logging. debug ( 'CHANGES COMMITTED' )
    return
def reader ( ):
    my_name = threading. currentThread ( ). name
    with sqlite3. connect (db_filename ,isolation_level = None ) as conn:
        cursor =conn. cursor ( )
        logging. debug ( 'waiting to synchronize' )
        ready. wait ( ) #synchronize threads
        cursor. execute ( 'select * from task' )
        logging. debug ( 'SELECT EXECUTED' )
        results =cursor. fetchall ( )
        logging. debug ( 'result fetched' )
    return

if __name__ == '__main__':
    ready = threading. Event ( )
    threads = [
        threading. Thread (name = 'Reader 1' ,target =reader ) ,
        threading. Thread (name = 'Reader 2' ,target =reader ) ,
        threading. Thread (name = 'Writer 1' ,target =writer ) ,
        threading. Thread (name = 'Writer 2' ,target =writer ) ,
    ]
    [t. start ( ) for t in threads ]
    time. sleep ( 1 )
    logging. debug ( 'setting ready' )
    ready. set ( )
    [t. join ( ) for t in threads ]

执行后的输出结果为：

2011-12-13 11:35:00,753 (Reader 1   ) waiting to synchronize
2011-12-13 11:35:00,753 (Reader 2   ) waiting to synchronize
2011-12-13 11:35:00,825 (Writer 1   ) waiting to synchronize
2011-12-13 11:35:00,953 (Writer 2   ) waiting to synchronize
2011-12-13 11:35:01,755 (MainThread ) setting ready
2011-12-13 11:35:01,756 (Reader 2   ) SELECT EXECUTED
2011-12-13 11:35:01,756 (Reader 1   ) SELECT EXECUTED
2011-12-13 11:35:01,757 (Reader 2   ) result fetched
2011-12-13 11:35:01,757 (Reader 1   ) result fetched
2011-12-13 11:35:02,755 (Writer 2   ) CHANGES COMMITTED
2011-12-13 11:35:02,755 (Writer 1   ) CHANGES COMMITTED