#47469 [Opn-Csd]: beginTransaction causing deadlock in SQLite3
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=47469edit=1
#47469 [Opn]: beginTransaction causing deadlock in SQLite3
ID: 47469 User updated by: rjohnson at intepro dot us Reported By: rjohnson at intepro dot us Status: Open Bug Type: PDO related Operating System: Windows XP SP3 PHP Version: 5.2.8 New Comment: 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 Previous Comments: [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=47469edit=1
#47469 [NEW]: beginTransaction causing deadlock in SQLite3
From: rjohnson at intepro dot us Operating system: Windows XP SP3 PHP version: 5.2.8 PHP Bug Type: PDO related Bug description: beginTransaction causing deadlock in SQLite3 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 bug report at http://bugs.php.net/?id=47469edit=1 -- Try a CVS snapshot (PHP 5.2): http://bugs.php.net/fix.php?id=47469r=trysnapshot52 Try a CVS snapshot (PHP 5.3): http://bugs.php.net/fix.php?id=47469r=trysnapshot53 Try a CVS snapshot (PHP 6.0): http://bugs.php.net/fix.php?id=47469r=trysnapshot60 Fixed in CVS: http://bugs.php.net/fix.php?id=47469r=fixedcvs Fixed in CVS and need be documented: http://bugs.php.net/fix.php?id=47469r=needdocs Fixed in release: http://bugs.php.net/fix.php?id=47469r=alreadyfixed Need backtrace: http://bugs.php.net/fix.php?id=47469r=needtrace Need Reproduce Script: http://bugs.php.net/fix.php?id=47469r=needscript Try newer version: http://bugs.php.net/fix.php?id=47469r=oldversion Not developer issue: http://bugs.php.net/fix.php?id=47469r=support Expected behavior: http://bugs.php.net/fix.php?id=47469r=notwrong Not enough info: http://bugs.php.net/fix.php?id=47469r=notenoughinfo Submitted twice: http://bugs.php.net/fix.php?id=47469r=submittedtwice register_globals: http://bugs.php.net/fix.php?id=47469r=globals PHP 4 support discontinued: http://bugs.php.net/fix.php?id=47469r=php4 Daylight Savings:http://bugs.php.net/fix.php?id=47469r=dst IIS Stability: http://bugs.php.net/fix.php?id=47469r=isapi Install GNU Sed: http://bugs.php.net/fix.php?id=47469r=gnused Floating point limitations: http://bugs.php.net/fix.php?id=47469r=float No Zend Extensions: http://bugs.php.net/fix.php?id=47469r=nozend MySQL Configuration Error: http://bugs.php.net/fix.php?id=47469r=mysqlcfg
#47469 [Opn]: beginTransaction causing deadlock in SQLite3
ID: 47469 User updated by: rjohnson at intepro dot us Reported By: rjohnson at intepro dot us Status: Open Bug Type: PDO related Operating System: Windows XP SP3 PHP Version: 5.2.8 New Comment: 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. Previous Comments: [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=47469edit=1
