On Wed, Aug 02, 2017 at 06:10:52PM +0000, Sylvain Pointeau wrote: > for a general case, I would need to persist the counter into a table (for a > specified sequencer) and doing the nextval inside a mutex lock > > Is it possible to insert/ select from a UDF if the statements are > serialized? or should I use the virtual tables? (should we store the > sequencers in a different DB?)
In SQLite3 all writes in transactions are serialized. No locks needed because there's just one big lock around the entire DB. I recommend you read up on SQLite3's transactions and locking. A next_serial() UDF would basically be a C-coded (or Perl, or whatever) function that uses the SQLite3 API to first run an UPDATE on the sequence then a SELECT to get the now-next value, and would return that. If you use the INSTEAD OF trigger approach, then the same applies, except that the triggers will be SQL-coded (which is nice, IMO). This is all perfectly safe in the current SQLite3 concurrency model (just one writer at a time). I don't think SQLite3's write concurrency will ever get better, but I suppose one never knows! If you were using an RDBMS with higher write concurrency then you'd need to be more careful and arrange for synchronization. Usually such RDBMSes provide builtin next_serial()-style functions anyways. As to your last question, I'd put the sequences table in the same DB, unless you need to attach multiple DBs and have all of them share sequences, in which case I'd make a DB just for the sequences, or else put them in the main DB. Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
