Perl Dbi Sqlite Commit Or Detach Fails When Autocommit Is Set To False

The following code fails on my Windows 10 machine running Strawberry Perl 5.24: use DBI; unlink glob('*.db3'); my $source = DBI->connect('dbi:SQLite:dbname=first.db3',q(),q(),{

Solution 1:

According to the documentation, when AutoCommit mode is turned off:

The default transaction behavior of SQLite is deferred, that means, locks are not acquired until the first read or write operation, and thus it is possible that another thread or process could create a separate transaction and write to the database after the BEGIN on the current thread has executed, and eventually cause a "deadlock". To avoid this, DBD::SQLite internally issues a BEGIN IMMEDIATE if you begin a transaction by calling begin_work or by turning off AutoCommit (since 1.38_01).

If you really need to turn off this feature for some reasons, set sqlite_use_immediate_transaction database handle attribute to false, and the default deferred transaction will be used.

(This seems like undesirable behavior, and maybe it's because I'm tired but I don't see how you can get deadlocks; just errors when trying to lock a database that's already locked by another connection to it)

But anyways:

$ sqlite3 second.db3sqlite> attach database 'first.db3' as chunk_db;sqlite> begin immediate;sqlite> detach database chunk_db;
Error: database chunk_db is locked

That looks familiar...

The default behavior when AutoCommit is off means you're always in a transaction with an acquired RESERVED lock. This has some unusual side-effects as you're seeing.

So, solutions in my order of preference:

• Turn on AutoCommit mode and manually begin transactions (With $dbh->begin_work).
• Skip the DETACH since you're closing the database connection anyways.
• Leave AutoCommit disabled and set the sqlite_use_immediate_transaction option to 0 when connecting to the database file.