SQLite

Check-in [779b1d0e17]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add miscellaneous test cases for concurrent transactions.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | begin-concurrent
Files: files | file ages | folders
SHA1: 779b1d0e17bc54062b2b09cdbf94e9e2f4bae4f7
User & Date: dan 2015-08-25 19:10:29.114
Context
2015-08-26
18:02
Fix a problem allowing some conflicting transactions to be committed. (check-in: a0566382d5 user: dan tags: begin-concurrent)
2015-08-25
19:10
Add miscellaneous test cases for concurrent transactions. (check-in: 779b1d0e17 user: dan tags: begin-concurrent)
17:16
If "PRAGMA integrity_check" is run while the database is being written by a CONCURRENT transaction, do not consider unreferenced pages to be an error. They may be part of the free-page list, which is not visible at the b-tree layer when running a CONCURRENT transaction. (check-in: f32b57b493 user: dan tags: begin-concurrent)
Changes
Unified Diff Ignore Whitespace Patch
Changes to test/concurrent.test.
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
    list [catch { sql1 { COMMIT } } msg] $msg [sqlite3_errcode db]
  } {0 {} SQLITE_OK}

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

#-------------------------------------------------------------------------
# Unlocked transactions may not modify the user_version or application_id.
#
reset_db
do_execsql_test 3.0 {
  PRAGMA journal_mode = wal;
  CREATE TABLE t1(x, y);
  INSERT INTO t1 VALUES('a', 'b');
  PRAGMA user_version = 10;







|







413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
    list [catch { sql1 { COMMIT } } msg] $msg [sqlite3_errcode db]
  } {0 {} SQLITE_OK}

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

#-------------------------------------------------------------------------
# Concurrent transactions may not modify the user_version or application_id.
#
reset_db
do_execsql_test 3.0 {
  PRAGMA journal_mode = wal;
  CREATE TABLE t1(x, y);
  INSERT INTO t1 VALUES('a', 'b');
  PRAGMA user_version = 10;
444
445
446
447
448
449
450





451



















































452
453
do_execsql_test 3.5 {
  COMMIT;
  PRAGMA user_version;
  PRAGMA application_id;
  SELECT * FROM t1;
} {10 0 a b c d}


























































finish_test








>
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
do_execsql_test 3.5 {
  COMMIT;
  PRAGMA user_version;
  PRAGMA application_id;
  SELECT * FROM t1;
} {10 0 a b c d}

#-------------------------------------------------------------------------
# However, another transaction modifying the user_version or application_id
# should not cause a conflict. And committing a concurrent transaction does not
# clobber the modification - even if the concurrent transaction allocates or
# frees database pages.
#
do_multiclient_test tn {
  do_test 4.$tn.1 {
    sql1 {
      PRAGMA journal_mode = wal;
      CREATE TABLE ttt(y UNIQUE, z UNIQUE);
      PRAGMA user_version = 14;
      BEGIN CONCURRENT;
        INSERT INTO ttt VALUES('y', 'z');
    }
  } {wal}
  do_test 4.$tn.2 {
    sql2 { PRAGMA user_version = 16 }
    sql1 COMMIT
    sql1 { PRAGMA user_version }
  } {16}

  do_test 4.$tn.3 {
    sql1 {
      BEGIN CONCURRENT;
        INSERT INTO ttt VALUES(randomblob(10000), randomblob(4));
        PRAGMA user_version;
    }
  } {16}
  do_test 4.$tn.4 {
    sql2 { PRAGMA user_version = 1234 }
    sql1 {
        PRAGMA user_version;
      COMMIT;
      PRAGMA user_version;
      PRAGMA integrity_check;
    }
  } {16 1234 ok}

  do_test 4.$tn.5 {
    sql1 {
      BEGIN CONCURRENT;
        DELETE FROM ttt;
        PRAGMA user_version;
    }
  } {1234}
  do_test 4.$tn.4 {
    sql2 { PRAGMA user_version = 5678 }
    sql1 {
        PRAGMA user_version;
      COMMIT;
      PRAGMA user_version;
      PRAGMA integrity_check;
    }
  } {1234 5678 ok}
}

finish_test

Changes to test/concurrent2.test.
364
365
366
367
368
369
370



























































371
372
373
    INSERT INTO kv VALUES(1100, 1100);
    PRAGMA integrity_check;
} {ok}
do_execsql_test 8.5 { 
  COMMIT;
  PRAGMA integrity_check;
} {ok}




























































finish_test








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
    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 {}}
}


finish_test