if I do
insert into mytable(f)
select nextval("myseq") from T
is it really possible to make an update into that nextval function? I don't
think so since only one statement can be run at the same time if sqlite is
in serialized mode.
Le mer. 2 août 2017 à 20:25, Nico Williams <n...@cryptonector.com> a écrit :
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
