Thank you all for your responses. This discussion has grown a lot more
than I thought it would.
Like I said in my original question, my system is made up of several
programs that communicate by sending messages to each other. I have a
utility program that can send messages to the programs for testing and
debugging purposes. I have updated my utility program and the program
that accesses the database so that I can send arbitrary SQL queries or
update statements to the database program to execute. Now I don't need
to use the command line SQLite tool and the locked database problem is
no longer an issue.
The discussion of transactions and interleaving queries with updates was
really good information.
I am (mis)using transactions not so much to group a unit of work that
must succeed together, but more to improve the I/O performance by
causing any updates to be written to disk at the end of the transaction.
Writing each change individually to disk was taking too much time and
wrapping things in a transaction reduced that time considerably.
I am not using BEGIN IMMEDIATE, just BEGIN, but I don't think it is a
problem in my case, since now there really is just one program accessing
the database.
I am probably interleaving queries and updates in at least one part of
my program, but it doesn't seem to be causing any problems. I'll let you
know if I get the 1:00 am hotline support call.
Swapnil, thanks for the link. I'm running on SCO OpenServer so I'm not
sure your product would work for me but I'll take a look. I was really
looking for code examples that demonstrate best practices.

Thanks again for all the comments, suggestions and good information.


> Hi Shawn,
> > demonstrate SQLite best
> > practices, including how to correctly handle error conditions, such
> > the database is locked condition?
> > If you pre-select and then modify, you have to be aware enough to
> > realize you MUST wrap the whole process in a manual transaction,
> > and you still need to know how to deal with all the locking and
> > busy issues that come with that.
> One way to assure use of best-practices in dealing with SQLite
> nitty-gritties of transactions, locking and busy errors would be to
> use a tool like StepSqlite PL/SQL compiler
> (
<> ).
> It automates SQLite best-practices (as recommended by Jay, Pavel and
> Simon in this thread) by wrapping things in transactions, handling
> BUSY errors and retrying after delay and others like preparing all SQL
> statements only once etc.
> SQLite combined with a 4GL like StepSqlite PL/SQL lets you focus on
> writing your code instead of having to learn to deal with SQLite
> idiosyncrasies right at the beginning.
> Full disclosure: I am the creator of StepSqlite.
> -Swapnil Kashikar
> support @ metatranz . com
sqlite-users mailing list

Reply via email to