php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

Bug #47469 beginTransaction causing deadlock in SQLite3
Submitted: 2009-02-21 18:35 UTC Modified: 2009-04-07 20:05 UTC
From: rjohnson at intepro dot us Assigned:
Status: Closed Package: PDO related
PHP Version: 5.2.8 OS: Windows XP SP3
Private report: No CVE-ID: None
View Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
If you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: rjohnson at intepro dot us
New email:
PHP Version: OS:

 

 [2009-02-21 18:35 UTC] 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.

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2009-02-21 19:51 UTC] 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-03-25 16:02 UTC] 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-04-07 20:05 UTC] rjohnson at intepro dot us 
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.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved. 		Last updated: Fri Dec 27 00:01:30 2024 UTC