Skip Menu |

This queue is for tickets about the DBD-SQLite CPAN distribution.

Report information
The Basics
Id: 56444
Status: resolved
Priority: 0/
Queue: DBD-SQLite
People
Owner: Nobody in particular
Requestors: mdootson [...] cpan.org
Cc:
AdminCc:
Bug Information
Severity: Wishlist
Broken in: 1.30_02
Fixed in: (no value)

History Show all quoted text
  Fri Apr 09 17:44:14 2010 mdootson [...] cpan.org - Ticket created
The documentation for 1.30_2 describes sqlite_use_immediate_transaction. Have you got a test case that demonstrates the described problem? According to my reading of the sqlite docs, the described situation cannot happen. You could only ever get a SQLITE_LOCKED error when the conflict arises within the calling process and never in the situation described in the module POD. SQLite locking would have to be fundamentally broken to exhibit the behaviour described in the POD. You should always get SQLITE_BUSY back in the situations described. Of course, you can always contrive within a single process to produce a SQLITE_LOCKED error. But that isn't what the POD says sqlite_use_immediate_transaction is for.
  Fri Apr 09 18:45:04 2010 ishigaki [...] cpan.org - Correspondence added
See https://rt.cpan.org/Ticket/Display.html?id=46289, and the Book from Apress which describes about the deadlock more thoroughly. On 2010-4-09 Fri 17:44:14, MDOOTSON wrote: Show quoted text
> The documentation for 1.30_2 describes
sqlite_use_immediate_transaction. Show quoted text
> Have you got a test case that demonstrates the described problem? > According to my reading of the sqlite docs, the described situation
cannot Show quoted text
> happen. You could only ever get a SQLITE_LOCKED error when the
conflict Show quoted text
> arises within the calling process and never in the situation
described in Show quoted text
> the module POD. SQLite locking would have to be fundamentally broken
to Show quoted text
> exhibit the behaviour described in the POD. You should always get > SQLITE_BUSY back in the situations described. Of course, you can
always Show quoted text
> contrive within a single process to produce a SQLITE_LOCKED error.
But Show quoted text
> that isn't what the POD says sqlite_use_immediate_transaction is for.
  Fri Apr 09 18:45:07 2010 The RT System itself - Status changed from 'new' to 'open'
  Fri Apr 09 18:45:10 2010 ishigaki [...] cpan.org - Status changed from 'open' to 'resolved'
  Sun Apr 11 04:07:40 2010 mdootson [...] cpan.org - Correspondence added
Hi, Just an update. Your solution seems to be absolutely the best one. I've tested it against contrived locking conflicts with multiple attached database files in addition to the lock escalation issue, versus catching the SQLITE_BUSY messages in my own code and the plain fact is that SQLite's inbuilt busy-timeout (which 'begin immediate tran' always brings into play) seems always more efficient than rollback and retrying in your own code. I can imagine a situation where many readers are locked out by a single long running transaction but my (limited) testing suggests it is still quicker to use the inbuilt busy callback via 'befin immediate tran'. May I suggest that you change the POD. The issue regarding 'pending locks' being left around only existed in SQLite versions >3.2.8 and < 3.4.0. The POD also seemed to suggest (to me at least) that it matters how other processes are accessing the db file. It does not. This might be an issue if sqlite 'cached' access were being used - but as it is not used in DBD::SQLite, I only went as far as confirming that clients not using cached access are unaffected by what happens in the cache clients. Also, you may like to consider making 'begin immediate transaction' the default for $dbh->begin_work. In any situation where there is concurrent access, the current default 'deferred' mode will cause SQLITE_BUSY errors that you have to write code to handle, whereas 'begin immediate transaction' will always work. Any benefit you might get from deferring your RESERVED lock only lasts until the first insert/delete/update statement in your transaction anyway. You might also like to remove 'begin exclusive transaction' as a suggestion, or at least make clear it isn't a preferred option. Just as 'begin immediate' appears to have no real-world downside, 'begin exclusive' seems to have no real world benefit with quite a lot of potential downside. (Though I have not tested the real impact). As I currently understand the SQLite locking process v's transactions reasonably well, but it will all fall out of my head by next week, would you appreciate a DBD::SQLite::Transactions.pod ? Thanks very much for your work on DBD::SQLite
  Sun Apr 11 04:07:43 2010 The RT System itself - Status changed from 'resolved' to 'open'
  Mon Apr 12 19:26:33 2010 ishigaki [...] cpan.org - Correspondence added
