用 SQLite 锁定一行(读锁?) [英] Locking a row with SQLite (read lock ?)

问题描述

我用 Python 开发了一个基本的代理测试器.代理 IP 和端口，以及它们的 date_of_last_test(例如 31/12/2011 10:10:10)和 result_of_last_test(OK 或 KO)存储在单个 SQLite 中表.(我意识到我可以存储更多关于测试结果的详细信息并保留历史记录/统计数据，但这个简单的模型适合我的需要).

I have developed a basic proxy tester in Python. Proxy IPs and ports, as well as their date_of_last_test (e.g. 31/12/2011 10:10:10) and result_of_last_test (OK or KO) are stored in a single SQLite table. (I realize I could store a lot more details on the tests results and keep an history / stats, but this simple model suits my needs).

这是测试器主循环的简化代码，我在其中循环代理并更新它们的状态:

Here is the simplified code of the tester main loop, where I loop over the proxies and update their status:

while True:
    # STEP 1: select
    myCursor.execute("SELECT * from proxy ORDER BY date_of_last_test ASC;")
    row = myCursor.fetchone()

    # STEP 2: update
    if isProxyWorking(row['ip'], row['port']): # this test can last a few seconds
        updateRow(row['ip'], row['port'], 'OK')
    else:
        updateRow(row['ip'], row['port'], 'KO')

当作为单个进程运行时，我的代码运行良好.现在，我希望能够运行该程序的多个进程，使用相同的 SQLite 数据库文件.当前代码的问题是缺乏一种锁定机制，可以防止多个进程测试同一个代理.

My code works fine when run as a single process. Now, I would like to be able to run many processes of the program, using the same SQLite database file. The problem with the current code is the lack of a locking mechanism that would prevent several processes from testing the same proxy.

在 STEP 1/SELECT 时间锁定行的最干净方法是什么，以便下一个执行 SELECT 的进程获取下一行?

What would be the cleanest way to put a lock on the row at STEP 1 / SELECT time, so that the next process doing a SELECT gets the next row ?

换句话说，我想避免以下情况:

In other words, I'd like to avoid the following situation:

假设现在是晚上 10 点，并且数据库包含 2 个代理:代理 A 最后一次测试是在晚上 8 点，代理 B 在晚上 9 点测试.

Let's say it's 10PM, and the DB contains 2 proxies: Proxy A tested for the last time at 8PM and proxy B tested at 9PM.

我启动了测试者的两个进程来更新它们的状态:

I start two processes of the tester to update their statuses:

• 10:00 - 进程 1 获取最旧的"代理来测试它:A
• 10:01 -进程 2 获取最旧的"代理来测试它:！！！A !!!(这里我像进程 2 来获取代理 B 因为 A 已经在测试 -虽然尚未在 db 中更新)
• 10:10 - 进程 1 对 A 的测试是结束，其状态在 DB 中更新
• 10:11 - 进程 2 对 A 的测试是结束，它的状态在 DB 中更新(！！！AGAIN !!!)

• 10:00 - Process 1 gets the "oldest" proxy to test it: A
• 10:01 - Process 2 gets the "oldest" proxy to test it: !!! A !!! (here I'd like Process 2 to get proxy B because A is already being tested - though not updated yet in db)
• 10:10 - Testing of A by Process 1 is over, its status is updated in DB
• 10:11 - Testing of A by Process 2 is over, its status is updated (!!! AGAIN !!!) in DB

在这种情况下没有实际的错误/异常，但我想避免浪费时间.

There is no actual error/exception in this case, but a waste of time I want to avoid.

推荐答案

SQlite 一次只允许一个进程更新数据库中的任何内容，来自 常见问题解答

SQlite only allows one process to update anything in the database at a time, From the FAQ

多个进程可以同时打开同一个数据库.多个进程可以同时执行 SELECT.但是任何时候只有一个进程可以对数据库进行更改，

Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time,

和

当 SQLite 尝试访问被另一个进程锁定的文件时，默认行为是返回 SQLITE_BUSY.您可以使用 sqlite3_busy_handler() 或 sqlite3_busy_timeout() API 函数从 C 代码调整此行为.

When SQLite tries to access a file that is locked by another process, the default behavior is to return SQLITE_BUSY. You can adjust this behavior from C code using the sqlite3_busy_handler() or sqlite3_busy_timeout() API functions.

因此，如果只有少量更新，那么这将起作用，否则您需要更改为功能更强大的数据库.

So if there only a few updates then this will work otherwise you need to change to a more capable database.

所以整个数据库上只有一个锁

so there is only one lock which is on the whole database

这篇关于用 SQLite 锁定一行(读锁?)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！