sqlite3并发操作导致数据库被锁问题记录

sqlite3并发操作导致数据库被锁问题记录
⽤此⽂章来记录在开发过程中遇到⽤sqlite数据库进⾏并发操作导致数据库被锁的问题。

这⾥我先简单说⼀下sqlite3数据库的特性：
SQLite 是⼀款轻型的嵌⼊式数据库，它占⽤资源⾮常的低，处理速度快，⾼效⽽且可靠。

在嵌⼊式设备中，可能只需要⼏百 K 的内存就够了。

因此在移动设备爆发时，它依然是最常见的数据持久化⽅案之⼀;
SQLite 的 API 是⽀持多线程访问的，多线程访问必然带来数据安全问题。

sqlite3⽀持并发执⾏读事务，即可以同时开启多个进程/线程从数据库读数据
sqlite3 是不⽀持并发执⾏写事务，即不能多个进程/线程同时往数据库写数据，它的写事务就是锁表，你⽆论开⼏个线程，只要写操作访问的是同⼀张表，最后在 sqlite3 那⾥都会被锁，实际上最后写操作都是顺序执⾏的。

本地存储，不⽀持⽹络访问
问题1
在项⽬开发过程中，SQLite同⼀时刻只允许单个线程写⼊，很多服务端程序会开很多线程，每个线程为⼀个客户端服务，如果有多个客户端同时发起写⼊请求，在服务端会因为某个线程尚未写⼊完成尚未解除对数据库的锁定⽽导致其他线程⽆法在限定的时间内完成写⼊操作⽽抛出异常，提⽰“database is locked”。

下⾯我复现⼀下问题的发⽣。

问题1
执⾏以下多线程写操作的代码：
# coding:utf-8
"""
测试sqlite数据库锁的问题
"""
import threading
import time
import contextlib
import traceback
import sqlite3
import os
# Path = "/tmp"
Path = r"D:\PythonProject\testProject"
Name = "openmptcprouter.db"
class DbError(Exception):
def __init__(self):
super().__init__(self)
def __str__(self):
return "DB Error"
class Job(object):
"""
A indicator to mark whether the job is finished.
"""
def __init__(self):
self._finished = False
def is_finished(self):
return self._finished
def finish(self):
self._finished = True
@contextlib.contextmanager
def transaction(path=Path, name=Name):
"""
Automatic handle transaction COMMIT/ROLLBACK. You MUST call trans.finish(),
if you want to COMMIT; Otherwise(not call or exception occurs), ROLLBACK.
>>> with transaction(conn) as trans:
>>> do something...
>>> if xxxxx:
>>> # if you don't want to commit, you just not call trans.finish().
>>> return error_page("xxxxxx")
>>> # if you want to commit, you call:
>>> trans.finish()
@param conn: database connection
"""
db_path = os.path.join(path, name)
conn = sqlite3.connect(db_path)
# conn.row_factory = dict_factory
cursor = conn.cursor()
trans = Job()
cursor.execute("BEGIN TRANSACTION")
try:
yield trans, cursor
if trans.is_finished():
mit()
else:
conn.rollback()
except:
conn.rollback()
raise DbError
finally:
cursor.close()
conn.close()
def write_fun():
ip = "172.0.0.1"
user_id = "1"
path = "/status/vpn"
params = "{work_fun1}"
info = "0000 获取vpn列表状态成功"
cost_time = "5"
print("wating to synchronize write")
ready.wait()
try:
print("=================start sqlite connection=================") with transaction() as (trans, cursor):
print("starting to write")
ready.wait()
cursor.execute(
"""
insert into operation_log(ip,user_id,path,params,info,cost_time)
values(?,?,?,?,?,?)
""", (ip, user_id, path, params, info, cost_time))
print("wating to commit")
# time.sleep(3) # 在这⾥延迟，数据库则会被锁住
trans.finish()
print("write commit complete")
print("=================close sqlite connection=================") except:
print(traceback.format_exc())
if __name__ == '__main__':
ready = threading.Event()
threads = [threading.Thread(target=write_fun) for i in range(3)]
[t.start() for t in threads]
time.sleep(1)
print("Setting ready")
ready.set()
[t.join() for t in threads]
输出结果：
wating to synchronize write
wating to synchronize write
wating to synchronize write
Setting ready
=================start sqlite connection=================
=================start sqlite connection=================
=================start sqlite connection=================
starting to write
starting to write
starting to write
wating to commit
write commit complete
=================close sqlite connection=================
wating to commit
write commit complete
=================close sqlite connection=================
wating to commit
write commit complete
=================close sqlite connection=================
从输出结果来看，当⽤三个线程同时并发去进⾏数据库的写操作的时候，并不会并发去执⾏，⽽是顺序去执⾏，如果⼀个写操作没完成，其他写操作需要等待。

