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

go to bug id or search bugs for

Bug #51680 Sqlite returns without waiting for timeout
Submitted: 2010-04-28 10:49 UTC Modified: 2010-06-22 09:11 UTC
Votes:1
Avg. Score:4.0 ± 0.0
Reproduced:0 of 0 (0.0%)
From: ncoesel at dealogic dot nl Assigned: scottmac (profile)
Status: Closed Package: SQLite related
PHP Version: Irrelevant OS: Linux
Private report: No CVE-ID: None
View Developer Edit
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: ncoesel at dealogic dot nl
New email:
PHP Version: OS:

 

 [2010-04-28 10:49 UTC] ncoesel at dealogic dot nl 
Description:
------------
Sqlite has a protection mechanism that prevents firing two (or) more queries from the same process and wait for the database to become available. 

The typical behaviour is that every now and then (depending on the database load) a query exits immediately with "SQLSTATE[HY000]: General error: 5 database is locked". Setting other busy timeout values doesn't help.

I've attached a patch that solves the problem unless the database is locked for a very long time (as usual some error . The patch is created for an older version of PHP. I've checked the latest CVS version and I'm quite sure the patch will still apply.

Patches

sqlite_retry (last revision 2010-04-28 08:50 UTC by ncoesel at dealogic dot nl)

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2010-04-29 21:13 UTC] felipe@php.net
-Status: Open +Status: Assigned -Assigned To: +Assigned To: scottmac
 [2010-06-07 07:10 UTC] magicaltux@php.net
-Status: Assigned +Status: Duplicate
 [2010-06-07 07:10 UTC] magicaltux@php.net 
This bug is a duplicate of http://bugs.php.net/51295 (which includes a slightly 
better way of fixing this).
 [2010-06-07 09:13 UTC] ncoesel at dealogic dot nl 
I'm sorry but you are mistaken. Sqlite_busytimeout may be ignored by Sqlite when two threads of the same process execute a query. This is documented behaviour of Sqlite. See http://www.sqlite.org/c3ref/busy_handler.html in the 4th paragraph.

"The presence of a busy handler does not guarantee that it will be invoked when there is lock contention. If SQLite determines that invoking the busy handler could result in a deadlock, it will go ahead and return SQLITE_BUSY or SQLITE_IOERR_BLOCKED  instead of invoking the busy handler."

I did very thourough research on this and my patch is a way to solve it altough there is always a chance the database may be locked for too long. If that is a problem one might have exceeded Sqlite's capabilities :-)
 [2010-06-09 14:35 UTC] magicaltux@php.net
-Status: Duplicate +Status: Open
 [2010-06-09 14:35 UTC] magicaltux@php.net 
Indeed not a duplicate of 51295, went a bit too fast. Sorry.

By the way SQLite has its own view on threads: http://www.sqlite.org/faq.html#q6

Especially I do not like the idea of looping until the query suceeds, especially 
since it may end sleeping quite a long time. My guess is it would be up to the 
php part to check for BUSY status and do something else (stream_select?) before 
retrying (or maybe add an option retry_count to query()/exec() with default=0)

Anyway that's just my point of view, I'm reopening this bug and letting scottmac 
do what he deems appropriate.
 [2010-06-22 03:17 UTC] felipe@php.net
-Status: Assigned +Status: Closed
 [2010-06-22 03:17 UTC] felipe@php.net 
The feature requested in #51295 has been implemented.
 [2010-06-22 09:11 UTC] ncoesel at dealogic dot nl 
Sorry for keeping on nagging but this still doesn't fix Bug #51680. Setting a busy_timeout is only part of the solution.

@magicaltux: Looping may look unpleasant but that is exactly what every database engine is doing when tables are locked. There is no way around that. Locked is locked. Doing something else may not be a good idea either. If the database is locked it means another process is very busy. Doing something else will take CPU cycles away from that other process and actually prolong the time to wait.

Since Sqlite's (file system based) locking mechanism isn't very fine grained the chances of running into a lock when using Sqlite from multiple processes/threads (like PHP does) are very high. Because Sqlite aims to be simplistic it hands parts of handling locked databases to the user.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved. 		Last updated: Fri Dec 27 07:01:28 2024 UTC