/ Artifact Content
Login
Home Timeline Branches Tags Tickets Wiki More...
Check-ins Using Download Hex
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Artifact 9dfbeb0a323733fe1d13443371734bb94a674dbf777f464365475903873111f8:

# 2015 July 26
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# Miscellaneous tests for transactions started with BEGIN CONCURRENT. 
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
source $testdir/lock_common.tcl
source $testdir/wal_common.tcl
set ::testprefix concurrent2

ifcapable !concurrent {
  finish_test
  return
}

do_multiclient_test tn {

  do_test 1.$tn.1 {
    sql1 {
      PRAGMA journal_mode = wal;
      CREATE TABLE t1(x);
      CREATE TABLE t2(y);
    }
  } {wal}
  do_test 1.$tn.5 { sql3 { PRAGMA integrity_check } } {ok}

  # Test that an CONCURRENT transaction that allocates/frees no pages does
  # not conflict with a transaction that does allocate pages.
  do_test 1.$tn.2  {
    sql1 { 
      BEGIN CONCURRENT;
        INSERT INTO t1 VALUES(4);
    }
    sql2 {
      INSERT INTO t2 VALUES(randomblob(1500));
    }
    sql1 {
      COMMIT;
    }
  } {}
  do_test 1.$tn.5 { sql3 { PRAGMA integrity_check } } {ok}
  
  # But that an CONCURRENT transaction does conflict with a transaction
  # that modifies the db schema.
  do_test 1.$tn.3  {
    sql1 {
      BEGIN CONCURRENT;
        INSERT INTO t1 VALUES(5);
    }
    sql2 {
      CREATE TABLE t3(z);
    }
    list [catch { sql1 COMMIT } msg] $msg
  } {1 {database is locked}}
  do_test 1.$tn.5 { sql3 { PRAGMA integrity_check } } {ok}
  
  # Test that an CONCURRENT transaction that allocates at least one page 
  # does not conflict with a transaction that allocates no pages.
  do_test 1.$tn.4  {
    sql1 { 
      ROLLBACK;
      BEGIN CONCURRENT;
        INSERT INTO t1 VALUES(randomblob(1500));
    }
    sql2 {
      INSERT INTO t2 VALUES(8);
    }
    sql1 {
      COMMIT;
    }
  } {}

  do_test 1.$tn.5 { sql3 { PRAGMA integrity_check } } {ok}
}

do_multiclient_test tn {
  do_test 2.$tn.1 {
    sql1 {
      PRAGMA journal_mode = wal;
      CREATE TABLE t1(x UNIQUE);
      CREATE TABLE t2(y UNIQUE);
    }
  } {wal}

  do_test 2.$tn.2  {
    sql1 { 
      BEGIN CONCURRENT;
        INSERT INTO t1 VALUES(randomblob(1500));
    }
    sql2 {
      INSERT INTO t2 VALUES(randomblob(1500));
    }
    sql1 COMMIT
  } {}

  do_test 2.$tn.3 { sql3 { PRAGMA integrity_check } } {ok}

  do_test 2.$tn.4  {
    sql1 { 
      BEGIN CONCURRENT;
        DELETE FROM t1;
    }
    sql2 {
      DELETE FROM t2;
    }
    sql1 COMMIT
  } {}

  do_test 2.$tn.5 { sql3 { PRAGMA integrity_check } } {ok}

  do_test 2.$tn.6 {
    sql1 {
      INSERT INTO t1 VALUES(randomblob(1500));
      INSERT INTO t1 VALUES(randomblob(1500));
      INSERT INTO t2 VALUES(randomblob(1500));
      DELETE FROM t1 WHERE rowid=1;
    }

    sql1 {
      BEGIN CONCURRENT;
        DELETE FROM t1 WHERE rowid=2;
    }

    sql2 {
      DELETE FROM t2;
    }

    sql1 COMMIT
  } {}

  do_test 2.$tn.7 { sql3 { PRAGMA integrity_check } } {ok}
}

