Hướng dẫn php sqlite database locked
I have a PHP script that does the following in the order presented: Show
I'm using PDO as the vehicle for both MySQL and SQLite. Here is the code for the insert:
(please forgive the usage of variables in the query, rather than a proper prepared statement; I removed the bound parameters to see if that was the problem) Using other tools (SQLite Manager for Firefox, SQLite Database Browser for Windows), I am able to access the database and write to it. This script is the only thing that touches this particular database. The permissions on the database file are When running the script (using So my question, then, is this: Is there something about running a PHP script from command line that prevents interaction with a SQLite database? If so, what? If not, what could be the issue here? (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:
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 The questions that arose in my mind are:
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 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 (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.
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.
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.
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. =)
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.
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 [link] [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.
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, (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]
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 [source]Hello Gunter Hick, thanks for taking a time to reply.
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]
|