Doc/doc patch is always most welcome :) On 2010-4-11 Sun 04:07:40, MDOOTSON wrote: Show quoted text
> Hi, > > Just an update. Your solution seems to be absolutely the best one.
I've Show quoted text
> tested it against contrived locking conflicts with multiple attached > database files in addition to the lock escalation issue, versus
catching Show quoted text
> the SQLITE_BUSY messages in my own code and the plain fact is that > SQLite's inbuilt busy-timeout (which 'begin immediate tran' always > brings into play) seems always more efficient than rollback and
retrying Show quoted text
> in your own code. I can imagine a situation where many readers are > locked out by a single long running transaction but my (limited)
testing Show quoted text
> suggests it is still quicker to use the inbuilt busy callback via
'befin Show quoted text
> immediate tran'. > > May I suggest that you change the POD. The issue regarding 'pending > locks' being left around only existed in SQLite versions >3.2.8 and < > 3.4.0. The POD also seemed to suggest (to me at least) that it
matters Show quoted text
> how other processes are accessing the db file. It does not. This
might Show quoted text
> be an issue if sqlite 'cached' access were being used - but as it is
not Show quoted text
> used in DBD::SQLite, I only went as far as confirming that clients
not Show quoted text
> using cached access are unaffected by what happens in the cache
clients. Show quoted text
> > Also, you may like to consider making 'begin immediate transaction'
the Show quoted text
> default for $dbh->begin_work. In any situation where there is > concurrent access, the current default 'deferred' mode will cause > SQLITE_BUSY errors that you have to write code to handle, whereas
'begin Show quoted text
> immediate transaction' will always work. Any benefit you might get
from Show quoted text
> deferring your RESERVED lock only lasts until the first > insert/delete/update statement in your transaction anyway. > > You might also like to remove 'begin exclusive transaction' as a > suggestion, or at least make clear it isn't a preferred option. Just
as Show quoted text
> 'begin immediate' appears to have no real-world downside, 'begin > exclusive' seems to have no real world benefit with quite a lot of > potential downside. (Though I have not tested the real impact). > > As I currently understand the SQLite locking process v's transactions > reasonably well, but it will all fall out of my head by next week,
would Show quoted text
> you appreciate a DBD::SQLite::Transactions.pod ? > > Thanks very much for your work on DBD::SQLite >
  Mon Sep 20 22:06:34 2010 ishigaki [...] cpan.org - Subject changed from (no value) to 'immediate transaction should be on by default, and the doc be updated'
  Sun Sep 02 10:36:09 2012 ishigaki [...] cpan.org - Correspondence added
Made the default transaction behavior "immediate" in the trunk, and removed "begin exclusive" info. Thanks. On Tue Apr 13 08:26:33 2010, ISHIGAKI wrote: Show quoted text
> Doc/doc patch is always most welcome :) > > On 2010-4-11 Sun 04:07:40, MDOOTSON wrote:
> > Hi, > > > > Just an update. Your solution seems to be absolutely the best one.
> I've
> > tested it against contrived locking conflicts with multiple
attached Show quoted text
> > database files in addition to the lock escalation issue, versus
> catching
> > the SQLITE_BUSY messages in my own code and the plain fact is that > > SQLite's inbuilt busy-timeout (which 'begin immediate tran' always > > brings into play) seems always more efficient than rollback and
> retrying
> > in your own code. I can imagine a situation where many readers are > > locked out by a single long running transaction but my (limited)
> testing
> > suggests it is still quicker to use the inbuilt busy callback via
> 'befin
> > immediate tran'. > > > > May I suggest that you change the POD. The issue regarding 'pending > > locks' being left around only existed in SQLite versions >3.2.8 and
< Show quoted text
> > 3.4.0. The POD also seemed to suggest (to me at least) that it
> matters
> > how other processes are accessing the db file. It does not. This
> might
> > be an issue if sqlite 'cached' access were being used - but as it
is Show quoted text
> not
> > used in DBD::SQLite, I only went as far as confirming that clients
> not
> > using cached access are unaffected by what happens in the cache
> clients.
> > > > Also, you may like to consider making 'begin immediate transaction'
> the
> > default for $dbh->begin_work. In any situation where there is > > concurrent access, the current default 'deferred' mode will cause > > SQLITE_BUSY errors that you have to write code to handle, whereas
> 'begin
> > immediate transaction' will always work. Any benefit you might get
> from
> > deferring your RESERVED lock only lasts until the first > > insert/delete/update statement in your transaction anyway. > > > > You might also like to remove 'begin exclusive transaction' as a > > suggestion, or at least make clear it isn't a preferred option.
Just Show quoted text
> as
> > 'begin immediate' appears to have no real-world downside, 'begin > > exclusive' seems to have no real world benefit with quite a lot of > > potential downside. (Though I have not tested the real impact). > > > > As I currently understand the SQLite locking process v's
transactions Show quoted text
> > reasonably well, but it will all fall out of my head by next week,
> would
> > you appreciate a DBD::SQLite::Transactions.pod ? > > > > Thanks very much for your work on DBD::SQLite > >
> >
  Sun Sep 02 10:36:10 2012 ishigaki [...] cpan.org - Status changed from 'open' to 'patched'
  Mon Sep 24 15:06:32 2012 ishigaki [...] cpan.org - Correspondence added
DBD::SQLite 1.38_01 is released and now immediate transaction is on by default. Thanks.
  Mon Sep 24 15:06:33 2012 The RT System itself - Status changed from 'patched' to 'open'
  Mon Sep 24 15:06:33 2012 ishigaki [...] cpan.org - Status changed from 'open' to 'resolved'