#-------------------------------------------------------------------------
# When an CONCURRENT transaction is opened on a database, the nFree and 
# iTrunk header fields of the cached version of page 1 are both set 
# to 0. This allows an CONCURRENT transaction to use its own private 
# free-page-list, which is merged with the main database free-list when
# the transaction is committed.
#
# The following tests check that nFree/iTrunk are correctly restored if
# an CONCURRENT transaction is rolled back, and that savepoint rollbacks
# that occur within CONCURRENT transactions do not incorrectly restore
# these fields to their on-disk values.
#
reset_db
do_execsql_test 3.0 {
  PRAGMA journal_mode = wal;
  CREATE TABLE t1(x, y);
  INSERT INTO t1 VALUES(randomblob(1500), randomblob(1500));
  DELETE FROM t1;
} {wal}

do_execsql_test 3.1 {
  BEGIN CONCURRENT;
    INSERT INTO t1 VALUES(1, 2);
  ROLLBACK;
}

do_execsql_test 3.2 { PRAGMA integrity_check } {ok}
do_execsql_test 3.3 { PRAGMA freelist_count } {2}

do_execsql_test 3.4.1 {
  BEGIN CONCURRENT;
    PRAGMA freelist_count;
} {2}
do_execsql_test 3.4.2 {
  SAVEPOINT xyz;
    INSERT INTO t1 VALUES(randomblob(1500), NULL);
    PRAGMA freelist_count;
} {0}
do_execsql_test 3.4.3 {
  ROLLBACK TO xyz;
} {}
do_execsql_test 3.4.4 { PRAGMA freelist_count } {0}
do_execsql_test 3.4.5 { COMMIT; PRAGMA freelist_count } {2}
do_execsql_test 3.4.6 { PRAGMA integrity_check } {ok}

do_execsql_test 3.5.1 {
  BEGIN CONCURRENT;
    UPDATE t1 SET x=randomblob(10) WHERE y=555;
    PRAGMA freelist_count;
} {0}
do_execsql_test 3.5.2 {
  ROLLBACK;
  PRAGMA freelist_count;
} {2}
do_execsql_test 3.5.3 { PRAGMA integrity_check } {ok}

#-------------------------------------------------------------------------
# Test that nothing goes wrong if an CONCURRENT transaction allocates a
# page at the end of the file, frees it within the same transaction, and
# then has to move the same page to avoid a conflict on COMMIT.
#
do_multiclient_test tn {
  do_test 4.$tn.1 {
    sql1 {
      PRAGMA journal_mode = wal;
      CREATE TABLE t1(x);
      CREATE TABLE t2(x);
    }
  } {wal}

  do_test 4.$tn.2 {
    sql1 {
      BEGIN CONCURRENT;
        INSERT INTO t1 VALUES(randomblob(1500));
        INSERT INTO t1 VALUES(randomblob(1500));
        DELETE FROM t1 WHERE rowid = 1;
    }

    sql2 {
      INSERT INTO t2 VALUES(randomblob(1500));
      INSERT INTO t2 VALUES(randomblob(1500));
      INSERT INTO t2 VALUES(randomblob(1500));
      INSERT INTO t2 VALUES(randomblob(1500));
      DELETE FROM t2 WHERE rowid IN (1, 2);
    }

    sql1 COMMIT
  } {}
}

#-------------------------------------------------------------------------
#
do_multiclient_test tn {
  do_test 5.$tn.1 {
    sql1 {
      PRAGMA journal_mode = wal;
      CREATE TABLE t1(x);
      CREATE TABLE t2(x);
      INSERT INTO t1 VALUES(randomblob(1500));
      PRAGMA page_count;
    }
  } {wal 4}

  do_test 5.$tn.2 {
    sql1 {
      BEGIN CONCURRENT;
        INSERT INTO t2 VALUES(randomblob(1500));
        PRAGMA page_count;
    }
  } {5}

  do_test 5.$tn.3 {
    sql2 { 
      DELETE FROM t1;
      PRAGMA freelist_count;
      PRAGMA page_count;
    }
  } {1 4}

  do_test 5.$tn.4 { sql1 COMMIT } {}
  do_test 5.$tn.5 { sql3 { PRAGMA integrity_check } } {ok}
}

