On Sun, 20 Jan 2019 21:51:19 +0000
<andrew.g...@l3t.com> wrote:
> > insert into t
> > select :pid, nrows, N
> > from (select 1 as N union select 2 union select 3) as cardinals
> > cross join (select :pid, count(*) as nrows from t) as how_many;
> >
> > By using a single SQL statement, you avoid a user-defined
> > transaction and any proprietary transaction qualifiers.
>
> There's too much back-and-forth between the database and my logic to
> put it all into a single statement. Thus, transactions are
> necessary. Transactions exist to allow multiple statements to become
> an atomic unit, so eschewing them is basically the same thing as
> admitting they don't work.
I don't suggest you eschew user-defined transactions, and I don't admit
they don't work. I suggest not relying on proprietary features. BEGIN
IMMEDIATE may do what you want, but at the cost of not learning
how to accomplish the same thing using standard SQL.
In general, the formulation
select ...
do stuff
insert ...
will get you in trouble in most SQL setups, because selected data are
subject to change, whether or not the logic is in a user-defined
transaction. If the DBMS is set up with serialized isolation,
concurrency will be compromised because you're holding a transaction
open across application logic. That's especially true when it would be
most convenient: when "do stuff" involves user interaction, and said
user might choose that moment for a coffee break.
The most common solution is to check your assumptions at the door,
otherwise known as "optimistic concurrency". At time of insert,
make sure the previously obtained data still obtain, i.e., that nothing
has changed meanwhile. If it has, the precondition for the update has
been violated, and you start over. If it hasn't -- and, in practice,
usually it hasn't -- you're good to go.
In your case, you might be able to do something like
select count(*) as nrows from t
do stuff
begin transaction
insert into t select ... where nrows = (select count(*) from t)
verify rows affected == 1
insert into t select ... where nrows = (select count(*) from t)
verify rows affected == 1
insert into t select ... where nrows = (select count(*) from t)
verify rows affected == 1
commit
putting whatever the relevant test is in the WHERE clause. The test
can be simplified in SQLite to just the first insert, because with
SQLite's single-writer design, they're redundant once the transaction
has begun to take effect (ie, once other writers really are blocked).
User-defined transactions weren't invented to deal with
read-write-write errors. They were invented to enforce referential
integrity. SQL has no syntax to update several related tables at the
same time. If updates to different tables would leave the database in
an inconsistent state, some mechanism is needed to convert those
several SQL statements into a single all-or-nothing update. Thus were
born user-defined transactions.
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
