By the sounds of it, you have multiple applications trying to access the database. One is the reader of the PLCs and writes to the database, the other is the management app that does the database clean up, or at least, this sounds like a multi-threaded app.
If the code you use to read the PLCs is long lived, then what I would do is try writing to a :memory: database whatever results come back from the PLCs, then try to write to the real database. If the database is locked, then, ignore the error for now, since your information is in memory. (Maybe keep a counter so that if you get more than 60 errors, the memory database gets dumped out somewhere else and then fails with proper errors). If the write was successful, then delete the contents of the :memory: database. A simple "insert into RealTable from MemoryTable" or whatever the case may be, within a transaction, should keep your database up to date, even with things such as timestamps. If your applications are short-lived, as in say something you'd get CRON, or Scheduled Tasks to run, then I'd say, again, write out to somewhere temporary (/tmp/PLCTempData or c:\temp\PLCTempData) every read event. Then have another app that comes by and writes the data from /tmp/PLCTempData directly into your database. Upon success, delete the temp file. Upon failure, leave it for the next attempt. On Fri, Jul 28, 2017 at 9:01 AM, Rob Richardson <[email protected]> wrote: > I have a program reads data from 17 PLCs and writes it into SQLite > databases every minutes. Every midnight, it deletes old data and vacuums > the databases. It's behaving strangely after that. I think the problem > begins because the vacuum operation is still going on at 12:01, when the > next read is scheduled. SQlite throws a "database is locked" error. > > I tried to replicate this by writing a little program that vacuums one > SQLite database repeatedly for three minutes. The first time I tried to > use it, the main program behaved as expected, showing the same odd > behavior. But the next two times I tried, my program sailed right through > the "database is locked" error, running as it was designed with no problems. > > So, instead of running vacuum over and over again, I would like to do > something that would lock my database against writing once, and then not > release it for three minutes. How can I do that? > > Thank you very much. > > RobR > _______________________________________________ > sqlite-users mailing list > [email protected] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
