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
扫一扫
214
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
