gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/tkt2854.test (about)

     1  # 2007 December 20
     2  #
     3  # The author disclaims copyright to this source code.  In place of
     4  # a legal notice, here is a blessing:
     5  #
     6  #    May you do good and not evil.
     7  #    May you find forgiveness for yourself and forgive others.
     8  #    May you share freely, never taking more than you give.
     9  #
    10  #***********************************************************************
    11  #
    12  # $Id: tkt2854.test,v 1.4 2009/03/16 13:19:36 danielk1977 Exp $
    13  
    14  set testdir [file dirname $argv0]
    15  source $testdir/tester.tcl
    16  db close
    17  
    18  ifcapable !shared_cache {
    19    finish_test
    20    return
    21  }
    22  
    23  set ::enable_shared_cache [sqlite3_enable_shared_cache 1]
    24  
    25  # Open 3 database connections. Connection "db" and "db2" share a cache.
    26  # Connection "db3" has its own cache.
    27  #
    28  do_test tkt2854-1.1 {
    29    sqlite3 db test.db
    30    sqlite3 db2 test.db
    31    sqlite3 db3 "file:test.db?cache=private" -uri 1
    32  
    33    db eval {
    34      CREATE TABLE abc(a, b, c);
    35    }
    36  } {}
    37  
    38  # Check that an exclusive lock cannot be obtained if some other 
    39  # shared-cache connection has a read-lock on a table.
    40  #
    41  do_test tkt2854-1.2 {
    42    execsql { 
    43      BEGIN;
    44      SELECT * FROM abc;
    45    } db2
    46  } {}
    47  do_test tkt2854-1.3 {
    48    catchsql { BEGIN EXCLUSIVE } db
    49  } {1 {database table is locked}}
    50  do_test tkt2854-1.4 {
    51    execsql { SELECT * FROM abc } db3
    52  } {}
    53  do_test tkt2854-1.5 {
    54    catchsql { INSERT INTO abc VALUES(1, 2, 3) } db3
    55  } {1 {database is locked}}
    56  do_test tkt2854-1.6 {
    57    execsql { COMMIT } db2
    58  } {}
    59  
    60  # Check that an exclusive lock prevents other shared-cache users from
    61  # starting a transaction.
    62  #
    63  do_test tkt2854-1.7 {
    64    set ::DB2 [sqlite3_connection_pointer db2]
    65    set ::STMT1 [sqlite3_prepare $DB2 "SELECT * FROM abc" -1 TAIL]
    66    set ::STMT2 [sqlite3_prepare $DB2 "BEGIN EXCLUSIVE" -1 TAIL]
    67    set ::STMT3 [sqlite3_prepare $DB2 "BEGIN IMMEDIATE" -1 TAIL]
    68    set ::STMT4 [sqlite3_prepare $DB2 "BEGIN" -1 TAIL]
    69    set ::STMT5 [sqlite3_prepare $DB2 "COMMIT" -1 TAIL]
    70    execsql { BEGIN EXCLUSIVE } db
    71  } {}
    72  do_test tkt2854-1.8 {
    73    catchsql { BEGIN EXCLUSIVE } db2
    74  } {1 {database schema is locked: main}}
    75  do_test tkt2854-1.9 {
    76    catchsql { BEGIN IMMEDIATE } db2
    77  } {1 {database schema is locked: main}}
    78  do_test tkt2854-1.10 {
    79    # This fails because the schema of main cannot be verified.
    80    catchsql { BEGIN } db2
    81  } {1 {database schema is locked: main}}
    82  
    83  # Check that an exclusive lock prevents other shared-cache users from
    84  # reading the database. Use stored statements so that the error occurs
    85  # at the b-tree level, not the schema level.
    86  #
    87  do_test tkt2854-1.11 {
    88    list [sqlite3_step $::STMT1] [sqlite3_finalize $::STMT1]
    89  } {SQLITE_ERROR SQLITE_LOCKED}
    90  do_test tkt2854-1.12 {
    91    list [sqlite3_step $::STMT2] [sqlite3_finalize $::STMT2]
    92  } {SQLITE_ERROR SQLITE_LOCKED}
    93  do_test tkt2854-1.13 {
    94    list [sqlite3_step $::STMT3] [sqlite3_finalize $::STMT3]
    95  } {SQLITE_ERROR SQLITE_LOCKED}
    96  do_test tkt2854-1.14 {
    97    # A regular "BEGIN" doesn't touch any databases. So it succeeds.
    98    list [sqlite3_step $::STMT4] [sqlite3_finalize $::STMT4]
    99  } {SQLITE_DONE SQLITE_OK}
   100  do_test tkt2854-1.15 {
   101    # As does a COMMIT.
   102    list [sqlite3_step $::STMT5] [sqlite3_finalize $::STMT5]
   103  } {SQLITE_DONE SQLITE_OK}
   104  
   105  # Try to read the database using connection "db3" (which does not share
   106  # a cache with "db"). The database should be locked.
   107  do_test tkt2854-1.16 {
   108    catchsql { SELECT * FROM abc } db3
   109  } {1 {database is locked}}
   110  do_test tkt2854-1.17 {
   111    execsql { COMMIT } db
   112  } {}
   113  do_test tkt2854-1.18 {
   114    execsql { SELECT * FROM abc } db2
   115  } {}
   116  
   117  # Check that if an attempt to obtain an exclusive lock fails because an
   118  # attached db cannot be locked, the internal exclusive flag used by
   119  # shared-cache users is correctly cleared.
   120  do_test tkt2854-1.19 {
   121    forcedelete test2.db test2.db-journal
   122    sqlite3 db4 test2.db
   123    execsql { CREATE TABLE def(d, e, f) } db4
   124    execsql { ATTACH 'test2.db' AS aux } db
   125  } {}
   126  do_test tkt2854-1.20 {
   127    execsql {BEGIN IMMEDIATE} db4
   128    catchsql {BEGIN EXCLUSIVE} db
   129  } {1 {database table is locked}}
   130  do_test tkt2854-1.21 {
   131    execsql {SELECT * FROM abc} db2
   132  } {}
   133  
   134  db close
   135  db2 close
   136  db3 close
   137  db4 close
   138  sqlite3_enable_shared_cache $::enable_shared_cache
   139  finish_test