Re: [Ruby-DBI-devel] ruby-dbi SQLite bug

[Brian C. <B.C...@po...>]

Cheers.

Here's another problem I have found with sqlite: Ruby DBI holds a permanent
lock on the database while it's open. But this isn't a problem when using
the command-line 'sqlite' client.

For example, in one window I can do

  $ irb
  require 'dbi'
  a = DBI.connect("dbi:sqlite:mytest.db")

If I do the same in another window, then I get:

DBI::DatabaseError: database is locked(database is locked)
	from /usr/local/lib/ruby/site_ruby/1.6/dbi/dbi.rb:567:in `connect'

If I use the sqlite command-line client in this other window, I get:

  $ sqlite mytest.db
  select * from foo;
  SQL error: database is locked.

However, if instead of using Ruby DBI I run sqlite twice, and do a 'select *
from foo' individually from each instance, it's fine.

Now, I've had a look through the code, and I think the problem is as
follows: if the application has AutoCommit turned off (as mine has, and is
the default) then ruby-dbi immediately issues a 'begin transaction' as soon
as the database is opened. This is what's locking the database, since I can
duplicate this interlocking using the sqlite command-line program by typing
'begin transaction'.

That's rather unfortunate, as it limits access to a single client.

My application is transaction-based. I would in fact happily turn on
AutoCommit, *IF* the following worked correctly for all dbds while
AutoCommit was on:

    @dbh.transaction do
      .. stuff
    end

However I'm sure it doesn't, because dbi doesn't issue a 'begin
transaction' anywhere (only 'commit' and 'rollback'). Looking at the Mysql
dbd, for example, I'm sure that transactions while AutoCommit is on can't
possibly work, although I'm happy to be corrected if that's not true.

Perhaps the whole transaction model needs looking at in DBI? At minimum I
would expect DBI#transaction {...} to work both with or without AutoCommit,
which means that DBI ought to be responsible for issuing a suitable 'begin'
statement.

(The three DBDs I use, incidentally, are SQLite, Mysql and Oracle, the
latter using the DBD from the OCI8 package)

Regards,

Brian.