接下来我的问题出现了：
此时如果我们在执⾏完sql操作后，进⾏commit操作之前，堵塞个3秒(在trans.finish()前⾯加上sleep延迟)，延迟整个写操作的过程，只有两个线程能完成写⼊操作，剩下⼀个，则会报数据库被锁住的异常(sqlite3.OperationalError: database is locked)。

注意：这⾥如果不想延迟去复现锁住的问题，则可以使⽤多⼀点的线程去同时执⾏，⽐如500个线程。

⼀般这⾥执⾏到200多到300线程，就会被锁住。

wating to synchronize write
wating to synchronize write
wating to synchronize write
Setting ready
=================start sqlite connection=================
=================start sqlite connection=================
=================start sqlite connection=================
starting to write
starting to write
starting to write
wating to commit
write commit complete
=================close sqlite connection=================
wating to commit
Traceback (most recent call last):
File "D:/PythonProject/testProject/test_lock_sqlite.py", line 67, in transaction
yield trans, cursor
File "D:/PythonProject/testProject/test_lock_sqlite.py", line 100, in write_fun
""", (ip, user_id, path, params, info, cost_time))
sqlite3.OperationalError: database is locked
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "D:/PythonProject/testProject/test_lock_sqlite.py", line 104, in write_fun
print("write commit complete")
File "D:\developer\Python37-64\lib\contextlib.py", line 130, in __exit__
self.gen.throw(type, value, traceback)
File "D:/PythonProject/testProject/test_lock_sqlite.py", line 76, in transaction
raise DbError
DbError: DB Error
write commit complete
=================close sqlite connection=================
所以我在这⾥猜测，顺序执⾏写操作的时候，⼀个写操作没完成，其他写操作需要等待，⽽且等待的时候⼤约是2*3s=6秒，如果超过，则数据库会被锁住。

因为这⾥如果我选择12个线程去执⾏，然后延迟1秒，只有6个线程能完成写⼊操作。

这⾥的规律后⾯我研究发现，这⾥的等待时间就是数据库的连接timeout设置。

默认timeout是5秒。

如果这⾥我把timeout设定为10秒，选择12个线程去执⾏，然后延迟1秒，就有11个线程能完成写⼊操作。

问题1
这⾥我最终的解决⽅案是，使⽤线程队列，把所有的数据库写操作放⼊队列，然后使⽤⼀个线程去执⾏队列⾥⾯的数据库写操作。

问题2
在项⽬开发过程中，如果有多个客户端同时发起写⼊和读取请求，此时如果其中有⼀个读取请求持续的时间过长，⼀直没有断开连接，尚未解除对数据库的锁定，导致其他的写操作⼀直挂着，便抛出异常，提⽰“database is locked”。

下⾯我复现⼀下问题的发⽣。

问题2
执⾏10个线程写操作和⼀个线程读操作的代码：
# coding:utf-8
"""
测试sqlite数据库锁的问题
"""
import threading
import time
import contextlib
import traceback
import sqlite3
import os
import datetime
Path = r"D:\PythonProject\testProject"
Name = "openmptcprouter.db"
class DbError(Exception):
def __init__(self):
super().__init__(self)
def __str__(self):
return "DB Error"
class Job(object):
"""
A indicator to mark whether the job is finished.
"""
def __init__(self):
self._finished = False
def is_finished(self):
return self._finished
def finish(self):
self._finished = True
@contextlib.contextmanager
def transaction(path=Path, name=Name):
"""
Automatic handle transaction COMMIT/ROLLBACK. You MUST call trans.finish(), if you want to COMMIT; Otherwise(not call or exception occurs), ROLLBACK.
>>> with transaction(conn) as trans:
>>> do something...
>>> if xxxxx:
>>> # if you don't want to commit, you just not call trans.finish().
>>> return error_page("xxxxxx")
>>> # if you want to commit, you call:
>>> trans.finish()
@param conn: database connection
"""
db_path = os.path.join(path, name)
conn = sqlite3.connect(db_path, timeout=10)
# conn.row_factory = dict_factory
cursor = conn.cursor()
trans = Job()
cursor.execute("BEGIN TRANSACTION")
try:
yield trans, cursor
if trans.is_finished():
mit()
else:
conn.rollback()
except:
conn.rollback()
raise DbError
finally:
cursor.close()
conn.close()
def write_fun():
ip = "172.0.0.1"
user_id = "1"
path = "/status/vpn"
params = "{work_fun1}"
info = "0000 获取vpn列表状态成功"
cost_time = "5"
print("wating to synchronize write")
ready.wait()
try:
print("=================start sqlite connection=================")
with transaction() as (trans, cursor):
print("starting to write")
cursor.execute(
"""
insert into operation_log(ip,user_id,path,params,info,cost_time)
values(?,?,?,?,?,?)
""", (ip, user_id, path, params, info, cost_time))
print("{}:wating to commit".format(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")))
# time.sleep(2)
trans.finish()
print("{}:write commit complete".format(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))) print("=================close sqlite connection=================")
except:
print(traceback.format_exc())
def read_fun(delay):
print("Wating to read_fun")
ready.wait()
# time.sleep(delay)
with transaction() as (trans, cursor):
print("connect read_fun")
cursor.execute("select * from operation_log")
print("{}:read_fun sleep".format(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")))
time.sleep(delay)
print("{}:read_fun Done".format(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")))
if __name__ == '__main__':
ready = threading.Event()
threads = [threading.Thread(target=write_fun) for i in range(10)]
threads.extend([threading.Thread(target=read_fun, args=(15,)) for i in range(1)])
[t.start() for t in threads]
time.sleep(1)
print("Setting ready")
ready.set()
[t.join() for t in threads]
输出结果
D:\python_XZF\py37env\Scripts\python.exe D:/PythonProject/testProject/test_lock_sqlite.py
wating to synchronize write
wating to synchronize write
wating to synchronize write
wating to synchronize write
wating to synchronize write
wating to synchronize write
wating to synchronize write
wating to synchronize write
wating to synchronize write
wating to synchronize write
Wating to read_fun
Setting ready
=================start sqlite connection=================
=================start sqlite connection=================
=================start sqlite connection=================
=================start sqlite connection=================
=================start sqlite connection=================
=================start sqlite connection=================
=================start sqlite connection=================
=================start sqlite connection=================
=================start sqlite connection=================
=================start sqlite connection=================
starting to writestarting to write
starting to write
starting to write
starting to write
starting to write
connect read_fun
starting to writestarting to write
starting to writestarting to write
2021-10-11 14:19:13:read_fun sleep
2021-10-11 14:19:13:wating to commit
2021-10-11 14:19:13:write commit complete
Traceback (most recent call last):
File "D:/PythonProject/testProject/test_lock_sqlite.py", line 71, in transaction
mit()
sqlite3.OperationalError: database is locked
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "D:/PythonProject/testProject/test_lock_sqlite.py", line 104, in write_fun
print("{}:write commit complete".format(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))) File "D:\developer\Python37-64\lib\contextlib.py", line 119, in __exit__
next(self.gen)
File "D:/PythonProject/testProject/test_lock_sqlite.py", line 77, in transaction
raise DbError
DbError: DB Error
2021-10-11 14:19:24:wating to commit
2021-10-11 14:19:24:write commit complete
Traceback (most recent call last):
File "D:/PythonProject/testProject/test_lock_sqlite.py", line 68, in transaction
yield trans, cursor
File "D:/PythonProject/testProject/test_lock_sqlite.py", line 100, in write_fun
""", (ip, user_id, path, params, info, cost_time))
sqlite3.OperationalError: database is locked
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "D:/PythonProject/testProject/test_lock_sqlite.py", line 104, in write_fun
print("{}:write commit complete".format(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))) File "D:\developer\Python37-64\lib\contextlib.py", line 130, in __exit__
self.gen.throw(type, value, traceback)
File "D:/PythonProject/testProject/test_lock_sqlite.py", line 77, in transaction
raise DbError
DbError: DB Error
Traceback (most recent call last):
File "D:/PythonProject/testProject/test_lock_sqlite.py", line 68, in transaction
yield trans, cursor
File "D:/PythonProject/testProject/test_lock_sqlite.py", line 100, in write_fun
""", (ip, user_id, path, params, info, cost_time))
sqlite3.OperationalError: database is locked
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "D:/PythonProject/testProject/test_lock_sqlite.py", line 104, in write_fun
print("{}:write commit complete".format(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))) File "D:\developer\Python37-64\lib\contextlib.py", line 130, in __exit__
self.gen.throw(type, value, traceback)
File "D:/PythonProject/testProject/test_lock_sqlite.py", line 77, in transaction
raise DbError
DbError: DB Error
Traceback (most recent call last):
File "D:/PythonProject/testProject/test_lock_sqlite.py", line 68, in transaction
yield trans, cursor
File "D:/PythonProject/testProject/test_lock_sqlite.py", line 100, in write_fun
""", (ip, user_id, path, params, info, cost_time))
sqlite3.OperationalError: database is locked
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "D:/PythonProject/testProject/test_lock_sqlite.py", line 104, in write_fun
print("{}:write commit complete".format(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))) File "D:\developer\Python37-64\lib\contextlib.py", line 130, in __exit__
self.gen.throw(type, value, traceback)
File "D:/PythonProject/testProject/test_lock_sqlite.py", line 77, in transaction
raise DbError
DbError: DB Error
Traceback (most recent call last):
File "D:/PythonProject/testProject/test_lock_sqlite.py", line 68, in transaction
yield trans, cursor
File "D:/PythonProject/testProject/test_lock_sqlite.py", line 100, in write_fun
""", (ip, user_id, path, params, info, cost_time))
sqlite3.OperationalError: database is locked
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "D:/PythonProject/testProject/test_lock_sqlite.py", line 104, in write_fun
print("{}:write commit complete".format(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")))
File "D:\developer\Python37-64\lib\contextlib.py", line 130, in __exit__
self.gen.throw(type, value, traceback)
File "D:/PythonProject/testProject/test_lock_sqlite.py", line 77, in transaction
raise DbError
DbError: DB Error
Traceback (most recent call last):
File "D:/PythonProject/testProject/test_lock_sqlite.py", line 68, in transaction
yield trans, cursor
File "D:/PythonProject/testProject/test_lock_sqlite.py", line 100, in write_fun
""", (ip, user_id, path, params, info, cost_time))
sqlite3.OperationalError: database is locked
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "D:/PythonProject/testProject/test_lock_sqlite.py", line 104, in write_fun
print("{}:write commit complete".format(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))) File "D:\developer\Python37-64\lib\contextlib.py", line 130, in __exit__
self.gen.throw(type, value, traceback)
File "D:/PythonProject/testProject/test_lock_sqlite.py", line 77, in transaction
raise DbError
DbError: DB Error
Traceback (most recent call last):
File "D:/PythonProject/testProject/test_lock_sqlite.py", line 68, in transaction
yield trans, cursor
File "D:/PythonProject/testProject/test_lock_sqlite.py", line 100, in write_fun
""", (ip, user_id, path, params, info, cost_time))
sqlite3.OperationalError: database is locked
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "D:/PythonProject/testProject/test_lock_sqlite.py", line 104, in write_fun
print("{}:write commit complete".format(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))) File "D:\developer\Python37-64\lib\contextlib.py", line 130, in __exit__
self.gen.throw(type, value, traceback)
File "D:/PythonProject/testProject/test_lock_sqlite.py", line 77, in transaction
raise DbError
DbError: DB Error
Traceback (most recent call last):
File "D:/PythonProject/testProject/test_lock_sqlite.py", line 68, in transaction
yield trans, cursor
File "D:/PythonProject/testProject/test_lock_sqlite.py", line 100, in write_fun
""", (ip, user_id, path, params, info, cost_time))
sqlite3.OperationalError: database is locked
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "D:/PythonProject/testProject/test_lock_sqlite.py", line 104, in write_fun
print("{}:write commit complete".format(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))) File "D:\developer\Python37-64\lib\contextlib.py", line 130, in __exit__
self.gen.throw(type, value, traceback)
File "D:/PythonProject/testProject/test_lock_sqlite.py", line 77, in transaction
raise DbError
DbError: DB Error
Traceback (most recent call last):
File "D:/PythonProject/testProject/test_lock_sqlite.py", line 68, in transaction
yield trans, cursor
File "D:/PythonProject/testProject/test_lock_sqlite.py", line 100, in write_fun
""", (ip, user_id, path, params, info, cost_time))
sqlite3.OperationalError: database is locked
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "D:/PythonProject/testProject/test_lock_sqlite.py", line 104, in write_fun
print("{}:write commit complete".format(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))) File "D:\developer\Python37-64\lib\contextlib.py", line 130, in __exit__
self.gen.throw(type, value, traceback)
File "D:/PythonProject/testProject/test_lock_sqlite.py", line 77, in transaction
raise DbError
DbError: DB Error
2021-10-11 14:19:28:read_fun Done
=================close sqlite connection=================
Process finished with exit code 0
从执⾏过程和输出结果来看，刚开始执⾏的时候只有⼀个线程马上完成了写⼊操作，其他写⼊操作因读操作的延误，⽽⼀直挂着等待执⾏，如果写⼊操作等待的时间超过了timeout值(默认5秒)，则会抛出异常，提⽰“database is locked”。

问题2
这⾥我最终的解决⽅案是，对数据库读操作后，马上断开连接，不要做其他跟数据库操作⽆关的操作。

1.SQLite 如何实现线程安全？
答：SQLite 的 API 是⽀持多线程访问的，多线程访问必然带来数据安全问题。

为了确保数据库安全，SQLite 内部抽象了两种类型的互斥锁（锁的具体实现和宿主平台有关）来应对线程并发问题：fullMutex
可以理解为 connection mutex，和连接句柄（上问描述的 sqlite3 结构体）绑定
保证任何时候，最多只有⼀个线程在执⾏基于连接的事务
coreMutex
当前进程中，与⽂件绑定的锁
⽤于保护数据库相关临界资源，确保在任何时候，最多只有⼀个线程在访问
如何理解 fullMutex？SQLite 中与数据访问相关的 API 都是通过连接句柄 sqlite3 进⾏访问的，基于 fullMutex 锁，如果多个线程同时访问某个 API -- 譬如sqlite3_exec(db, ...)，SQLite 内部会根据连接的 mutex 将该 API 的逻辑给保护起来，确保只有⼀个线程在执⾏，其他线程会被mutex 给 block 住。

对于 coreMutex，它⽤来保护数据库相关临界资源。

⽤户可以配置这两种锁，对这两种锁的控制衍⽣出 SQLite 所⽀持的三种线程模型：
single-thread
coreMutex 和 fullMutex 都被禁⽤
⽤户层需要确保在任何时候只有⼀个线程访问 API，否则报错（crash）
multi-thread
coreMutex 保留，fullMutex 禁⽤
可以多个线程基于不同的连接并发访问数据库，但单个连接在任何时候只能被⼀个线程访问
单个 connection，如果并发访问，会报错（crash）
报错信息：illegal multi-threaded access to database connection
serialized
coreMutex 和 fullMutex 都保留
2.如果SQLite 对并发读写，也即同时进⾏读事务和写事务的⽀持如何？
答：这个问题的答案与⽤户所选择的⽇志模型有关，以下答案也能解释问题2出现的具体原因。

SQLite ⽀持两种⽇志记录⽅式，或者说两种⽇志模型：Rollback和WAL。

SQLite 默认的⽇志模式是 rollback。

每次写事务都有两个写 IO 的操作（⼀次是创建 .db-journal，⼀次修改数据库）
可以同时执⾏多个读事务
不能同时执⾏多个写事务
读事务会影响写事务，如果读事务较多，写事务在提交阶段（获取 exclusive 锁）常会遇到 SQLITE_BUSY 错误
写事务会影响读事务，在写事务的提交阶段，读事务是⽆法进⾏的
写事务遇到 SQLITE_BUSY 错误的节点较多
如果编写⾼并发的服务端程序，⼀定要对sqlite3数据库的写⼊操作和读取操作进⾏有效管理，常⽤的⽅案有四个：
1. 使⽤线程队列，把所有的写操作放⼊队列中，确保同⼀时刻只有⼀个线程执⾏写⼊数据库的代码；
2. 使⽤锁机制使得多个线程竞争进⼊临界区，确保同⼀时刻只有⼀个线程执⾏写⼊数据库的代码；
3. 连接数据库时设置参数timeout，设置当数据库处于锁定状态时最长等待时间，sqlite3.connect()函数的参数timeout默认值为5秒，不适
合服务端程序。

但是参数timeout设置为多少更合适取决于具体的应⽤场景，虽然形式简洁，但是不如前⾯两种⽅法通⽤。

4. 读操作和写操作的时间不宜过长，操作完数据库后，马上断开连接，不要做其他⽆关数据库的操作。