Does the MATLAB Interface to SQLite support concurrency?

Question

SQLite as a database supports varying degrees of concurrency depending on version and settings, therefore I was expecting the MATLAB Interface to SQLite in the Database Toolbox would support some level of concurrency. And when a database access fails, it should at the very least show errors.

However, when I used the following sniplet,

conn=sqlite("sqlite_concurr_test.db","create");
conn.close();
ppool=parpool(4);
ff=parallel.Future.empty();
disp("Write 500 numbers")
for ii=1:500
    ff(ii)=parfeval(ppool,@writeOne,0,ii);
end
for ii=1:500
    ff(ii).wait()
end
delete(ppool);
conn=sqlite("sqlite_concurr_test.db");
readback=conn.sqlread("test");
disp("Readback "+num2str(size(readback,1))+" numbers");

function writeOne(ii)
    conn=sqlite("sqlite_concurr_test.db");
    conn.sqlwrite("test",array2table(ii));
    conn.close();
end

I got the unexpected result of

Starting parallel pool (parpool) using the 'Processes' profile ...

Connected to the parallel pool (number of workers: 4).

Write 500 numbers

Parallel pool using the 'Processes' profile is shutting down.

Readback 74 numbers

This indicates that some database write did not occur, and there are no error reports. What can I do to change this behavior? Is there anything I should do to assure parallel access, or at the very least get notifications if anything goes wrong?

Answer 1

I expect that there is no direct solution using MATLAB built-ins for SQLite. It looks like there may be some options if you are comfortable writing your own SQL statements and then using execute your commit.

One possible workaround is sending the results to be written back to the client.

conn = sqlite('tst.db','create');
ps = parpool("LocalProfile1",3);
q = parallel.pool.DataQueue;
afterEach(q, @(T) sqlwrite(conn,"myTable",T));

parfor k = 1 : 10000
    T = array2table(rand(1,2));
    send(q,T)
end

Tall = sqlread(conn,"myTable");
assert(height(Tall) == 10000);