BTW, if you're running everything on a single machine there's lots of other ways you can do locking that don't involve the database.
On Wed, Mar 01, 2006 at 11:20:01AM -0800, w b wrote: > Unfortunately I think that this would lock the whole database within SQLITE > as there is no row level locking, so probably not the best way to go > forward, unless all of the other applications are only performing reads ? > > > Some othe ideas that might help. > > Have a field in one of your tables (May be a process table as Jim > descriobed) that stores the last update time when your main app performed a > refresh of the data. Your other applications could then infer that if that > value is greater than some threshold that the data within is old and should > not be trusted. So your other applications could infer from that that your > app has crashed. In this case you might not need to clean the DB as the data > is effectively implied as being bad given that the last_refresh time is > outside of your accepted aging window. This assumes that you are > periodically refreshing the data in there which sounds like that is the case > > On recovery (restart ) of your application I think the only thing you > probably dont want to do is go thru the recreation of the tables as that > would invalidate any prepares that your other applications have done. So may > be delete the old data and refresh it (or simply overwrite it). In doing so > your other applications would then see a new time stamp within the accepted > threshold range and so could now trust that data again. > > Wayne > > > "Jim C. Nasby" <[EMAIL PROTECTED]> wrote: On Wed, Mar 01, 2006 at 07:38:58PM > +0100, Elrond wrote: > > > > Hi, > > > > I'm considering to put the state of a running app into an > > sqlite db. I want it in a db, so external tools can query > > it and know, what the app is doing currently. > > > > Any hints on how to clean up the db, when the app crashes? > > > > (I have external resources, that I need to "lock", so the > > idea is to put the locks in the db, so more than one > > instance of the app can run and they don't kill the > > external resource.) > > > > Any hints? > > Depending on your needs, you might be able to just lock a row for > updates and hold that lock. IE, open a seperate connection to the > database and do: > > BEGIN; > UPDATE process SET start_time = now() WHERE process_id = ?; > > And then 'sit' on that connection until you're done. When you're > finished, just issue a COMMIT. Note that some databases won't like you > leaving that transaction open a real long time, so it depends on what > you're doing if this will work. I also don't know if SQLite cares about > such things. > -- > Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] > Pervasive Software http://pervasive.com work: 512-231-6117 > vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 > -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
