sqlite/test/concurrent5.test

# 2017 May 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.
#
#***********************************************************************
#
#

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

ifcapable !concurrent {
  finish_test
  return
}

db close
sqlite3_shutdown
test_sqlite3_log [list lappend ::log]
set ::log [list]

sqlite3 db test.db

proc do_test_conflict_msg {tn msg} {
  set msg "cannot commit CONCURRENT transaction - [string trim $msg]"
  uplevel [list do_test $tn {lindex $::log end} $msg]
}

do_execsql_test 1.0 {
  PRAGMA journal_mode = wal;
  CREATE TABLE t1(x, y);
  CREATE TABLE t2(c);
  WITH s(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<100)
  INSERT INTO t1 SELECT randomblob(400), randomblob(400) FROM s;
} {wal}

sqlite3 db2 test.db

do_test 1.1.1 {
  set ::log [list]
  db2 eval {
    BEGIN CONCURRENT;
      SELECT count(*) FROM t1;
      INSERT INTO t2 VALUES(10);
  }

  db eval {
    INSERT INTO t1 VALUES(randomblob(200), randomblob(200));
  }

  catchsql COMMIT db2
} {1 {database is locked}}
do_test_conflict_msg 1.1.2 {
  conflict at page 2 (read-only page; part of db table t1; content=0500000063021100...)
}

do_test 1.2.1 {
  set ::log [list]
  db2 eval {
    ROLLBACK;
    BEGIN CONCURRENT;
      INSERT INTO t1 VALUES(11, 12);
  }

  db eval {
    INSERT INTO t1 VALUES(12, 11);
  }

  catchsql COMMIT db2
} {1 {database is locked}}

do_test_conflict_msg 1.2.2 {
  conflict at page 105 (read/write page; part of db table t1; content=0D00000002026100...)
}

do_test 1.3.1 {
  set ::log [list]
  db2 eval {
    ROLLBACK;
    BEGIN CONCURRENT;
      INSERT INTO t2 VALUES('x');
  }

  db eval {
    INSERT INTO t2 VALUES('y');
  }

  catchsql COMMIT db2
} {1 {database is locked}}

do_test_conflict_msg 1.3.2 {
  conflict at page 3 (read/write page; part of db table t2; content=0D0000000103FB00...)
}

do_test 1.4.1 {
  set ::log [list]

  execsql {
    ROLLBACK;
    CREATE TABLE t3(a INTEGER PRIMARY KEY, b INTEGER);
    CREATE INDEX i3 ON t3(b);

    WITH s(i) AS (
      SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<5000
    ) INSERT INTO t3 SELECT i, i FROM s;

    BEGIN CONCURRENT;
      INSERT INTO t3 VALUES(0, 5001);
  } db2

  execsql { INSERT INTO t3 VALUES(NULL, 5002) } db
  catchsql COMMIT db2
} {1 {database is locked}}

do_test_conflict_msg 1.3.2 {
  conflict at page 211 (read/write page; part of db index t3.i3; content=0A0310006300D800...)
}

db2 close
reset_db
do_execsql_test 1.5.0 {
  PRAGMA auto_vacuum = 0;
  PRAGMA journal_mode = wal;
  CREATE TABLE t1(a, b);
  CREATE INDEX i1 ON t1(a, b);
  WITH s(i) AS (
    SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<1000
  ) 
  INSERT INTO t1 SELECT i, randomblob(200) FROM s;
} {wal}
do_test 1.5.1 {
  set ::log [list]

  execsql {
    BEGIN CONCURRENT;
      INSERT INTO t1 VALUES(100000, '');
  } db

  sqlite3 db2 test.db
  execsql { INSERT INTO t1(rowid, a, b) VALUES(-1, 100001, '') } db2
  catchsql COMMIT db
} {1 {database is locked}}

do_test_conflict_msg 1.5.2 {
  conflict at page 507 (read/write page; part of db index t1.i1; content=0A00000003025000...)
}


#-------------------------------------------------------------------------
reset_db
sqlite3 db2 test.db

set big1 [string repeat ab 10000]
set big2 "[string repeat ab  9999]xy"

do_execsql_test 1.6.0 {
  CREATE TABLE x1(x, y);
  INSERT INTO x1 VALUES(1, $big1);
  PRAGMA journal_mode = wal;
} {wal}

do_execsql_test -db db2 1.6.1.1 {
  BEGIN; 
    UPDATE x1 SET y=$big2;
} {}
do_execsql_test 1.6.1.2 {
  BEGIN CONCURRENT;
    UPDATE x1 SET y=$big2;
}
do_execsql_test -db db2 1.6.1.3 COMMIT
do_catchsql_test 1.6.1.4 {
  COMMIT;
} {1 {database is locked}}
do_test_conflict_msg 1.6.1.5 {
  conflict at page 21 (read/write page; part of db table x1; content=0000000061626162...)
}
catchsql ROLLBACK

do_test 1.6.2.1 {
  execsql { BEGIN } db2
  set fd [db2 incrblob main x1 y 1]
  seek $fd 19998
  puts -nonewline $fd 00
  close $fd
} {}
do_test 1.6.2.2 {
  execsql { BEGIN CONCURRENT } db
  set fd [db incrblob main x1 y 1]
  seek $fd 19998
  puts -nonewline $fd 12
  close $fd
} {}
do_execsql_test -db db2 1.6.2.3 COMMIT
do_catchsql_test 1.6.2.4 {
  COMMIT;
} {1 {database is locked}}
do_test_conflict_msg 1.6.1.5 {
  conflict at page 21 (read/write page; part of db table x1; content=0000000061626162...)
}
catchsql ROLLBACK

#--------------------------------------------------------------------------
reset_db
sqlite3 db2 test.db

set big1 [string repeat ab 10000]
set big2 "[string repeat ab  9999]xy"

do_execsql_test 1.7.0 {
  CREATE TABLE ww(a);
  CREATE TABLE y1(x, y);
  INSERT INTO y1 VALUES(1, $big1);
  PRAGMA journal_mode = wal;
} {wal}

do_execsql_test -db db2 1.7.1 {
  BEGIN;
    UPDATE y1 SET y=$big2;
    SELECT * FROM ww;
}

do_execsql_test 1.7.2 {
  BEGIN CONCURRENT;
    INSERT INTO ww SELECT y FROM y1;
}

do_execsql_test -db db2 1.7.3 COMMIT

do_catchsql_test 1.7.4 {
  COMMIT;
} {1 {database is locked}}

#-------------------------------------------------------------------------
reset_db
sqlite3 db2 test.db

set big1 "[string repeat ab 10000]"
set big2 "[string repeat ab  9999]xy"
do_execsql_test 2.0 {
  PRAGMA journal_mode = wal;
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT);
  CREATE TABLE t2(a INTEGER PRIMARY KEY, b TEXT);
  INSERT INTO t1 VALUES(100, $big1);
} {wal}

do_execsql_test -db db2 2.1 {
  BEGIN CONCURRENT;
    INSERT INTO t2 SELECT * FROM t1;
}

do_execsql_test 2.2 {
   UPDATE t1 SET b=$big2
}

do_test 2.3 {
  list [catch { db2 eval COMMIT } msg] $msg
} {1 {database is locked}}

do_test_conflict_msg 2.4 {
  conflict at page 22 (read-only page; part of db table t1; content=0000000061626162...)
}

db close
db2 close
sqlite3_shutdown
test_sqlite3_log 
sqlite3_initialize
finish_test