#-------------------------------------------------------------------------
#
do_multiclient_test tn {
  do_test 6.$tn.1 {
    sql1 {
      PRAGMA journal_mode = wal;
      CREATE TABLE t1(x);
      INSERT INTO t1 VALUES(randomblob(1500));
      PRAGMA wal_checkpoint;
    }
  } {wal 0 5 5}

  do_test 6.$tn.2 {
    sql1 { 
      BEGIN CONCURRENT;
        INSERT INTO t1 VALUES(randomblob(1500));
        INSERT INTO t1 VALUES(randomblob(1500));
    }
  } {}

  do_test 6.$tn.3 {
    sql2 {
      BEGIN;
        INSERT INTO t1 VALUES(randomblob(1500));
        INSERT INTO t1 VALUES(randomblob(1500));
      COMMIT;
    }
  } {}

  do_test 6.$tn.4 { 
    list [catch { sql1 COMMIT } msg] $msg
  } {1 {database is locked}}
  do_test 6.$tn.5 { sql3 { PRAGMA integrity_check } } {ok}
  do_test 6.$tn.5 { sql3 { SELECT count(*) from t1 } } {3}
}

#-------------------------------------------------------------------------
# Test that if a corrupt wal-index-header is encountered when attempting
# to commit a CONCURRENT transaction, the transaction is not committed
# (or rolled back) and that SQLITE_BUSY_SNAPSHOT is returned to the user.
#
catch { db close }
forcedelete test.db
testvfs tvfs
sqlite3 db test.db -vfs tvfs
do_execsql_test 7.1 {
  PRAGMA journal_mode = wal;
  BEGIN;
    CREATE TABLE t1(a, b, PRIMARY KEY(a));
    INSERT INTO t1 VALUES(1, 2);
    INSERT INTO t1 VALUES(3, 4);
  COMMIT;
  BEGIN CONCURRENT;
    INSERT INTO t1 VALUES(5, 6);
    INSERT INTO t1 VALUES(7, 8);
    SELECT * FROM t1;
} {wal 1 2 3 4 5 6 7 8}

# Corrupt the wal-index header
incr_tvfs_hdr test.db 11 1

do_catchsql_test 7.2.1 { COMMIT } {1 {database is locked}}
do_test 7.2.2 { sqlite3_extended_errcode db } SQLITE_BUSY_SNAPSHOT

do_execsql_test 7.3.1 {
  SELECT * FROM t1;
  ROLLBACK;
} {1 2 3 4 5 6 7 8}
do_execsql_test 7.3.2 {
  SELECT * FROM t1;
} {1 2 3 4}

#-------------------------------------------------------------------------
# Test that "PRAGMA integrity_check" works within a concurrent 
# transaction. Within a concurrent transaction, "PRAGMA integrity_check"
# is unable to detect unused database pages, but can detect other types
# of corruption.
#
reset_db
do_execsql_test 8.1 {
  PRAGMA journal_mode = wal;
  CREATE TABLE kv(k INTEGER PRIMARY KEY, v UNIQUE);
  INSERT INTO kv VALUES(NULL, randomblob(750));
  INSERT INTO kv SELECT NULL, randomblob(750) FROM kv;
  INSERT INTO kv SELECT NULL, randomblob(750) FROM kv;
  INSERT INTO kv SELECT NULL, randomblob(750) FROM kv;
  INSERT INTO kv SELECT NULL, randomblob(750) FROM kv;
  INSERT INTO kv SELECT NULL, randomblob(750) FROM kv;
  DELETE FROM kv WHERE rowid%2;
  PRAGMA freelist_count;
} {wal 34}
do_execsql_test 8.2 { PRAGMA integrity_check } ok
do_execsql_test 8.3 { 
  BEGIN CONCURRENT;
    PRAGMA integrity_check;
} {ok}
do_execsql_test 8.4 { 
    INSERT INTO kv VALUES(1100, 1100);
    PRAGMA integrity_check;
} {ok}
do_execsql_test 8.5 { 
  COMMIT;
  PRAGMA integrity_check;
} {ok}

