SQLite 并发:第二个进程没有得到数据库更新 [英] SQLite concurrency: the 2nd process doesn't get DB updates

问题描述

为了查看 SQLite 是否可以同时被 2 个进程使用，我尝试了这个:

In order to see if SQLite can be used by 2 processes at the same time, I tried this:

script1.py(每 1 秒更新一次数据库)

script1.py (updating the database every 1 second)

import sqlite3, time
conn = sqlite3.connect('test.db')
conn.execute("CREATE TABLE IF NOT EXISTS kv (key text, value text)")

for i in range(1000):
    conn.execute('REPLACE INTO kv (key, value) VALUES (?,?)', (1, i))
    conn.commit()
    print i
    time.sleep(1)

script2.py(每 1 秒查询一次数据库)

script2.py (querying the database every 1 second)

import sqlite3, time
conn = sqlite3.connect('test.db')
c = conn.cursor()

while True:
    c.execute('SELECT value FROM kv WHERE key = ?', (1,))
    item = c.fetchone()
    print item
    time.sleep(1)

我启动了script1.py，然后是script2.py，让它们同时运行.我希望 script2.py 会知道(虽然我不知道怎么做！)数据库已更新，并且必须重新加载其中的一部分.但遗憾的是我在 script2.py 中得到了这个:

I started script1.py and then script2.py, and let them running at the same time. I hoped that script2.py would know (I don't know how though!) that the DB has been updated, and that it has to reload a part of it. But sadly I get this in script2.py:

(u'0',)
(u'0',)
(u'0',)
(u'0',)
(u'0',)
(u'0',)
(u'0',)

即它没有获得 script1.py 的更新.

i.e. it doesn't get script1.py's updates.

是否有一种简单的方法可以使 SQLite 实现这一点?

推荐答案

这适用于 sqlite3:从回答这个问题

This works fine with sqlite3: Moved from the answer to this question

import sqlite3, time
conn = sqlite3.connect('test.db')
conn.execute("CREATE TABLE IF NOT EXISTS kv (key text unique, value text)")

for i in range(1000):
    conn.execute('REPLACE INTO kv (key, value) VALUES (?,?)', (1, i))
    conn.commit()
    print i
    time.sleep(1)

script2.py

import sqlite3, time
conn = sqlite3.connect('test.db')
c = conn.cursor()

while True:
    c.execute('SELECT value FROM kv WHERE key = ?', (1,))
    item = c.fetchone()
    print item
    time.sleep(1)

输出

python script2.py 
(u'3',)
(u'4',)
(u'5',)
(u'6',)
(u'7',)

问题是您最初没有制作密钥 独特

The problem is that you originally haven't made your key unique

当违反 UNIQUE 或 PRIMARY KEY 约束时，REPLACE算法删除导致约束的预先存在的行在插入或更新当前行和命令继续正常执行.

When a UNIQUE or PRIMARY KEY constraint violation occurs, the REPLACE algorithm deletes pre-existing rows that are causing the constraint violation prior to inserting or updating the current row and the command continues executing normally.

如果 键 不是唯一的，就会发生以下情况:

Without the key being unique here's what happening:

sqlite3 test.db 
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
sqlite> select * from kv;
1|0
1|1
1|2
1|3
1|4
sqlite> select * from kv;
1|0
1|1
1|2
1|3
1|4
1|5
sqlite> select * from kv;
1|0
1|1
1|2
1|3
1|4
1|5
1|6
1|7
sqlite> 

是的，sqlite3 支持事务，但有一些警告.因此，如果您还需要支持多个写入者 - 多个读取者 场景，由于锁争用，一切都可能变得有点棘手

And yes, sqlite3 supports transactions, with a few caveats though. So if you also need to support multiple writers - multiple readers scenario everything may become a bit tricky because of locks contention

这是一个相关 如果需要，可以讨论多位作家案例

Here's a related discussion on the multiple writers case if you need it

这篇关于SQLite 并发:第二个进程没有得到数据库更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！