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 <[email protected]> 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 <[email protected]> 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
>> [email protected]
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
