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'); SELECT LAST_INSERT_ROWID(); 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'); SELECT LAST_INSERT_ROWID(); 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).
