I have just joined this mailing list, because searching the archives I have not found much related to locking. I am not completely new to SQLite, having previously used it when I wrote my latest version of a chat program, where I migrated the database over to from mysql.
In my first attempt to do this, I tried to completely manage a single users transaction (write a message, move between rooms etc) in the apache thread which handled the original page or subsequent ajax requests and thought, but was subsquently proven wrong that SQLlite would queue when there was contention for a lock. In the end I implemented a loop on BEGIN EXCLUSIVE which failed with a SQLITE_BUSY by sleeping for a random period of a few milliseconds and trying again. However once I got up to a few online test users (about 40 each speaking every 30 seconds or so) , interaction between the threads became too much. I ended up in almost continual busy mode backing off and retrying when the locks were taken. In the end I implemented a Server Process based approach to get round that (and it worked extremely well). I am about to try and port two more web/ajax php/javascript applications from postgres to sqlite - the rationale being that having the local database file accessible directly rather than some amorphous set behind a server will be easier to manage. Also both of these have the characteristic that there will be a lot less parallelism. However there will not be none, and I will still have to allow for the fact that users will be looking at the database in parallel. In fact both of the applications have the property that they will do quite a bit of querying of the database to build up the web pages they look at and that normally a single user will conduct the majority of the update transactions. One of these applications is a personal financial planning package where I expect normally a single user to update the data, although I don't want to rule out maybe a couple of people doing it. I already have application level checks to check the integrity of data related to parallel updates but I need to plan for potentially the database clashing when two people attempt an update transaction at the same time. I think the answers related to the more complex application discussed below will enable me to do this. The other is an (American) football results picking competition. In this the majority of the update transactions are the performed by a (single) administrator with the majority of the users just performing queries to check for matches and past results. Once a week (or maybe a two or three times if they decide the initial picks are wrong) each user in the competition will do a single transaction to update all their picks for the week. I want to design an optimal locking strategy and a series of simple middleware functions to handle the occassional requirements to back off and retry. However I am not sure from the documentation exactly when I might receive a SQL_BUSY failure in the cases where a) The administrator commits a change he has setup on his web page. I would expect to him (or rather the ajax request resulting from him hitting the SAVE button) to do a BEGIN TRANSACTION (not yet sure if its optimal to do an IMMEDIATE or EXCLUSIVE at this point). What happens to i) This request if there are users still completing queries to read the data. IE does it immediately fail SQLITE_BUSY or will it wait a short period whilst any current SELECT statements complete ii) Users who are performing a series of SELECT statements (with complex joins) and for which the Administrators BEGIN TRANSACTION occurs in the middle of the series. And as a follow on iii) Should I really be doing a BEGIN TRANSACTION before that series of SELECTS to hold off the administrator here and ensure the user gets the entire page displayed. When would the administrator see the SQLITE_BUSY if he did a BEGIN IMMEDIATE b) As the user submits his picks I would again do a BEGIN TRANSACTION (or may be a BEGIN EXLUSIVE or BEGIN IMMEDIATE and other users and the administrator are doing their things in parallel. I am aware of the staged locking process, what I am unclear about is that does failure to get the locks at ANY of these stages cause an SQLITE_BUSY to occur? I am actually not using C, but the php routines for this. I have my suspicions, but I can't find it explicity stated anywhere that the PHP::SQLITE3 module behaves exactly like the C routines but that using PHP::PDO abstraction layer handles the lock detection and retry itself (my only evidence is that the php manual for bool *PDO::setAttribute* ( int $attribute , mixed <http://uk.php.net/manual/en/language.pseudo-types.php#language.types.mixed> $value ) ... /PDO::ATTR_TIMEOUT/: Specifies the timeout duration in seconds. Not all drivers support this option, and it's meaning may differ from driver to driver. For example, sqlite will wait for up to this time value before giving up on obtaining an writable lock, but other drivers may interpret this as a connect or a read timeout interval. Requires int <http://uk.php.net/manual/en/language.types.integer.php> . which sort of implies that it does). I presume many people have experience of the sort of application pattern I am describing. What approach to managing locking do you take? -- Alan Chandler http://www.chandlerfamily.org.uk _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