#-------------------------------------------------------------------------
# Test that concurrent transactions do not allow foreign-key constraints
# to be bypassed.
#
do_multiclient_test tn {
  do_test 9.$tn.1 {
    sql1 {
      PRAGMA journal_mode = wal;
      CREATE TABLE pp(i INTEGER PRIMARY KEY, j);
      CREATE TABLE cc(a, b REFERENCES pp);

      WITH seq(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM seq WHERE i<100)
      INSERT INTO pp SELECT i, randomblob(1000) FROM seq;

      PRAGMA foreign_keys = 1;
    }
  } {wal}


  do_test 9.$tn.2.1 {
    sql1 {
      BEGIN CONCURRENT;
        INSERT INTO cc VALUES(42, 42);
    }
  } {}
  do_test 9.$tn.2.2 {
    sql2 { DELETE FROM pp WHERE i=42 }
    list [catch { sql1 COMMIT } msg] $msg
  } {1 {database is locked}}
  do_test 9.$tn.2.3 {
    sql1 ROLLBACK
  } {}

  do_test 9.$tn.3.1 {
    sql1 {
      PRAGMA foreign_keys = 0;
      BEGIN CONCURRENT;
        INSERT INTO cc VALUES(43, 43);
    }
  } {}
  do_test 9.$tn.3.2 {
    sql2 { DELETE FROM pp WHERE i=43 }
    list [catch { sql1 COMMIT } msg] $msg
  } {0 {}}

  do_test 9.$tn.4.1 {
    sql1 {
      PRAGMA foreign_keys = on;
      BEGIN CONCURRENT;
        INSERT INTO cc VALUES(44, 44);
    }
  } {}
  do_test 9.$tn.4.2 {
    sql2 { DELETE FROM pp WHERE i=1 }
    list [catch { sql1 COMMIT } msg] $msg
  } {0 {}}
}

#-------------------------------------------------------------------------
# Test that even if a SELECT statement appears before all writes within
# a CONCURRENT transaction, the pages it reads are still considered when
# considering whether or not the transaction may be committed.
#
do_multiclient_test tn {
  do_test 10.$tn.1.1 {
    sql1 {
      PRAGMA journal_mode = wal;
      CREATE TABLE t1(a);
      CREATE TABLE t2(b);
      CREATE TABLE t3(c);
      INSERT INTO t1 VALUES(1), (2), (3);
      INSERT INTO t2 VALUES(1), (2), (3);
      INSERT INTO t3 VALUES(1), (2), (3);
    }
  } {wal}

  do_test 10.$tn.1.2 {
    sql1 {
      BEGIN CONCURRENT;
        SELECT * FROM t1;
        INSERT INTO t2 VALUES(4);
    }
  } {1 2 3}

  do_test 10.$tn.1.3 {
    sql2 { INSERT INTO t1 VALUES(4) }
    list [catch {sql1 COMMIT} msg] $msg
  } {1 {database is locked}}
  sql1 ROLLBACK

  # In this case, because the "SELECT * FROM t1" is first stepped before
  # the "BEGIN CONCURRENT", the pages it reads are not recorded by the
  # pager object. And so the transaction can be committed. Technically
  # this behaviour (the effect of an ongoing SELECT on a BEGIN CONCURRENT
  # transacation) is undefined.
  #
  do_test 10.$tn.2.1 {
    code1 {
      set ::stmt [sqlite3_prepare db "SELECT * FROM t1" -1 dummy]
      sqlite3_step $::stmt
    }
  } {SQLITE_ROW}
  do_test 10.$tn.2.2 {
    sql1 {
      BEGIN CONCURRENT; 
        INSERT INTO t2 VALUES(4);
    }
    code1 {
      set res [list]
      lappend res [sqlite3_column_int $::stmt 0]
      while {[sqlite3_step $::stmt]=="SQLITE_ROW"} {
        lappend res [sqlite3_column_int $::stmt 0]
      }
      sqlite3_finalize $::stmt
      set res
    }
  } {1 2 3 4}
  do_test 10.$tn.2.3 {
    sql2 { INSERT INTO t1 VALUES(5) }
    sql1 COMMIT
  } {}

  # More tests surrounding long-lived prepared statements and concurrent
  # transactions.
  do_test 10.$tn.3.1 {
    sql1 {
      BEGIN CONCURRENT;
        SELECT * FROM t1;
      COMMIT;
    }
    sql1 {
      BEGIN CONCURRENT;
        INSERT INTO t2 VALUES(5);
    }
    sql2 {
      INSERT INTO t1 VALUES(5);
    }
    sql1 COMMIT
    sql3 {
      SELECT * FROM t2;
    }
  } {1 2 3 4 5}
  do_test 10.$tn.3.2 {
    sql1 {
      BEGIN CONCURRENT;
        SELECT * FROM t1;
      ROLLBACK;
    }
    sql1 {
      BEGIN CONCURRENT;
        INSERT INTO t2 VALUES(6);
    }
    sql2 {
      INSERT INTO t1 VALUES(6);
    }
    sql1 COMMIT
    sql3 { SELECT * FROM t2 }
  } {1 2 3 4 5 6}
  do_test 10.$tn.3.3 {
    sql1 { BEGIN CONCURRENT }
    code1 {
      set ::stmt [sqlite3_prepare db "SELECT * FROM t1" -1 dummy]
      sqlite3_step $::stmt
    }
    sql1 {
      INSERT INTO t2 VALUES(7);
      SELECT * FROM t3;
      ROLLBACK;
      BEGIN CONCURRENT;
    }
    sql2 { INSERT INTO t3 VALUES(5) }
    code1 { sqlite3_finalize $::stmt }
    sql1 {
      INSERT INTO t2 VALUES(8);
      COMMIT;
    }
  } {}
}

