SQLite Forum

Database is locked when "upgrading" from read to write
Login

Database is locked when "upgrading" from read to write

(1) By RenatoF (renatof) on 2021-04-27 09:45:08 [link] [source]

Hello,

I am writing an app using PHP and SQLite (using PDO) - not using WAL.

There are cases I begin a transaction, retrieve data using SELECT and immediately after use part of the data returned (usually a primary key) in a subsequent INSERT/UPDATE/DELETE. I do not remember facing any lock issues with this procedure.

However, in cases I need to "process" the retrieved data (what takes 1~2 seconds) before proceeding with the INSERT/UPDATE/DELETE, I am often presented an error:

SQLSTATE[HY000]: General error: 5 database is locked

From what I understood by reading the documentation and some forum threads, when the transaction is started (PDO uses just 'BEGIN', as far as I know, thus defaults to BEGIN DEFERRED), the file access is deferred and really happens at the moment the first SQL instruction involving data is processed. If it's a SELECT, the database file is opened for read and if other operations involving writing (INSERT, UPDATE, DELETE) come in sequence, there's an automatic "upgrade" to a write transaction.

The questions that arose in my mind are:

  1. In the cases the first operation within a transaction (started with just 'BEGIN' - deferred) involves writing, is the file access timeout (defaults 60 seconds for SQLite in PDO) respected? I expect a Yes as answer here (otherwise it would be a great coincidence I never faced issues with database locks in such cases I do write/delete rows as the 1st operation within a transaction).

  2. the fact of me never facing such error in the cases the data retrieved in the first SELECT operation is used immediately after in INSERT/UPDATE/DELETE is just luck (because the "time window" another process could lock the file is too short and minimizes the probability of it happening) or is there a very short (at least) time this "upgrade" to writing can wait before failing with the busy error?

  3. do you (if the authors/maintainers read here) consider adding a waiting time/retries in the process of "upgrading" a read transaction to a write transaction in BEGIN DEFERRED transactions? This way we would not need to commit a transaction just to start another one for writes.

  4. BEGIN IMMEDIATE transactions wait for the timeout (default 60 seconds in PHP's PDO) when accessing a file for write or do they immediately return the busy error if the file is locked by another process?

  5. Is there a way to manually "upgrade" a transaction started with 'BEGIN' in order to guarantee write access is granted right from beginning (would be useful because PDO does not allow passing 'IMMEDIATE' in PDO::beginTransaction(); and the framework I use handles transactions -- a workaround I can think of is starting the transaction as usual and then sending 'COMMIT;BEGIN IMMEDIATE;' query manually in sequence).

Thanks.

(2) By Simon Slavin (slavin) on 2021-04-27 14:45:24 in reply to 1 [link] [source]

You are going to have a problem understanding what's going on because you're using the PDO module of PHP. The PDO module standardises database handling, so that all databases can be addressed the same way, with the same functions and commands in the same order. To make this happen it does a bunch of stuff behind the scenes, including issuing SQL commands you don't know about. One of the most frequent things systems do with this is create their own transactions, which means that your own transaction commands may be ignored or overridden. This may be why you needed to ask us questions in the first place: because you can't tell what's going on because PDO is doing things you don't know about.

Would it be feasible in your project to use the sqlite3 module of PHP instead ? Functions of this module correspond exactly with SQLite's C API: the SQLite documentation you've obviously understood applies exactly to them, with no overriding.

If you are using the sqlite3 module, then issuing BEGIN EXCLUSIVE should secure an immediate exclusive lock on the database, which means there'll never be any escalation, and that any errors on securing a lock should be returned for the BEGIN EXCLUSIVE command, not for any subsequent command. Of course, if you issue your own BEGIN of any kind you should also be issuing your own END.

Answers to your numbered questions: Each attempt at locking can last for the timeout time. Each attempt includes timeout sleeps (increasing backoff) to allow other connections to complete their work. The sleeps start short and get longer. A single operation may involve more than one lock, sometimes different kinds of lock. I don't know when PDO does its locking but you cannot depend on PDO to act exactly like the SQLite documentation. I think what you want is BEGIN EXCLUSIVE.

(4) By RenatoF (renatof) on 2021-04-27 18:03:44 in reply to 2 [link] [source]

Hello Simon, thanks for taking a time to reply.

You are going to have a problem understanding what's going on because you're using the PDO module of PHP.

I am not having problems understanding what's going on in regards to PDO. I know the commands it issues when PDO::beginTransaction() and PDO::commit() are called. PHP is open source and the specific sqlite3 commands - for the PHP version I am using - are visible here.

One of the most frequent things systems do with this is create their own transactions, which means that your own transaction commands may be ignored or overridden.

Transactions are not "overridden" (at least not for the SQLite driver). I can issue PDO::beginTransaction(), PDO::query() / PDO::exec(), PDO::commit() at my discretion.

This may be why you needed to ask us questions in the first place: because you can't tell what's going on because PDO is doing things you don't know about.

If you pay attention to the questions I've asked, they are all about my interest in better understanding how SQLite behaves to it's own commands, not about PDO. =)

