Requests to SQLite3 databases do not wait for the lock, causing errors on simultaneous access
Closedopened 2 years ago by ChristopherCormier · 18 comments
Reference in new issue
There is no content yet.
Delete Branch '%!s(<nil>)'
Deleting a branch is permanent. It CANNOT be undone. Continue?
This can be trivially reproduced by trying to submit two posts at the same time to a board, but it can happen commonly out in the wild on any board that has a high likelihood of two people trying to submit something at once.
The largest issue here is that, from a user's perspective, the post looks like it should've succeeded but it doesn't, and they lose everything that they've typed.
"Silently" isn't entirely true, as it does produce three warnings that are visible for a small fraction of a second in the same screen where it updates the thread and index, though it goes by so quickly that it'd be impossible for a user to understand it, and unintelligible even if they did: https://i.imgur.com/iUtTiuq.png
Though not thoroughly tested, this problem should also reasonably affect any and all database operations when using SQLite3, preventing anything from happening concurrently. Notably, accessing the management panel at the same time as someone is making a post causes an error in the management panel as well. I'm suspecting that the reverse is also true, that accessing the management panel would block someone's post if it was timed right.
My basic impression is that the optimal way to fix this would be to raise the SQLite busy timeout value so that other requests will properly wait on the lock to close, "queueing" for it instead of immediately erroring out. Let me know if I'm misunderstanding anything.
Small follow-up: I think I've been able to reproduce this simply from deleting two posts separately in relatively quick succession (7-10 seconds between). There's a decently powerful server backing my instance with no other activity outside of testing. It seems like the lock is lasting longer than is reasonable, especially with the tiny size of the database and the triviality of the database operation. Is it waiting on something else before it closes?
Thanks for reporting this.
Reopening for now, as it seems like this has been happening again when my board's at peak hours of activity. Bewilderingly, this includes moments when no posts are even being made, but the board is being accessed a lot.
Anecdotally, it seems like it happens much more frequently when TINYIB_AUTOREFRESH is set to a very low value, but I don't know if that makes sense. It also tends to happen when navigating through the moderation section, presumably from the lastactive value being updated for staff accounts in the database on each page load.
The most common error I've seen lately is:
PHP Warning: SQLite3::exec(): database is locked in /var/www/html/inc/database/sqlite3.php on line 4
But the line number has been almost anything, especially including lines 15, 17, 19, and 33 recently. I've had user reports of this error occasionally eating posts as well, and it's possibly connected to some other issues I've experienced (such as threads sometimes only partially generating, creating a listing on the board index and catalog but without creating an associated res/X.html file).
I don't know if there's any good way for me to test or diagnose the cause of this. If I query the setting in the database with
PRAGMA busy_timeout;, it claims to be set to 0 instead of the expected 60000, but I don't know if that has any actual relation to the timeout that's being set in sqlite3_link.php
5d191f3513from the sqlitefix branch, which enables write-ahead logging.
Also, please try replacing this line
$db->exec("PRAGMA busy_timeout = 60000");
and let me know if
Sorry for the long wait, I only just now got around to adopting those fixes. I've replaced that line in my sqlite3_link.php file with
$db->exec("PRAGMA busy_timeout = 60000");and
$db->exec("PRAGMA journal_mode = wal");as shown here: https://i.imgur.com/9d5D416.png
Time will tell if this fixes the problem, but I'll note that querying busy_timeout through the interactive shell still returns
0, even after TinyIB has modified the database with this new code added.
That said, perhaps this issue stems from my inexperience with SQL. Even if I copy the database to a separate file that's unmanaged by TinyIB, manually run
PRAGMA busy_timeout = 60000;on it, confirm it saved by querying the pragma immediately after, and then save and exit the database, the value has reset whenever I freshly open it again.
Is this pragma value meant to be persistently stored in the database after being set? Or is it only remembered as long as your connection with the database remains open? I suspect I'm doing something wrong.
Update: Doesn't seem to have made a difference unfortunately. During another spike of activity, my error.log saw a sizable number of "PHP Warning: SQLite3::exec(): database is locked in [...]/inc/database/sqlite3.php on line 4" entries.
Worth noting that "PRAGMA journal_mode;" does print
walnow, but "PRAGMA busy_timeout;" continues printing
Thanks. I will look into this more when I have some time. If this is being caused by a race condition, #251 should fix this.
Please let me know whether this was indeed fixed by #251 (which I've just implemented).
My boards have been less active anyways lately so I can't say for sure, but I haven't had any instances of this error since pulling in
57b50658a2a couple months ago. I'll close this for now and assume it's fixed. Thank you for your work as always!
This issue is still a thing on sqlite3!
Im on latest master branch with patch from sqlitefix branch (php-sqlite3 7.4-30)
And this still happens... way more rarely after enabling WAL mode but it happens, is terribly annoying
To end users behavior is just as explained in first post
@tslocum any futher ideas?
@alix, please share which operating system you are using. I don't believe you need to apply any patches to TinyIB or PHP to get this to work, you should be able to use a standard release of both. Once you've confirmed these details I will reopen this issue.
by patch I've meant this commit
OS is debian 11 bullseye with 5.10.x kernel
Im using standard version of php7.4 and php-sqlite3 from debian repo
Like I said this error happens really really rarely now but still...
That branch should no longer be needed, because of
57b50658a2. Please update to the latest commit in the master branch, and let me know if you still encounter this.
Im on latest master branch all the time since my first post
(just took that commit from sqlitefix branch and applyed it on my own because I faced issues...)
picrel is unrelated to posting issue but still probably shoudnt be that way at all...
You shouldn't need to apply the commit in the sqlitefix branch. Please reset your repository to not include this commit. If you are still encountering issues, ensure
TINYIB_LOCKFILEis set to some value. You could check this by temporarily replacing
lockDatabasewith the following:
we seem to be having some strange bug with the text formating
I understand tho like I said it seems improve stuff overall. And since I faced this issue before applying that commit... reverting it wont fix issue for sure
I've replaced lockDatabase() function with code you have posted and saw no 'fancydeath' on posting so I assume lock file is working fine
when I put fancyDie(TINYIB_LOCKFILE); at very begining of lock function it says
such file exists on main imageboard directory all the time
Which of other database modes you recomend?
Im thinking in terms of minimal size and minimal amount of additional packages needed to be installed.
Sqlite3 file based database feels perfect for this meaning but this strange issue...
While this should be in another issue ticket its true
This sort of characters can bypass message framebox and expand beyond (while they shoudn't)