ID: 47469
User updated by: rjohnson at intepro dot us
Reported By: rjohnson at intepro dot us
-Status: Open
+Status: Closed
Bug Type: PDO related
Operating System: Windows XP SP3
PHP Version: 5.2.8
New Comment:
I believe I found a fix for this (or at least a workaround). It looks
like the real problem was with PDO::prepare(). Prepared statements must
be prepared prior to calling PDO::beginTransaction()
Right now this is working for us in test:
1. Create the SQLite3 PDO connection
2. Prepare any statements necessary
3. Set a loop test variable and start loop
4. Start try{} block
5. Call beginTransaction() to get out of autocommit mode
6. Attempt to execute prepared statement -> could throw PDOException
"DATABASE IS LOCKED", skip to step 9
7. Call PDO::commit() to commit changes
8. Set loop test variable to false and/or break loop
9. Catch "DATABASE IS LOCKED" PDOException, sleep for some period of
time and reiterate the loop (Go to step 3)
If this passes our tests, I'll make a note of it in the online manual.
Previous Comments:
------------------------------------------------------------------------
[2009-03-25 16:02:08] rjohnson at intepro dot us
Hello. Just curious as to if anyone has looked at this since it has
been open for a month. It is still an issue for us with version 5.2.8
and I did not see any fixes for it in the changelog for 5.2.9. If there
is anything you need from me, please let me know.
Thanks
------------------------------------------------------------------------
[2009-02-21 19:51:57] rjohnson at intepro dot us
It looks like the multiple transaction thing might be a SQLite3
bug/feature as I am able to open 2 command-line interfaces using the
SQLite3 command-line tool and issue a BEGIN on both. I can do an insert
with one, but the insert on the other fails with the database locked
error (just like the PDO problem).
There is still a problem in PDO, however. When we capture a database
locked exception we have an open transaction. If we try to commit or
rollback the open transaction the process fails with an error indicating
other transactions are open (presumably from process 1). In my SQLite
command-line test I can commit the transaction from process 2 (where the
lock exception was encountered) while the transaction from process 1 is
open. The problem with PDO is in the loop - if database is locked we
can't commit or rollback the open transaction, when we try to begin the
transaction in the next iteration of the loop we get this error: "There
is already an active transaction". If we handle that exception and keep
the transaction open, then the 2 scripts deadlock. So if we could
commit or rollback the transaction from process 2 when we see that the
database is locked (as the SQLite command-line client allows) that might
work.
------------------------------------------------------------------------
[2009-02-21 18:35:03] rjohnson at intepro dot us
Description:
------------
When executing 2 separate processes that insert or update a SQLite3
database, PDO allows both to begin a transaction rather than indicating
that another transaction has locked the database. This causes deadlock.
Using SQLite2 we would issue a BEGIN and catch a SQLITE_BUSY, then
usleep and try again. This worked flawlessly.
Reproduce code:
---------------
http://beacon.intepro.us/pdoSqliteBug.html
Expected result:
----------------
We run this code from 2 tabs in Firefox with name=0 and name=1 passed
as $_GET params. The sleep(1) allows us enough time to execute both
processes simultaneously.
The beginTransaction in the 2nd script should detect that SQLite is
busy (the 1st transaction has a lock on the file), the code should sleep
for .25 of a second and try again until the first process is done, then
the second should complete. Both processes should complete in roughly
20 seconds.
Actual result:
--------------
Both scripts run indefinitely. If we stop the second process, the
first will complete.
------------------------------------------------------------------------
--
Edit this bug report at http://bugs.php.net/?id=47469&edit=1
