On Thu, 5 Sep 2013 19:53:15 +0100
Simon Slavin <slav...@bigfraud.org> wrote:
> On 5 Sep 2013, at 7:20pm, Peter Haworth <p...@lcsql.com> wrote:
>
> > That works fine but wondering if there might be a single UPDATE
> > statement that could do this for me. I can use the WHERE clause to
> > select sequence 3,4, and 5 but the UPDATE has to process the rows
> > in descending sequence order to avoid UNIQUE conflicts.
>
> An interesting point. If all the updates are done inside a COMMIT,
> then the conflict may not be recognised because by the time the first
> change is written back to the table, the conflicting entry has
> already been renumbered.
I was puzzled by this thread, so ran my own little test. To my dismay,
UPDATE is not atomic in SQLite.
$ rm -f db; sqlite3 -echo db < sql
create table t
( PKey INTEGER PRIMARY KEY
, Name TEXT
, Sequence INTEGER
, unique (Name, Sequence)
);
insert into t (Name, Sequence) values ('Blue', 1);
insert into t (Name, Sequence) values ('Blue', 2);
insert into t (Name, Sequence) values ('Blue', 3);
insert into t (Name, Sequence) values ('Blue', 4);
insert into t (Name, Sequence) values ('Blue', 5);
-- insert a new 3
BEGIN TRANSACTION ;
UPDATE t
SET Sequence = Sequence + 1
WHERE Sequence >= 3
AND Name = 'Blue';
SQL error near line 17: columns Name, Sequence are not unique
That's perfectly good SQL. SQLite is simply not executing the
update atomically.
Anyone tempted to protest may be forgetting "atomic" means more than
"all or nothing". It also means the DBMS may execute the transaction
however it sees fit, at the cost of ensuring that constraints remain in
effect upon commit but *only* upon commit. That's why this works:
sqlite> create table a (a int, b int check( a + b = 3));
sqlite> insert into a values (1, 2);
sqlite> select * from a;
a b
---------- ----------
1 2
sqlite> update a set a = b, b = a;
sqlite> select * from a;
a b
---------- ----------
2 1
Constraints hold for the whole database at all times whenever the user
can see the data. Any update is valid if it can logically be applied
and leave the database in a state consistent with its declared
constraints.
Here's how Marc L. Allen's query should work (different
DBMS):
$ bsqldb < /tmp/sql
PKey Name Sequence
----------- ------------------------------ -----------
1 Blue 1
2 Blue 2
3 Blue 4
4 Blue 5
5 Blue 6
6 Blue 3
6 rows affected
Note that the final insert is assigned the next auto-generated
PKey (6), and the old 3 is now 4, etc.
Granted, it's not easy. The update processing cannot simply find each
row in turn and update it (as appears to be the case now). One
alternative would be to make a copy of the whole set, update it, and
then apply it en masse. Another probably slower way would be to "keep
trying": if a row can't be updated, find one that can and continue
until they're all done or you're painted into a corner and
have to rollback. Doubtless there are better ways.
No one ever said a DBMS was a simple creature!
Simon mentioned
> http://www.sqlite.org/conflict.html
This has nothing to do with deferred constraint resolution. Deferred
constraint resolution involves more than one table being updated in a
transaction, deferring constraint enforcement until the
commit. See for example this bit of DB2 documentation,
http://pic.dhe.ibm.com/infocenter/idshelp/v115/index.jsp?topic=%2Fcom.ibm.sqls.doc%2Fids_sqs_0633.htm.
I also looked at http://www.sqlite.org/lang_conflict.html, which says,
"REPLACE
When a UNIQUE constraint violation occurs, the REPLACE algorithm
deletes pre-existing rows that are causing the constraint violation
prior to inserting or updating the current row and the command
continues executing normally."
The order in which the rows are (internally) processed is arbitrary,
and the results of using "or replace" here are predictably
unpredictable:
BEGIN TRANSACTION ;
UPDATE or replace t
SET Sequence = Sequence + 1
WHERE Sequence >= 3
AND Name = 'Blue';
insert into t (Name, Sequence) values ('Blue', 3);
COMMIT;
select * from t;
PKey Name Sequence
---------- ---------- ----------
1 Blue 1
2 Blue 2
3 Blue 4
5 Blue 6
6 Blue 3
but what's a Blue 5 among friends? :-/
As regards the documentation, the only fair thing to do for now would
be to add UPDATE to the omitted page, explaining the row-by-rowism.
I would also suggest flagging the UPDATE page itself, because the
behavior is very much at variance with the standard.
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