Would it be feasible in your project to use the sqlite3 module of PHP instead ?

No. And not necessary. The problem is more related to the fact I am using a PHP framework (Adianti Framework) whose TTransaction::open('dbname') method instantiates PDO passing the SQLite file path and automatically starts a transaction using PDO::startTransaction() (which is committed when I call TTransaction::close()) and I have some reasons not to modify the FW source.

I am guessing I could END that transaction and start a new (BEGIN IMMEDIATE) using PDO::exec('END;BEGIN IMMEDIATE;') before proceeding the commands I want to guarantee the lock will be acquired (no matter how much time it takes - I just want to prevent an immediate "database is locked" error without retries).

Or, as a last resort, I could just instantiate the connection as a 'standalone' PDO object by myself (not using the fw's TTransaction class) since I need more control about the transaction in very rare situations.

However, before doing so, I am trying to understand if the behavior I am observing is expected/normal, thus the questions were made here.

Each attempt at locking can last for the timeout time.

This is not exactly what I am observing when the first statement in a transaction (DEFERRED) is a SELECT. When a DELETE statement is sent in sequence, the upgrade to a write lock is failing immediately, apparently without even a single retry. My expectation is this "upgrade" should do retries up to the same time defined as timeout (60 seconds by default) when the first statement of a transaction involves writing.

If this is the normal behavior of SQLite for DEFERRED transactions (not waiting at least the defined timeout for the lock upgrade (from a read-only to a read-write lock), I strongly believe this is the MAJOR barrier for all web apps developers who thinks "SQLite is not powerful enough" for their projects and push MySQL/PostgreSQL on their users. Waiting for the lock to be acquired on "upgrades" instead immediately failing would basically mimic how server-client DBMS work.

(6) By Keith Medcalf (kmedcalf) on 2021-04-27 22:40:29 in reply to 4 [source]

Issuing a command to "BEGIN DEFERRED TRANSACTION" does not actually begin a transaction. It merely turns off the autocommit mechanism. Neither the "commit" nor "rollback" commands commit or rollback a transaction. They merely turn the autocommit mechanism back on (and rollback sets a flag that tells the autocommit processing to discard the update).

BEGIN IMMEDIATE TRANSACTION both (a) turns off the autocommit mechanism AND (b) acquires an "intent to read" lock and (c) acquires an "intent to update" lock.

BEGIN EXCLUSIVE TRANSACTION both (a) turns off the autocommit mechanism AND (b) acquires an "intent to read" lock and (c) acquires an "intent to update" lock and (d) and "exclusive" lock (meaning that there must be no readers either).

If the autocommit mechanism is turned off, then processing a retrieval/read operation will acquire a "intent to read but not update" transaction if that level of transaction is not already held by the connection.

If the autocommit mechansim is turned off, then processing an update/insert/write operation will acquire a "intent to read" and "intent to update" transaction if that level of transaction is not already held on that connection.

In both the above cases the acquisition of the "intent to read" or "intent to update" lock may fail if some other connection holds a lock which prevents acquisition of the requested lock.

If it is possible for the lock requested to eventually be granted, then the busy_timeout will be used to "wait" for the lock before returning BUSY.

If it is not possible that the lock requested can EVER be granted, then (a) there is no point waiting (it is a waste of time and effort) and (b) the BUSY will be returned immediately.

A "commit" operation may fail (BUSY) if an exclusive lock is required but other connections hold read locks that are not released before the busy timeout expires.

(7) By RenatoF (renatof) on 2021-04-28 11:37:22 in reply to 6 [link] [source]

Hello Keith. Thank you for taking a time to reply.

In both the above cases the acquisition of the "intent to read" or "intent to update" lock may fail if some other connection holds a lock which prevents acquisition of the requested lock.

If it is possible for the lock requested to eventually be granted, then the busy_timeout will be used to "wait" for the lock before returning BUSY.

I am not using 'BEGIN IMMEDIATE' or 'BEGIN EXCLUSIVE' at any moment and I am still facing 'database is locked' errors, specifically between a SELECT and a DELETE within the same transaction (when an 'upgrade' of the "intent to read" lock needs to be upgraded to "intent to update" is necessary). So, could you please better describe why the request fails immediately? I can't understand why it does not respect the timeout for retries in the attempt to upgrade the lock.

Also, direct answers - even short - to the questions in my original post would be much appreciated.

(8) By Keith Medcalf (kmedcalf) on 2021-04-28 12:15:02 in reply to 7 [link] [source]

The request fails immediately so that you can abort the transaction entirely and start over because a deadlock has been detected.

Lets suppose you start a transaction with an ordinary BEGIN (as in deferred, as in just turn off the auto-commit machinery for that connection). At this point the connection holds no locks at all.

When you issue the first "SELECT" a "read lock" must be acquired. This will only be denied if some other connection has an "exclusive" lock (as in the database is presently being written to -- an actual write, not an "intent to write").

Now that you have acquired this lock, you are preventing any other connection from acquiring an exclusive lock (ie, it will not be able to commit its "intent to write" operation).

Holding the "read" lock you attempt to do a "DELETE". This requires an "intent to write" lock. This will fail IMMEDIATELY (no waiting) if another connection already holds an "intent to write" lock because that other connection will require an "exclusive lock" (all readers get out) in order to "commit" its changes to the database to free the "intent to write" lock. Your holding of a "read lock" will prevent that other connection from ever being able to complete its "intent to write". No amount of waiting will resolve this "deadly embrace" so you get a BUSY error immediately so that you can abort your transaction (and release the read lock) so that the other writer can complete its transaction.

This is a long known and much studied issue where multiple locks may be required.

(11) By RenatoF (renatof) on 2021-05-02 22:12:15 in reply to 8 [link] [source]

So, if I understood correctly, the only way I could avoid having to write my own retry mechanism while using a non-WAL SQLite database is by using BEGIN IMMEDIATE on all transactions (or at least all those are expected to perform a write at some point).

This would make all transactions to acquire a write lock right from start (respecting the timeout if necessary instead returning SQLITE_BUSY immediately) and the "deadly embrace" you mentioned would never occur, correct?

(12) By Keith Medcalf (kmedcalf) on 2021-05-03 02:10:41 in reply to 11 [link] [source]

That is correct.

Once you begin a transaction with BEGIN IMMEDIATE (and it is successful) you will hold both a "read" and a "write" lock. No subsequent SQL command (before COMMIT) will return SQLITE_BUSY because you have all the locks you require. Other connection ordinary BEGIN and "read" operations will succeed but attempts to acquire a "write" lock will fail.

The exception, of course, is that COMMIT may return SQLITE_BUSY (after the timeout expires) if it is unable to acquire the EXCLUSIVE lock required to actually write the changes to the database (non-WAL journal) (because not all read locks have been released). (Since WAL journalling does not require exclusive access to the database (as in all readers are done) in order to commit the transaction to the database, COMMIT is also guaranteed to succeed and will not return SQLITE_BUSY).

However, a failure of COMMIT can be retried since in this circumstance a deadlock will never occur.

If you are going to use transactions like this:

BEGIN SELECT ... --- compute update using read data --- UPDATE ...

and the UPDATE fails with SQLITE_BUSY you need to ROLLBACK the transaction and start over (including re-reading the data because some other connection may have changed it in the interim).

(13) By Keith Medcalf (kmedcalf) on 2021-05-03 02:15:30 in reply to 12 [link] [source]

Actually, it would in theory be possible for an UPDATE after a successful BEGIN IMMEDIATE to return SQLITE_BUSY indicating that it required EXCLUSIVE access to the database in order to "spill the page cache". However, this should always be a transient condition and you can retry the failing command (some reader simply has not released its read transaction in a timely fashion). This could only happen in non-WAL journal mode.

(15) By RenatoF (renatof) on 2021-05-03 13:57:21 in reply to 13 [link] [source]

This is a rare case, where SQLITE_BUSY is returned after the timeout (60 defaults in case of PHP PDO), given all transactions which perform UPDATEs are IMMEDIATE, correct?

(17.1) By Keith Medcalf (kmedcalf) on 2021-05-03 17:17:56 edited from 17.0 in reply to 15 [link] [source]

That is correct. It should be exceedingly rare, but it can happen. However, you only need to retry the single statement, not the entire transaction.

For example if you do, for example, UPDATE t SET y = x*14; on a 700 million row table t then the update will affect ALL 700 million rows. This is likely to exceed the amount of memory available in the page cache to hold updated pages so in order to process the statement, an EXCLUSIVE lock is required on the database so it can be updated (spill the cache). If this lock cannot be acquired within the timeout then an SQLITE_BUSY error will occur.

(18) By RenatoF (renatof) on 2021-05-03 18:05:30 in reply to 17.1 [link] [source]

Thank you for clarification, Keith.

I think I am on track to resolve my problems now.

(14) By RenatoF (renatof) on 2021-05-03 13:55:06 in reply to 12 [link] [source]

If you are going to use transactions like this:

BEGIN SELECT ... --- compute update using read data --- UPDATE ...

and the UPDATE fails with SQLITE_BUSY you need to ROLLBACK the transaction and start over (including re-reading the data because some other connection may have changed it in the interim).

Here you are talking about a SQLITE_BUSY error that happened after the timeout for retries is exceeded (considering the transaction is IMMEDIATE)?

(16) By Keith Medcalf (kmedcalf) on 2021-05-03 17:03:24 in reply to 14 [link] [source]

No, this is after a regular begin deferred. You should not get a SQLITE_BUSY timeout after a BEGIN IMMEDIATE (unless the failure is to acquire an exclusive lock to spill the cache).

(3) By Gunter Hick (gunter_hick) on 2021-04-27 15:39:39 in reply to 1 [link] [source]

AFAIK (pertaining to non-WAL mode), one cannot upgrade a read transaction to a write transaction until all other read transactions have finished.

In your case I suspect the following:

Connection A starts as a reader and does some processing.

Connection B starts as a reader and wants to upgrade to a writer; it needs to wait for connectionA to finish.

Connection A now wants to upgrade too. This is a deadlock, because it would need connection B to finish first - which is waitng for connection A. Since no progress can be made, connection A is notfied of the deadlock.

(5) By RenatoF (renatof) on 2021-04-27 18:07:36 in reply to 3 [link] [source]

Hello Gunter Hick, thanks for taking a time to reply.

AFAIK (pertaining to non-WAL mode), one cannot upgrade a read transaction to a write transaction until all other read transactions have finished.

Transaction - Section 2.1 explains a read transaction cannot be upgraded to a write transaction only in cases another write transaction is in progress. Other reads can be operating - they just won't see the changes made by the writer unless they finish their own transactions and start a new.

(9.1) By Keith Medcalf (kmedcalf) on 2021-04-28 12:30:11 edited from 9.0 in reply to 1 [link] [source]

  1. Yes. An "intent to write" lock requires an "intent to read" lock. You cannot "write" without "reading" first.

  2. An INSERT/UPDATE/DELETE acquires both a "read" and an "intent to write" lock. If the next statement is a SELECT there is no additional lock processing required because you already hold a "read" lock.

  3. No. Holding a "read" lock will prevent the other updater (holding the "intent to write" lock) from completing their work and committing that other transaction. You must release your read lock in order to permit the other writer to commit. (however in WAL mode you holding a read does not prevent the other writer from committing but you will still not be able to acquire an "intent to write" even after the other connection commits because you are no longer holding a "current view" of the database, unless you release and re-acquire your locks).

  4. No. A bare BEGIN (as in BEGIN DEFERRED) does not start a transaction. It turns off the auto-commit machinery so that the transaction commenced by the next statement is not automatically committed at the end of the execution of that statement. If that statement is a "read" statement, then the transaction is a read transaction. If that statement is a "write" statement, then the transaction is a write transaction. BEGIN IMMEDIATE and BEGIN EXCLUSIVE both turn off the auto-commit machinery and start a transaction (write or exclusive respectively).

(10.1) By Keith Medcalf (kmedcalf) on 2021-04-28 12:40:30 edited from 10.0 in reply to 1 [link] [source]