1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
---
date: "2004-10-09T22:22:38Z"
title: SQLite DB-Locking a Problem?
---
<p>
I've been playing with <a href='http://sqlite.org/'>SQLite</a> for the last couple of days. I'm trying to figure out if it's a suitable replacement for the ad-hoc storage format I've got for <a href='http://raggle.org/'>Raggle</a>. <a href='http://sqlite.org/'>SQLite</a> has a lot of benefits: it's fast, small (the , and free (public domain). It supports sub-selects, atomic transactions, and there's a complete set of <a href='http://sqlite-ruby.sf.net/'>Ruby bindings</a> (which are available as a <a href='http://rubygems.rubyforge.org/'>Gem</a>, but not as a <a href='http://debian.org/'>Debian</a> package... go figure). Of course all this goodness doesn't come without <a href='http://www.sqlite.org/omitted.html'>caveats</a>. Specifically, <a href='http://www.sqlite.org/faq.html#q7'>here's</a> the one that might be a problem for me:
</p>
<blockquote cite='http://www.sqlite.org/faq.html#q7'>
<p>
Locking in SQLite is very course-grained. SQLite locks the entire database. Big database servers (PostgreSQL, Oracle, etc.) generally have finer grained locking, such as locking on a single table or a single row within a table. If you have a massively parallel database application, you should consider using a big database server instead of SQLite.
</p>
<p>
Source: <cite><a href='http://www.sqlite.org/faq.html'>SQLite <acronym
title='Frequently Asked Questions'>FAQ</acronym></a></cite>
</p>
</blockquote>
<p>
<a href='http://raggle.org/'>Raggle</a> isn't a "massively parallel database application", but it can have up to N threads (where N is the number of feeds a user is subscribed to) attempting to write to the feed list simultaneously. I can probably queue database inserts and limit the threads to <code>SELECT</code>ing from their respective tables, but that smacks of hackery, which is what I was trying to avoid in the first place. I guess it's still a better solution than what <a href='http://raggle.org/'>Raggle</a> does right now. Ah well, <em>C'est la vie</em>.
</p>
|
