使用sqlite3并发写入 [英] Concurrent writing with sqlite3

问题描述

我使用 sqlite3 python模块将批处理作业的结果写入常见的 .db 文件。我选择了SQLite，因为多个进程可能会尝试在同一时间写，而且我理解它SQLite应该处理这个好。我不确定的是当多个进程完成并尝试同时写入时会发生什么。因此，如果几个进程看起来像这样

I'm using the sqlite3 python module to write the results from batch jobs to a common .db file. I chose SQLite because multiple processes may try to write at the same time, and as I understand it SQLite should handel this well. What I'm unsure of is what happens when multiple processes finish and try to write at the same time. So if several processes that look like this

conn = connect('test.db')

with conn: 
    for v in xrange(10): 
        tup = (str(v), v)
        conn.execute("insert into sometable values (?,?)", tup)

立即执行，他们会抛出异常吗？要礼貌地等待其他进程写吗？有没有更好的方法来做到这一点？

execute at once, will they throw an exception? Wait politely for the other processes to write? Is there some better way to do this?

推荐答案

sqlite 库将锁定数据库 当写入数据库时​​，每个进程将等待锁定被释放以轮到它们。

The sqlite library will lock the database per process when writing to the database and each process will wait for the lock to be released to get their turn.

数据库不需要写入直到提交时间。您正在使用连接作为上下文管理器（好！），所以提交发生在循环完成后，并且所有 insert 语句已执行。

The database doesn't need to be written to until commit time however. You are using the connection as a context manager (good!) so the commit takes place after your loop has completed and all insert statements have been executed.

如果您的数据库有唯一性约束，可能是提交失败，因为一个进程已经添加了另一个进程冲突的行。

If your database has uniqueness constraints in place, it may be that the commit fails because one process has already added rows that another process conflicts with.

这篇关于使用sqlite3并发写入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！