On 1 Dec 2012, at 3:22pm, dochsm <docshotma...@hotmail.com> wrote: > My database is shared by many users on a network. As part of auto-updating my > client applications via the web it might be necessary to alter some table > structures / view sql or put extra data into tables inside the shared sqlite > database. Problem is that the db might be in use at the time. > > Qn1) Is it safe simply to wrap up everthing I want to do in a transaction > and then commit the transaction?
As far as SQLite is concerned, yes. SQLite will lock the schema against changes while SELECT UPDATE INSERT commands are executing, and will lock the database against those commands while the database schema is being changed. The effect is that locking is used in the same way it would be if two users tried to do UPDATE commands at the same time. > Q2) If I back up the database first using the online backup to safely back > it up, how do I safely restore it again when the db might be in use at the > time? I assume you're talking about the online backup API: <http://www.sqlite.org/c3ref/backup_finish.html> Under those circumstances I think you would use the same backup API in the other direction to restore the database again. It will have a lock on that destination database the whole time it's working, of course. So if you have implemented a timeout correctly your users will just get a long pause or a "database busy" message during access. If you have been writing your code correctly you will have used transactions to group all related accesses /including related SELECTs/ together. So any particular piece of code in your app should notice only consistency. > I'm using DISQLite and the online api in delphi, not the command line thing. I have seen some bad drivers which assume that underlying schema will not be changed by another user while they have a database connection open. I have no reason to believe that this is one of them, but it might be worth reading the documentation. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
