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 ]
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
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
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
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 ]
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
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