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():
            conn.commit()

        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():
            conn.commit()

        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
    conn.commit()
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。

这里简单对rollback 日志模式稍作总结(想了解wal日志模式，请参考https://zhangbuhuai.com/post/sqlite.html)：

每次写事务都有两个写 IO 的操作（一次是创建 .db-journal，一次修改数据库）
可以同时执行多个读事务
不能同时执行多个写事务
读事务会影响写事务，如果读事务较多，写事务在提交阶段（获取 exclusive 锁）常会遇到 SQLITE_BUSY 错误
写事务会影响读事务，在写事务的提交阶段，读事务是无法进行的
写事务遇到 SQLITE_BUSY 错误的节点较多

总结

如果编写高并发的服务端程序，一定要对sqlite3数据库的写入操作和读取操作进行有效管理，常用的方案有四个：

使用线程队列，把所有的写操作放入队列中，确保同一时刻只有一个线程执行写入数据库的代码；
使用锁机制使得多个线程竞争进入临界区，确保同一时刻只有一个线程执行写入数据库的代码；
连接数据库时设置参数timeout，设置当数据库处于锁定状态时最长等待时间，sqlite3.connect()函数的参数timeout默认值为5秒，不适合服务端程序。但是参数timeout设置为多少更合适取决于具体的应用场景，虽然形式简洁，但是不如前面两种方法通用。
读操作和写操作的时间不宜过长，操作完数据库后，马上断开连接，不要做其他无关数据库的操作。

关于sqlite3详细原理的文章，可参考：https://zhangbuhuai.com/post/sqlite.html

posted on 2021-10-11 11:01 xufat 阅读(6925) 评论(1) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

阅读排行：
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· 阿里巴巴 QwQ-32B真的超越了 DeepSeek R-1吗？
· 【译】Visual Studio 中新的强大生产力特性
· 【设计模式】告别冗长if-else语句：使用策略模式优化代码结构
· 10年+ .NET Coder 心语 ── 封装的思维：从隐藏、稳定开始理解其本质意义