Are you sure it is not the special case documented in the ATTACH command? (see https://sqlite.org/lang_attach.html)
" Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:" and the journal_mode is not WAL. If the main database is ":memory:" or if the journal_mode is WAL, then transactions continue to be atomic within each individual database file. But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not." -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von sanhua.zh Gesendet: Mittwoch, 01. August 2018 09:57 An: sqlite-users <sqlite-users@mailinglists.sqlite.org>; drh <d...@sqlite.org> Betreff: [EXTERNAL] [sqlite] UNION ALL bug in Multi-threading I find a bug that exists in `UNION ALL`. tldr: `UNION ALL` will not merge the data in different schemas in the moment that one of schema is committed but the another not. BUT, `UNION` will. Here are the reproduce steps: Preparation: 1. Prepare a database named "OLD" 1.1 Create a table for "OLD": `CREATE TABLE old(i INTEGER)` 1.2 Insert values into "OLD" to make it large enough: `INSERT INTO old VALUES(?1)` 2. Prepare a database named "NEW" 2.1 Create a table for "NEW": `CREATE TABLE new(i INTEGER)` Migration: For thread 1: 1. Attach "OLD" to "NEW" as "oldSchema": `ATTACH OLD AS oldSchema` 2. Migrate data from "OLD" to "NEW" in same transaction. Note that they should be executed with same handle using ATTACH mentioned in 1. 2.1 `BEGIN IMMEDIATE` 2.1 Select one of the row from "OLD": `SELECT i FROM oldSchema.old` 2.2 Insert the row into "NEW": `INSERT INTO main.new VALUES(?1)` 2.3 Delete the row from "OLD": `DELETE FROM oldSchema.old WHERE i == ?1` 2.4 `COMMIT` For thread 2-N: 1. Create a view that union two tables: `CREATE TEMP VIEW v AS SELECT i FROM oldSchema.old UNION ALL SELECT i FROM main.new` 2. Select one of the value from view: `SELECT i FROM temp.v ORDER BY i LIMIT 1 OFFSET ?1`. Here is the strange result: As an example, if the values of 0-999 is inserted into "OLD", then value N should be selected as expected at offset N. But in these kind of steps, it will not. It can be a little bit hard to reproduce due to the multi-threading. BUT if it sleeps for a while when committing, it will be much easier to reproduce: // vdbeCommit method of vdbeaux.c for(i=0; rc==SQLITE_OK idb-nDb; i++){ Btree *pBt = db-aDb[i].pBt; sqlite3_sleep(10); // additional sleep here if( pBt ){ rc = sqlite3BtreeCommitPhaseOne(pBt, 0); } } It seems that the bug happens when one of the schema is committed but the another one is not. On the other handle, if `UNION ALL` is changed to `UNION` while creating view, the bug will not happen too. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
