database is locked
Added by Emeric Poupon over 8 years ago
Hello,
I am facing an annoying issue.
I have a single thread that performs lots of select/update/insert in my database (scanning for the local media collection).
For each found media file, a new transaction is used to lookup the entry in the database. If not found, the same transaction is used to insert the new entry.
In the meantime, a user is trying to log in. Authentication is done using Wt::Auth.
Most of the time, users cannot even log in:
[2015-Sep-27 12:00:29.782314] 31957 [/ 30p29CoBMKMN7HTJ] [error] "Wt: error during event handling: Sqlite3: update ""auth_info"\" set ""version"" = ?, ""user_id"" = ?, ""password_hash"" = ?, ""password_method"" = ?, ""password_salt"" = ?, ""status"" = ?, ""failed_login_attempts"" = ?, ""last_login_attempt"" = ?, ""email"" = ?, ""unverified_email"" = ?, ""email_token"" = ?, ""email_token_expires"" = ?, ""email_token_role"" = ? where ""id"" = ? and ""version"" = ?: database is locked\"
[2015-Sep-27 12:00:29.782420] 31957 [/ 30p29CoBMKMN7HTJ] [error] "Wt: fatal error: Sqlite3: update ""auth_info"\" set ""version"" = ?, ""user_id"" = ?, ""password_hash"" = ?, ""password_method"" = ?, ""password_salt"" = ?, ""status"" = ?, ""failed_login_attempts"" = ?, ""last_login_attempt"" = ?, ""email"" = ?, ""unverified_email"" = ?, ""email_token"" = ?, ""email_token_expires"" = ?, ""email_token_role"" = ? where ""id"" = ? and ""version"" = ?: database is locked\"
Warning: Wt::Dbo::Session exiting with 1 dirty objects
When performing some actions on the database (ex: playlist creation), the thread that is scanning the local media collection and updating the database sometimes produce some errors too:
[2015-Sep-27 11:54:56.249423] 31957 - [error] - [DB UPDATER] Exception while parsing audio file : '"/tmp/test.mp3"': 'Sqlite3: insert into "track" ("version", "track_number", "disc_number", "name", "duration", "date", "original_date", "genre_list", "file_path", "file_last_write", "file_added", "checksum", "cover_type", "mbid", "release_id", "artist_id") values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?): database is locked' => skipping!
I tried to the following things:
- using WAL (PRAGMA journal_mode=WAL) => same problem
- using a shared Sql connection pool, for the scanning thread and the rest of the Wt application (pool size is 10) => same problem
- using a shared Sql connection pool, for the scanning thread and the rest of the Wt application (pool size is 1) => it seems to never lock, but the performance is significantly degraded
What do you think? I am using Wt 3.3.3 on Debian 8.
Regards,
Replies (4)
RE: database is locked - Added by Koen Deforche over 8 years ago
Hey Emeric,
'database is locked' is a problem that cannot be fixed in Sqlite3. In a project in the past we ended up splitting the database in different files just so that two processes could insert data (each in their own) without this problem occurring.
Using WAL improved the situation but it's only a statistical improvement: it simply reduces the chance of it happening, but it still happens when the journal is being processed.
I am afraid that what you're trying to do is beyond the capabilities of Sqlite3.
Koen
RE: database is locked - Added by Emeric Poupon over 8 years ago
Hello,
Thanks for your answer. I was afraid to read such a response...
Actually, I have already thought about splitting up the database into two parts (user related data and media related data).
But it seems to be quite a lot of work. I think I will first set the pool size of sql connections to 1 and decide later.
Thanks for your help.
Emeric
RE: database is locked - Added by Emeric Poupon over 8 years ago
Here is a feedback that talks about the "database is locked" problem: http://beets.radbox.org/blog/sqlite-nightmare.html
Basically, it seems to help a lot to compile sqlite3 using HAVE_USLEEP=1. I am wondering if it is the case for the version shipped with Wt?
Regards,
RE: database is locked - Added by Emeric Poupon about 8 years ago
I switched from the shipped sqlite3 version to the system one (on debian) and indeed it seems to solve the very long waits I can have from time to time on the database accesses.
I can confirm the shipped version of sqlite3 is compiled without usleep support on my Debian testing.
Ticket created: http://redmine.webtoolkit.eu/issues/4792