Re: [sqlite] Re: [unclassified] Re: [sqlite] getting rid of dirty SQLITE_BUSY workaround
On Tue, Mar 15, 2005 at 01:24:51PM -0500, Ned Batchelder wrote: > The best solution is to rollback entire transactions when the database is > busy. This means structuring your code so that all transactions can > rollback and retry. In my experience, this is necessary (though *far* less > frequently) even with the "big boy" databases. See, for example, > http://staff.newtelligence.net/clemensv/PermaLink,guid,826bc7c9-8b0f-4df6-aabe-e6c5377a9446.aspx Yet another reason why MVCC is such a beautiful thing. I've managed to accidentally get Oracle to abort a transaction due to detected deadlock only once or twice over the years, and I had to work pretty hard to do that - lots of complicated PL/SQL code which was taking row and/or table locks in differing table orders in different places. And that, of course, was an application bug. (You must take all locks in the same table by table order, everywhere. To damn bad that the RDBMS doesn't give you any real tools to help you verify that.) I don't remember ever seeing deadlock for any other reason in Oracle, and PostgreSQL (which has effectively the same MVCC model) should be the same. Databases using lock-based strategies rather than MVCC are, of course, more susceptible do deadlock problems under high concurrency. On the other hand, Oracle has more than once given me the joy of aborting my big huge special purpose transaction with a "not enough rollback space" error. Oops, gotta turn those annoyingly manual dba knobs some more. PostgreSQL is probably better in that respect, as its "rollback" space is effectively in the table itself, which will just keep getting bigger and bigger as necessary. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/
RE: [sqlite] Re: [unclassified] Re: [sqlite] getting rid of dirty SQLITE_BUSY workaround
> Regarding: ... As an example the ethernet card you're probably using > right > now. > ... they detect the collision, wait a short period, and retry. > > For those using Unix/Linux, would this provide the queuing needed for > those > apps with numerous parallel writes? > http://sqlrelay.sourceforge.net/sqlrelay/ The technique isn't specific to operating systems, or even computers. It's a queueing method humans usually use when trying to obtain services that are only intermittantly available. If you have larger requirements than just "I need to get this written as soon as possible" it may or may not work. Are your parallel writes in any way related or have to be performed in any specific order? If not it works fine. It might have the drawback that some processes can lock the database for long periods. The wait time for a write for other processes might be long. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
RE: [sqlite] Re: [unclassified] Re: [sqlite] getting rid of dirty SQLITE_BUSY workaround
The problem with queuing all writes is that you're playing fast and loose
with the isolation of the transactions. Imagine two threads (A and B).
Each reads the same value, increments it, and then writes it (Ar Aw, and Br
Bw). If the operations interleave properly (Ar Aw Br Bw), the final values
will be correct). If they don't (Ar Br Aw Bw), then A's write will be lost.
By queuing the writes, you've lost the association with the reads they were
based on, and the database doesn't have a chance to enforce the isolation of
the transactions. Don't forget: the whole reason the SQLite locks work the
way they do it to guarantee the proper transaction semantics.
To make a single writer thread work, you'd need to put the *entire*
transaction, including the reads, onto the writer thread. If you're going
to do reads on one thread, and then writes on another, you might was well
avoid the threading mess, and structure your operations like this:
begin;
/* do all the reads */
commit;
do {
begin;
/* do all the writes */
commit;
} until the commit succeeds;
The best solution is to rollback entire transactions when the database is
busy. This means structuring your code so that all transactions can
rollback and retry. In my experience, this is necessary (though *far* less
frequently) even with the "big boy" databases. See, for example,
http://staff.newtelligence.net/clemensv/PermaLink,guid,826bc7c9-8b0f-4df6-aa
be-e6c5377a9446.aspx
--Ned.
-Original Message-
From: Andrew Piskorski [mailto:[EMAIL PROTECTED]
Sent: Monday, March 14, 2005 8:23 PM
To: sqlite-users@sqlite.org; [EMAIL PROTECTED]
Subject: Re: [sqlite] Re: [unclassified] Re: [sqlite] getting rid of dirty
SQLITE_BUSY workaround
On Tue, Mar 15, 2005 at 12:21:15AM +0100, Thomas Lotterer wrote:
> On Sun, Mar 13, 2005, jed wrote:
>
> > [...] web applications fit well into the model of "many readers, one
> > writer", sqlite does this very well.
> >
> Well, there might be web applications which are read-only from the web's
> view. But to be honest, most of them also call for occasional writes.
> Think of a simple address book. Also I think of uses like tracking
> session cookies which also use occasional writes. In all those cases
I have not tried this with SQLite, but if I was using SQLite for such
an app, I assume I would need to serialize all such writes through a
single thread or process. E.g., in my web server, the connection
thread servicing the user request would send a message to my one
single db writer thread, saying, "Heh, please insert this data for
me!". Then the conn thread would simply block until the db writer
thread replies with, "Yup, your transaction is committed."
That is a uglier than what you'd do with a real server-based RDBMS
like Oracle or PostgreSQL, but it should scale fine until you have
either: One, very large numbers of hits on your simple and efficient
web app. Or two, a complicated web app with many potentially long
running transactions, etc. In the real world, the second concern is
much more likely to bite you than the first, and cries out for a more
capable, more general purpose database than SQLite.
It would be nice if SQLite had MVCC, which would let it scale much
further up into the PostgreSQL-like realm normally dominated by
client/server databases, but given the "simple, small, embedded" niche
that Dr. Hipp intended for SQLite, it's easy to see why adding MVCC
isn't any sort of priority, even if it could be done without making
the code much more complicated.
--
Andrew Piskorski <[EMAIL PROTECTED]>
http://www.piskorski.com/
RE: [sqlite] Re: [unclassified] Re: [sqlite] getting rid of dirty SQLITE_BUSY workaround
Regarding: ... As an example the ethernet card you're probably using right now. ... they detect the collision, wait a short period, and retry. For those using Unix/Linux, would this provide the queuing needed for those apps with numerous parallel writes? http://sqlrelay.sourceforge.net/sqlrelay/ Donald Griggs Opinions are not necessarily those of Misys Healthcare Systems nor its board of directors. -Original Message- From: Jay [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 15, 2005 9:51 AM2228882 As an example the ethernet card you're probably using right now. If a card tries to transmit at the same time as another card on the same network segment, they detect the collision, wait a short period, and retry. As long as you have few writers and many readers it works acceptably. __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/
Re: [sqlite] Re: [unclassified] Re: [sqlite] getting rid of dirty SQLITE_BUSY workaround
> I have not tried this with SQLite, but if I was using SQLite for such > an app, I assume I would need to serialize all such writes through a > single thread or process. E.g., in my web server, the connection > thread servicing the user request would send a message to my one > single db writer thread, saying, "Heh, please insert this data for > me!". Then the conn thread would simply block until the db writer > thread replies with, "Yup, your transaction is committed." Why not just do it the same way other distributed systems do it? As an example the ethernet card you're probably using right now. If a card tries to transmit at the same time as another card on the same network segment, they detect the collision, wait a short period, and retry. As long as you have few writers and many readers it works acceptably. __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/
Re: [sqlite] Re: [unclassified] Re: [sqlite] getting rid of dirty SQLITE_BUSY workaround
On Tue, Mar 15, 2005 at 12:21:15AM +0100, Thomas Lotterer wrote: > On Sun, Mar 13, 2005, jed wrote: > > > [...] web applications fit well into the model of "many readers, one > > writer", sqlite does this very well. > > > Well, there might be web applications which are read-only from the web's > view. But to be honest, most of them also call for occasional writes. > Think of a simple address book. Also I think of uses like tracking > session cookies which also use occasional writes. In all those cases I have not tried this with SQLite, but if I was using SQLite for such an app, I assume I would need to serialize all such writes through a single thread or process. E.g., in my web server, the connection thread servicing the user request would send a message to my one single db writer thread, saying, "Heh, please insert this data for me!". Then the conn thread would simply block until the db writer thread replies with, "Yup, your transaction is committed." That is a uglier than what you'd do with a real server-based RDBMS like Oracle or PostgreSQL, but it should scale fine until you have either: One, very large numbers of hits on your simple and efficient web app. Or two, a complicated web app with many potentially long running transactions, etc. In the real world, the second concern is much more likely to bite you than the first, and cries out for a more capable, more general purpose database than SQLite. It would be nice if SQLite had MVCC, which would let it scale much further up into the PostgreSQL-like realm normally dominated by client/server databases, but given the "simple, small, embedded" niche that Dr. Hipp intended for SQLite, it's easy to see why adding MVCC isn't any sort of priority, even if it could be done without making the code much more complicated. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/
Re: [sqlite] Re: [unclassified] Re: [sqlite] getting rid of dirty SQLITE_BUSY workaround
Keep It Simple, Keep It Small Foot Print. If the multi-access, multi writter can be implemented without compromising these two tenets, then great. Otherwise, let's use a different RDBMS. Regards, Uriel_Carrasquilla jed <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org cc: 03/13/2005 09:33 Subject: [sqlite] Re: [unclassified] Re: [sqlite] getting rid of dirty SQLITE_BUSY PM workaround Please respond to sqlite-users I like sqlite for it's simplicity in administration, small footprint, and reliability, this coupled with a very robust implementation of SQL. a lot embedded and web applications fit well into the model of "many readers, one writer", sqlite does this very well. Applications that need many concurrent tasks doing updates might think about using a server based RDBMS that has the added complexity and size for this purpose. All this said ... It might me nice to have an option where you can have sqlite "wait forever" might be nice to implement as a pragma. the downside is that ... well things might wait for a very long time and appear to hang. Just an idea, Jim Dodgen At 06:01 PM 3/13/2005, you wrote: >On Sun, 2005-03-13 at 16:49 -0500, D. Richard Hipp wrote: > > On Sun, 2005-03-13 at 21:56 +0100, Thomas Lotterer wrote: > > > > > I cannot believe it is normal behavior of a database application running > > > on a multitasking operating system to assume there will only be one > > > writer and otherwise let the application fail or do retries by itself. > > > > > > > I'll look into it. > > > >http://www.sqlite.org/cvstrac/chngview?cn=2385 > >-- >D. Richard Hipp <[EMAIL PROTECTED]>
