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. Shawn > Hi Shawn, > > > demonstrate SQLite best > > practices, including how to correctly handle error conditions, such as > > 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 > > (https://www.metatranz.com/stepsqlite <https://www.metatranz.com/stepsqlite> ). > > 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
