1 # 2001 September 15

     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 # This file implements regression tests for SQLite library.  The

    12 # focus of this script is database locks.

    13 #

    14 # $Id: lock.test,v 1.33 2006/08/16 16:42:48 drh Exp $

    15 

    16 

    17 set testdir [file dirname $argv0]

    18 source $testdir/tester.tcl

    19 

    20 # Create an alternative connection to the database

    21 #

    22 do_test lock-1.0 {

    23   sqlite3 db2 ./test.db

    24   set dummy {}

    25 } {}

    26 do_test lock-1.1 {

    27   execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}

    28 } {}

    29 do_test lock-1.2 {

    30   execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name} db2

    31 } {}

    32 do_test lock-1.3 {

    33   execsql {CREATE TABLE t1(a int, b int)}

    34   execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}

    35 } {t1}

    36 do_test lock-1.5 {

    37   catchsql {

    38      SELECT name FROM sqlite_master WHERE type='table' ORDER BY name

    39   } db2

    40 } {0 t1}

    41 

    42 do_test lock-1.6 {

    43   execsql {INSERT INTO t1 VALUES(1,2)}

    44   execsql {SELECT * FROM t1}

    45 } {1 2}

    46 # Update: The schema is now brought up to date by test lock-1.5.

    47 # do_test lock-1.7.1 {

    48 #   catchsql {SELECT * FROM t1} db2

    49 # } {1 {no such table: t1}}

    50 do_test lock-1.7.2 {

    51   catchsql {SELECT * FROM t1} db2

    52 } {0 {1 2}}

    53 do_test lock-1.8 {

    54   execsql {UPDATE t1 SET a=b, b=a} db2

    55   execsql {SELECT * FROM t1} db2

    56 } {2 1}

    57 do_test lock-1.9 {

    58   execsql {SELECT * FROM t1}

    59 } {2 1}

    60 do_test lock-1.10 {

    61   execsql {BEGIN TRANSACTION}

    62   execsql {UPDATE t1 SET a = 0 WHERE 0}

    63   execsql {SELECT * FROM t1}

    64 } {2 1}

    65 do_test lock-1.11 {

    66   catchsql {SELECT * FROM t1} db2

    67 } {0 {2 1}}

    68 do_test lock-1.12 {

    69   execsql {ROLLBACK}

    70   catchsql {SELECT * FROM t1}

    71 } {0 {2 1}}

    72 

    73 do_test lock-1.13 {

    74   execsql {CREATE TABLE t2(x int, y int)}

    75   execsql {INSERT INTO t2 VALUES(8,9)}

    76   execsql {SELECT * FROM t2}

    77 } {8 9}

    78 do_test lock-1.14.1 {

    79   catchsql {SELECT * FROM t2} db2

    80 } {1 {no such table: t2}}

    81 do_test lock-1.14.2 {

    82   catchsql {SELECT * FROM t1} db2

    83 } {0 {2 1}}

    84 do_test lock-1.15 {

    85   catchsql {SELECT * FROM t2} db2

    86 } {0 {8 9}}

    87 

    88 do_test lock-1.16 {

    89   db eval {SELECT * FROM t1} qv {

    90     set x [db eval {SELECT * FROM t1}]

    91   }

    92   set x

    93 } {2 1}

    94 do_test lock-1.17 {

    95   db eval {SELECT * FROM t1} qv {

    96     set x [db eval {SELECT * FROM t2}]

    97   }

    98   set x

    99 } {8 9}

   100 

   101 # You cannot UPDATE a table from within the callback of a SELECT

   102 # on that same table because the SELECT has the table locked.

   103 #

   104 # 2006-08-16:  Reads no longer block writes within the same

   105 # database connection.

   106 #

   107 #do_test lock-1.18 {

   108 #  db eval {SELECT * FROM t1} qv {

   109 #    set r [catch {db eval {UPDATE t1 SET a=b, b=a}} msg]

   110 #    lappend r $msg

   111 #  }

   112 #  set r

   113 #} {1 {database table is locked}}

   114 

   115 # But you can UPDATE a different table from the one that is used in

   116 # the SELECT.

   117 #

   118 do_test lock-1.19 {

   119   db eval {SELECT * FROM t1} qv {

   120     set r [catch {db eval {UPDATE t2 SET x=y, y=x}} msg]

   121     lappend r $msg

   122   }

   123   set r

   124 } {0 {}}

   125 do_test lock-1.20 {

   126   execsql {SELECT * FROM t2}

   127 } {9 8}

   128 

   129 # It is possible to do a SELECT of the same table within the

   130 # callback of another SELECT on that same table because two

   131 # or more read-only cursors can be open at once.

   132 #

   133 do_test lock-1.21 {

   134   db eval {SELECT * FROM t1} qv {

   135     set r [catch {db eval {SELECT a FROM t1}} msg]

   136     lappend r $msg

   137   }

   138   set r

   139 } {0 2}

   140 

   141 # Under UNIX you can do two SELECTs at once with different database

   142 # connections, because UNIX supports reader/writer locks.  Under windows,

   143 # this is not possible.

   144 #

   145 if {$::tcl_platform(platform)=="unix"} {

   146   do_test lock-1.22 {

   147     db eval {SELECT * FROM t1} qv {

   148       set r [catch {db2 eval {SELECT a FROM t1}} msg]

   149       lappend r $msg

   150     }

   151     set r

   152   } {0 2}

   153 }

   154 integrity_check lock-1.23

   155 

   156 # If one thread has a transaction another thread cannot start

   157 # a transaction.  -> Not true in version 3.0.  But if one thread

   158 # as a RESERVED lock another thread cannot acquire one.

   159 #

   160 do_test lock-2.1 {

   161   execsql {BEGIN TRANSACTION}

   162   execsql {UPDATE t1 SET a = 0 WHERE 0}

   163   execsql {BEGIN TRANSACTION} db2

   164   set r [catch {execsql {UPDATE t1 SET a = 0 WHERE 0} db2} msg]

   165   execsql {ROLLBACK} db2

   166   lappend r $msg

   167 } {1 {database is locked}}

   168 

   169 # A thread can read when another has a RESERVED lock.

   170 #

   171 do_test lock-2.2 {

   172   catchsql {SELECT * FROM t2} db2

   173 } {0 {9 8}}

   174 

   175 # If the other thread (the one that does not hold the transaction with

   176 # a RESERVED lock) tries to get a RESERVED lock, we do get a busy callback

   177 # as long as we were not orginally holding a READ lock.

   178 #

   179 do_test lock-2.3.1 {

   180   proc callback {count} {

   181     set ::callback_value $count

   182     break

   183   }

   184   set ::callback_value {}

   185   db2 busy callback

   186   # db2 does not hold a lock so we should get a busy callback here

   187   set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg]

   188   lappend r $msg

   189   lappend r $::callback_value

   190 } {1 {database is locked} 0}

   191 do_test lock-2.3.2 {

   192   set ::callback_value {}

   193   execsql {BEGIN; SELECT rowid FROM sqlite_master LIMIT 1} db2

   194   # This time db2 does hold a read lock.  No busy callback this time.

   195   set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg]

   196   lappend r $msg

   197   lappend r $::callback_value

   198 } {1 {database is locked} {}}

   199 catch {execsql {ROLLBACK} db2}

   200 do_test lock-2.4.1 {

   201   proc callback {count} {

   202     lappend ::callback_value $count

   203     if {$count>4} break

   204   }

   205   set ::callback_value {}

   206   db2 busy callback

   207   # We get a busy callback because db2 is not holding a lock

   208   set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg]

   209   lappend r $msg

   210   lappend r $::callback_value

   211 } {1 {database is locked} {0 1 2 3 4 5}}

   212 do_test lock-2.4.2 {

   213   proc callback {count} {

   214     lappend ::callback_value $count

   215     if {$count>4} break

   216   }

   217   set ::callback_value {}

   218   db2 busy callback

   219   execsql {BEGIN; SELECT rowid FROM sqlite_master LIMIT 1} db2

   220   # No busy callback this time because we are holding a lock

   221   set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg]

   222   lappend r $msg

   223   lappend r $::callback_value

   224 } {1 {database is locked} {}}

   225 catch {execsql {ROLLBACK} db2}

   226 do_test lock-2.5 {

   227   proc callback {count} {

   228     lappend ::callback_value $count

   229     if {$count>4} break

   230   }

   231   set ::callback_value {}

   232   db2 busy callback

   233   set r [catch {execsql {SELECT * FROM t1} db2} msg]

   234   lappend r $msg

   235   lappend r $::callback_value

   236 } {0 {2 1} {}}

   237 execsql {ROLLBACK}

   238 

   239 # Test the built-in busy timeout handler

   240 #

   241 do_test lock-2.8 {

   242   db2 timeout 400

   243   execsql BEGIN

   244   execsql {UPDATE t1 SET a = 0 WHERE 0}

   245   catchsql {BEGIN EXCLUSIVE;} db2

   246 } {1 {database is locked}}

   247 do_test lock-2.9 {

   248   db2 timeout 0

   249   execsql COMMIT

   250 } {}

   251 integrity_check lock-2.10

   252 

   253 # Try to start two transactions in a row

   254 #

   255 do_test lock-3.1 {

   256   execsql {BEGIN TRANSACTION}

   257   set r [catch {execsql {BEGIN TRANSACTION}} msg]

   258   execsql {ROLLBACK}

   259   lappend r $msg

   260 } {1 {cannot start a transaction within a transaction}}

   261 integrity_check lock-3.2

   262 

   263 # Make sure the busy handler and error messages work when

   264 # opening a new pointer to the database while another pointer

   265 # has the database locked.

   266 #

   267 do_test lock-4.1 {

   268   db2 close

   269   catch {db eval ROLLBACK}

   270   db eval BEGIN

   271   db eval {UPDATE t1 SET a=0 WHERE 0}

   272   sqlite3 db2 ./test.db

   273   catchsql {UPDATE t1 SET a=0} db2

   274 } {1 {database is locked}}

   275 do_test lock-4.2 {

   276   set ::callback_value {}

   277   set rc [catch {db2 eval {UPDATE t1 SET a=0}} msg]

   278   lappend rc $msg $::callback_value

   279 } {1 {database is locked} {}}

   280 do_test lock-4.3 {

   281   proc callback {count} {

   282     lappend ::callback_value $count

   283     if {$count>4} break

   284   }

   285   db2 busy callback

   286   set rc [catch {db2 eval {UPDATE t1 SET a=0}} msg]

   287   lappend rc $msg $::callback_value

   288 } {1 {database is locked} {0 1 2 3 4 5}}

   289 execsql {ROLLBACK}

   290 

   291 # When one thread is writing, other threads cannot read.  Except if the

   292 # writing thread is writing to its temporary tables, the other threads

   293 # can still read.  -> Not so in 3.0.  One thread can read while another

   294 # holds a RESERVED lock.

   295 #

   296 proc tx_exec {sql} {

   297   db2 eval $sql

   298 }

   299 do_test lock-5.1 {

   300   execsql {

   301     SELECT * FROM t1

   302   }

   303 } {2 1}

   304 do_test lock-5.2 {

   305   db function tx_exec tx_exec

   306   catchsql {

   307     INSERT INTO t1(a,b) SELECT 3, tx_exec('SELECT y FROM t2 LIMIT 1');

   308   }

   309 } {0 {}}

   310 

   311 ifcapable tempdb {

   312   do_test lock-5.3 {

   313     execsql {

   314       CREATE TEMP TABLE t3(x);

   315       SELECT * FROM t3;

   316     }

   317   } {}

   318   do_test lock-5.4 {

   319     catchsql {

   320       INSERT INTO t3 SELECT tx_exec('SELECT y FROM t2 LIMIT 1');

   321     }

   322   } {0 {}}

   323   do_test lock-5.5 {

   324     execsql {

   325       SELECT * FROM t3;

   326     }

   327   } {8}

   328   do_test lock-5.6 {

   329     catchsql {

   330       UPDATE t1 SET a=tx_exec('SELECT x FROM t2');

   331     }

   332   } {0 {}}

   333   do_test lock-5.7 {

   334     execsql {

   335       SELECT * FROM t1;

   336     }

   337   } {9 1 9 8}

   338   do_test lock-5.8 {

   339     catchsql {

   340       UPDATE t3 SET x=tx_exec('SELECT x FROM t2');

   341     }

   342   } {0 {}}

   343   do_test lock-5.9 {

   344     execsql {

   345       SELECT * FROM t3;

   346     }

   347   } {9}

   348 }

   349 

   350 do_test lock-999.1 {

   351   rename db2 {}

   352 } {}

   353 

   354 finish_test