do_multiclient_test tn {
  do_test 11.$tn.1 {
    sql1 {
      PRAGMA journal_mode = wal;
      CREATE TABLE t1(a);
    }
  } {wal}

  do_test 11.$tn.2 {
    code1 { sqlite3_wal_info db main }
  } {0 2}

  do_test 11.$tn.3 {
    sql1 { INSERT INTO t1 VALUES(1) }
    code1 { sqlite3_wal_info db main }
  } {2 3}

  do_test 11.$tn.4 {
    sql2 { INSERT INTO t1 VALUES(2) }
    code2 { sqlite3_wal_info db2 main }
  } {3 4}

  do_test 11.$tn.5 {
    sql1 { PRAGMA wal_checkpoint }
    sql2 { INSERT INTO t1 VALUES(3) }
    code2 { sqlite3_wal_info db2 main }
  } {0 1}
}

reset_db
do_execsql_test 12.0 {
  PRAGMA journal_mode = wal;
  CREATE TABLE tx(a INTEGER PRIMARY KEY, b);
} {wal}
do_test 12.1 {
  for {set i 0} {$i < 50} {incr i} {
    execsql {
      BEGIN CONCURRENT;
      INSERT INTO tx(b) VALUES( randomblob( 1200 ) );
      COMMIT;
    }
  }
  execsql { PRAGMA page_size }
} {1024}
do_execsql_test 12.2 {
  DELETE FROM tx;
}
do_test 12.3 {
  for {set i 0} {$i < 50} {incr i} {
    execsql {
      BEGIN CONCURRENT;
      INSERT INTO tx(b) VALUES( randomblob( 1200 ) );
      COMMIT;
    }
  }
  execsql { PRAGMA page_size }
} {1024}
do_execsql_test 12.4 {
  DELETE FROM tx;
}
do_test 12.5 {
  execsql { BEGIN CONCURRENT }
  for {set i 0} {$i < 5000} {incr i} {
    execsql {
      INSERT INTO tx(b) VALUES( randomblob( 1200 ) );
    }
  }
  execsql { COMMIT }
  execsql { PRAGMA page_size }
} {1024}


finish_test
This page was generated in about 0.004s by Fossil 2.10 [9d9ef82234] 2019-10-04 21:41:13