Sqlite3 busy and yet server has no load nor concurrency

Hi,

Strange behavior here. On my dev machine code works, but on the
production machine I get the dreaded:

SQLite3::BusyException: database is locked INSERT INTO “comments” […]

What kind of bug could yield such behavior? It’s impossible the database
times out, there are no concurrent requests coming in, and the db is
only 150kB. Is there a possible race condition? Caching conflict?

In development mode, the DB time is less than 10ms and total time less
than 300ms with a timeout of 5000. The server is much faster than my dev
machine and loading a page on average takes less than 100ms.

Thanks for insights

Check the permissions on the sqlite3 file and make sure your account
(www-data) can read/write.

Good idea, but permissions are set correctly.

The incredible thing is that this app uses the same code as another app.
On the other one it works without any problem, and this one just decided
to give me hell. I guess it’s a pretty stupid option hidden somewhere in
my code that’s messing up everything, the thing is I don’t know where to
look for :frowning:

If anyone is interested, here the explanation:

I backed up the server without shutting it down, and it seems that the
db file was in an inconsistent state. So by correctly backing it up and
restoring the file, the problem got solved.

I was starting to get crazy!

Fernando,

glad you solved the issue!!
However, I must say this: don’t use SQLite in production environments!
It’s excellent for development, but flimsy and poor suited for
production.

Try MySQL or PostgreSQL for cost-free production ready databases.

Cheers!!

Well, giving a second thought, it all depends. :slight_smile:
If your app has very low concurrency and traffic, and if it is low in
transaction intensity and you don’t have any security constraints,
SQLite may be it is fit for production.

But… with SQLite you have poor support for concurrency, it’s no
scalable, you can’t secure it more than set permissions in your
directory, and since it is text-based, content is almost available to
anyone (by default).

However, I must say this: don’t use SQLite in production environments!
It’s excellent for development, but flimsy and poor suited for
production.

Why is that? It’s so easy to setup, maintain, backup (and fuck up…),
that I find it perfectly suited for production servers. Did you run into
major issues?

Try MySQL or PostgreSQL for cost-free production ready databases.

I used to run postgresql but I migrated to sqlite for its simplicity.
I’m tired about super-performance-hypeness that’s painful to maintain.

Unfortunately my apps have low traffic, so postgresql was really
overkill.

I’ll report on this list if I run into any scaling problems, but I guess
I be fine with sqlite for a good time.

Fernando P. wrote:

Unfortunately my apps have low traffic, so postgresql was really
overkill.

Postgres isn’t overkill. Juan P. is absolutely right: SQLite is
inappropriate for production. If you’ve been getting away with it till
now, consider yourself lucky – and don’t push your luck. Use a real
multiuser DB for production.

I’ll report on this list if I run into any scaling problems, but I guess
I be fine with sqlite for a good time.

You most likely guess wrong.

–Â
Marnen Laibow-Koser
http://www.marnen.org
[email protected]

Sent from my iPhone

Juan P. Genovese wrote:

But… with SQLite you have poor support for concurrency, it’s no
scalable, you can’t secure it more than set permissions in your
directory, and since it is text-based, content is almost available to
anyone (by default).

I just remembered that MySQL uses MyISAM tables by default, and they
only support table locking. So it’s basically the same concurrency
support as sqlite isn’t it? So why would MyISAM be cool and sqlite not?

Fernando P. wrote:

Juan P. Genovese wrote:

But… with SQLite you have poor support for concurrency, it’s no
scalable, you can’t secure it more than set permissions in your
directory, and since it is text-based, content is almost available to
anyone (by default).

I just remembered that MySQL uses MyISAM tables by default, and they
only support table locking. So it’s basically the same concurrency
support as sqlite isn’t it? So why would MyISAM be cool and sqlite not?

Hey, one other bit to that answer. MyISAM locks the whole table. I
believe SQLite locks the whole database. SQLite is really, really not
meant for multiuser situations.

Best,
–Â
Marnen Laibow-Koser
http://www.marnen.org
[email protected]

Sent from my iPhone

Fernando P. wrote:

Juan P. Genovese wrote:

But… with SQLite you have poor support for concurrency, it’s no
scalable, you can’t secure it more than set permissions in your
directory, and since it is text-based, content is almost available to
anyone (by default).

I just remembered that MySQL uses MyISAM tables by default, and they
only support table locking. So it’s basically the same concurrency
support as sqlite isn’t it? So why would MyISAM be cool and sqlite not?

Who the hell uses MyISAM, except for special cases? Sure it’s the
default, but even Rails’ migrations build InnoDB tables unless otherwise
specified – which, with MySQL, is the right thing to do.

But I’d really encourage you to use Postgres instead. At the cost of
very slightly more difficult setup, you get a hugely better DBMS.

Best,
–Â
Marnen Laibow-Koser
http://www.marnen.org
[email protected]

Sent from my iPhone

Well, for starters, that is why MySQL has two modes. MyISAM is only for
very special cases and it is not the Rails default for MySQL.
Also, MyISAM locks a table, SQLite locks the whole file (hence, the
whole database).
InnoDB is the way to go if you’ll use MySQL.
Postgre is another fantastic choice.
And if you’re feeling adventurous, try something like CouchDB (although
is a DB for a very different target than the traditional RDBMS)

Don’t get me wrong, SQLite is awesome for development. I use it everyday
for my side projects and even I’ll start embedding an SQLite database
for a Gnome2-ruby application I’ll be developing soon, but only for
config data and stuff like that.
But, in case of Web Applications, I would rather go with something much
more elaborated than SQLite.

Cheers!

Juan P.

Hey, one other bit to that answer. MyISAM locks the whole table. I
believe SQLite locks the whole database.

Indeed, you’re right. I’ll stick to PostgreSQL then.

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs