ms access 2010 - Delphi + ACCDB: Could not update; currently locked -

i have delphi app performs lengthy calculations on each record in data set , writes results record table (one of many) in access 2010 accdb. since can take hours run, speed things spawns additional process (or more one) handle half records while main process handles other half. processes , accdb local 1 machine, , i’m using ado components (tadoconnection/table/query). serialize access db, i'm using mutex. flow looks like:

procedure addrecord(asourcedata: tdataset; aresults: tresults); begin   mutex := createmutex(nil, false, ‘name’);   waitforsingleobject(mutex, infinite);   <find table, t, write to>   <search record in table>   <if found, t.edit; else t.append>   <update fields in table asourcedata , aresults>   t.post;  <== error   releasemutex(mutex); end; 

i figured mutex avoid locking issues, since 1 process allowed hit db @ time, sporadic “could not update; locked” errors. finishes without error, , error can occur in either of 2 process. i've tried including onposterror handler , allowing 10 retries. lets continue, other times still fails.

are there timing issues haven’t thought of? e.g., 1 process finishes post , releasemutex calls, other process acquires mutex , attempts update same table/record before accdb lock cleared.

any ideas welcome. tia.

first, make sure have locking type on table or recordset ltoptomistic. if aren't setting it, you're good, because default tcustomadodataset objects.

second, may want refactor code , use transactions. typically, done begintrans , committrans functions tadoconnection.

third, may want rethink whole operation. perhaps better construct 1 or more in-memory datasets , perform batch inserts , updates physical database less frequency , in serial fashion.

for example, create:

1. one dataset per table, or
2. one dataset inserts , updates, or
3. some other scheme.

so when reach arbitrarily-defined threshold in-memory datasets, push data in 1 bulk operation db.

without access lot more code, connection setup, , other environmental factors, cannot more specific.