Why does SQLite give a "database locked" for the second query in a transaction when using Perl DBD :: SQLite?

Question

Is there a known problem with SQLite giving the "database locked" error for the second query in one transaction when using Perl DBD :: SQLite? Scenario: Linux, Perl DBI, AutoCommit => 0, a routine with two blocks of code (using blocks to localize variable names). In the first block of code, the request descriptor is created with the prepare () command in the select statement, it is executed (), and the block is closed. The second code blocks another request descriptor, is created by preparing for the update statement, and often (30% of the time) SQLite / DBI gives a database lock error at this point. I think the error occurs during preparation (), and not at runtime ().

My job is to commit after the first request. (Calling the completion of the first request did not help). I prefer not to commit a few reasons related to elegance and efficiency. The source code worked fine for many years with Postgres as a database. I tried sqlite_use_immediate_transaction with no effect.

In all other situations, I found that SQLite works very well, so I suspect that this is an oversight in the DBD driver, not a problem with SQLite. Unfortunately, my current code is a bunch of scripts and modules, so I don’t have a short test case with one file.

Answer 1

In any case, this is not related to this: "Transaction and database lock" from DBD::SQLite perldoc?

An AutoCommit or begin_work transaction is nice and convenient, but sometimes you can get an annoying "database locked" error. This usually happens when someone starts a transaction and tries to write to the database while the other person is reading from the database (in another transaction). You may be surprised, but SQLite does not lock the database when you are just starting a normal (pending) transaction to maximize concurrency. It reserves a lock when you issue a statement for writing, but while you are not really trying to write using a commit statement, it allows other people to read from the database. However, reading from the database also requires a shared lock, and this prevents giving you the exclusive lock that you reserved, so you get a "database locked" error, and other people will get the same error if they try to write later, since you still have a pending lock. busy_timeout does not help in this case.

To avoid this, enter the transaction type explicitly. You can start an immediate transaction (or start an exclusive transaction) for each transaction or set the sqlite_use_immediate_transaction database processing attribute to true (starting from 1.30_02) to always use an immediate transaction (even if you just use begin_work or disable AutoCommit.).

 my $dbh = DBI->connect("dbi:SQLite::memory:", "", "", { sqlite_use_immediate_transaction => 1, }); 

Note that this only works when all connections use the same (not deferred) transaction. See http://sqlite.org/lockingv3.html for more details.