On Sun 2015-12-13 13:47, Simon Slavin <slavins at bigfraud.org> wrote:
> On 13 Dec 2015, at 5:34pm, Clemens Ladisch <clemens at ladisch.de> wrote:
> > Olivier Mascia wrote:

> >> should the design of competing threads revolve around each one having
> >> a distinct connection handle?

> > Yes.

> Which, of course, decreases the point of you having competing threads in
> the first place.  Which is related to the FAQ pointing at

> <http://www.eecs.berkeley.edu/Pubs/TechRpts/2006/EECS-2006-1.pdf>

That fellow merely points out that coders who do not know what they are doing 
should not attempt to use multiple threads.  
Proper use of multiple threads of concurrent execution requires a programmer 
versed in concurrency.

Only co-operating threads can share a single connection betwixt themselves and 
then only for use for the purpose for which they are co-operating.  Competing 
threads will need separate connections per thread.

In other words, if execution in one thread can change the state seen by another 
thread "in undesirable ways" then you made a design error by classifying 
obviously "competing" uses of "shared state" improperly as "co-operating" uses 
of "shared state", in this case a database connection.

You can either (a) stop sharing state; (b) make the sharing of state moot by 
not depending on it.  In this case (b) would mean using a select to get the 
rowid for the target row and not using a function which accesses shared state.  
(a) could mean assigning a separate connection per thread or it could mean 
creating a mutex that serializes "insert and get last insert rowid" operations 
so that only one at a time may affect the "state" of the connection.  (assuming 
that your shared connection does not introduce any other anomalies for which 
you have failed to account).

In other words:

INSERT INTO table VALUES ('somedata');

presumes that no other thread is permitted to execute *ANY* operation affecting 
the last_insert_rowid between the two statements.  This can be guaranteed by 
not allowing other "things" to issue update/inserts on the connection:

block_waiting_for_mutex('INSERT AND GET ROWID')
INSERT INTO table VALUES ('somedata');
release_mutex('INSERT AND GET ROWID')

Alternatively,  if you use something like:

INSERT INTO table VALUES ('somedata');
SELECT rowid FROM table WHERE data='somedata';

will always work properly since it is not dependent on shared state (assuming 
that your database is properly normalized, or course).

Reply via email to