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