Thanks for the reply Thomas. This script runs on a machine that has a lot of work to send out to compute hosts. There are over 100 compute hosts and each has a number of processes requesting work. That means there may be as many as 30-40 hosts asking for work. To increase parallelization I send 20 units of work to each host.
I need an exclusive transaction to avoid double assignments. In other words, from the first select I need to make sure that those same units of work don't come up in another request until they've been updated as belonging to a given host. This amount of concurrency is a bit too much for SQLite. A colleague sent me the following query that I think will work for what I'm trying to accomplish. Here it is: update table set hostAssignment = "somehost", timeHostAssignment = 123456799 where id in ( select id from work_to_be_done where hostAssignment is NULL and timeHostAssignment is NULL LIMIT 20) Daniel On Thu, Jan 29, 2009 at 10:57 AM, Thomas Briggs <t...@briggs.cx> wrote: > 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