I am having similar concurrency issues and found that executing a "BEGIN IMMEDIATE" will only grab a reserved lock. This allows other threads to read the database and the writer won't grab the exclusive lock until it commits. This is helpful if the writer does a fair amount of work inside the transaction before committing and your readers are getting starved.
I also found a case where if there are say 10 threads all attempting to get a lock on the database then it's possible for some to be starved even though no single thread holds the lock for a long time. I had to implement a fifo queue on my side to make sure that the writers obtained the lock in the order requested. Otherwise I saw cases where one thread kept getting the lock because it would finish its work and request the lock again before the other waiting threads completed their busy timeout sleep. Mike Borland -----Original Message----- From: Thomas Briggs [mailto:t...@briggs.cx] Sent: Thursday, January 29, 2009 9:57 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] PL/SQL in Sqlite? If you really are only updating 20 records at a time you should be able to make it work plenty fast enough with plain old SQL. Something feels wrong about using an exclusive transaction here too. I can't say why, and I may well be wrong, but... just a gut hunch. On Thu, Jan 29, 2009 at 12:47 PM, Daniel Watrous <dwmaill...@gmail.com> wrote: > Hello, > > I'm wondering if there is anything like PL/SQL that is built into > SQLite? I have an application that requires me to use EXCLUSIVE > transactions as follows: > > BEGIN EXCLUSIVE > SELECT from table limit 20 > for each > UPDATE record assignment > COMMIT > > The requests for assignments are coming in concurrently from many > clients and I'm finding that SQLite isn't keeping up. I know that > SQLite isn't intended for highly concurrent environments that make > many writes to the database, but I'd like to make it work if > possible... > > So, is there a way to push this processing over to SQLite (e.g. PL/SQL > style) that would speed it up? > > Or, is there some way you can think of to make assignments of a subset > of records to concurrent clients that would be more efficient and help > me avoid the large number of timeouts I'm getting right now? > > Thanks, > Daniel > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
