File:  [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test / lock.test
Revision 1.1.1.1 (vendor branch): download - view: text, annotated - select for diffs - revision graph
Tue Feb 21 17:04:16 2012 UTC (12 years, 10 months ago) by misho
Branches: sqlite3, MAIN
CVS tags: v3_7_10, HEAD
sqlite3

    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.1.1.1 2012/02/21 17:04:16 misho 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:   # Give a complex pathname to stress the path simplification logic in
   24:   # the vxworks driver and in test_async.
   25:   file mkdir tempdir/t1/t2
   26:   sqlite3 db2 ./tempdir/../tempdir/t1/.//t2/../../..//test.db
   27:   set dummy {}
   28: } {}
   29: do_test lock-1.1 {
   30:   execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}
   31: } {}
   32: do_test lock-1.2 {
   33:   execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name} db2
   34: } {}
   35: do_test lock-1.3 {
   36:   execsql {CREATE TABLE t1(a int, b int)}
   37:   execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}
   38: } {t1}
   39: do_test lock-1.5 {
   40:   catchsql {
   41:      SELECT name FROM sqlite_master WHERE type='table' ORDER BY name
   42:   } db2
   43: } {0 t1}
   44: 
   45: do_test lock-1.6 {
   46:   execsql {INSERT INTO t1 VALUES(1,2)}
   47:   execsql {SELECT * FROM t1}
   48: } {1 2}
   49: # Update: The schema is now brought up to date by test lock-1.5.
   50: # do_test lock-1.7.1 {
   51: #   catchsql {SELECT * FROM t1} db2
   52: # } {1 {no such table: t1}}
   53: do_test lock-1.7.2 {
   54:   catchsql {SELECT * FROM t1} db2
   55: } {0 {1 2}}
   56: do_test lock-1.8 {
   57:   execsql {UPDATE t1 SET a=b, b=a} db2
   58:   execsql {SELECT * FROM t1} db2
   59: } {2 1}
   60: do_test lock-1.9 {
   61:   execsql {SELECT * FROM t1}
   62: } {2 1}
   63: do_test lock-1.10 {
   64:   execsql {BEGIN TRANSACTION}
   65:   execsql {UPDATE t1 SET a = 0 WHERE 0}
   66:   execsql {SELECT * FROM t1}
   67: } {2 1}
   68: do_test lock-1.11 {
   69:   catchsql {SELECT * FROM t1} db2
   70: } {0 {2 1}}
   71: do_test lock-1.12 {
   72:   execsql {ROLLBACK}
   73:   catchsql {SELECT * FROM t1}
   74: } {0 {2 1}}
   75: 
   76: do_test lock-1.13 {
   77:   execsql {CREATE TABLE t2(x int, y int)}
   78:   execsql {INSERT INTO t2 VALUES(8,9)}
   79:   execsql {SELECT * FROM t2}
   80: } {8 9}
   81: do_test lock-1.14.1 {
   82:   catchsql {SELECT * FROM t2} db2
   83: } {0 {8 9}}
   84: do_test lock-1.14.2 {
   85:   catchsql {SELECT * FROM t1} db2
   86: } {0 {2 1}}
   87: do_test lock-1.15 {
   88:   catchsql {SELECT * FROM t2} db2
   89: } {0 {8 9}}
   90: 
   91: do_test lock-1.16 {
   92:   db eval {SELECT * FROM t1} qv {
   93:     set x [db eval {SELECT * FROM t1}]
   94:   }
   95:   set x
   96: } {2 1}
   97: do_test lock-1.17 {
   98:   db eval {SELECT * FROM t1} qv {
   99:     set x [db eval {SELECT * FROM t2}]
  100:   }
  101:   set x
  102: } {8 9}
  103: 
  104: # You cannot UPDATE a table from within the callback of a SELECT
  105: # on that same table because the SELECT has the table locked.
  106: #
  107: # 2006-08-16:  Reads no longer block writes within the same
  108: # database connection.
  109: #
  110: #do_test lock-1.18 {
  111: #  db eval {SELECT * FROM t1} qv {
  112: #    set r [catch {db eval {UPDATE t1 SET a=b, b=a}} msg]
  113: #    lappend r $msg
  114: #  }
  115: #  set r
  116: #} {1 {database table is locked}}
  117: 
  118: # But you can UPDATE a different table from the one that is used in
  119: # the SELECT.
  120: #
  121: do_test lock-1.19 {
  122:   db eval {SELECT * FROM t1} qv {
  123:     set r [catch {db eval {UPDATE t2 SET x=y, y=x}} msg]
  124:     lappend r $msg
  125:   }
  126:   set r
  127: } {0 {}}
  128: do_test lock-1.20 {
  129:   execsql {SELECT * FROM t2}
  130: } {9 8}
  131: 
  132: # It is possible to do a SELECT of the same table within the
  133: # callback of another SELECT on that same table because two
  134: # or more read-only cursors can be open at once.
  135: #
  136: do_test lock-1.21 {
  137:   db eval {SELECT * FROM t1} qv {
  138:     set r [catch {db eval {SELECT a FROM t1}} msg]
  139:     lappend r $msg
  140:   }
  141:   set r
  142: } {0 2}
  143: 
  144: # Under UNIX you can do two SELECTs at once with different database
  145: # connections, because UNIX supports reader/writer locks.  Under windows,
  146: # this is not possible.
  147: #
  148: if {$::tcl_platform(platform)=="unix"} {
  149:   do_test lock-1.22 {
  150:     db eval {SELECT * FROM t1} qv {
  151:       set r [catch {db2 eval {SELECT a FROM t1}} msg]
  152:       lappend r $msg
  153:     }
  154:     set r
  155:   } {0 2}
  156: }
  157: integrity_check lock-1.23
  158: 
  159: # If one thread has a transaction another thread cannot start
  160: # a transaction.  -> Not true in version 3.0.  But if one thread
  161: # as a RESERVED lock another thread cannot acquire one.
  162: #
  163: do_test lock-2.1 {
  164:   execsql {BEGIN TRANSACTION}
  165:   execsql {UPDATE t1 SET a = 0 WHERE 0}
  166:   execsql {BEGIN TRANSACTION} db2
  167:   set r [catch {execsql {UPDATE t1 SET a = 0 WHERE 0} db2} msg]
  168:   execsql {ROLLBACK} db2
  169:   lappend r $msg
  170: } {1 {database is locked}}
  171: 
  172: # A thread can read when another has a RESERVED lock.
  173: #
  174: do_test lock-2.2 {
  175:   catchsql {SELECT * FROM t2} db2
  176: } {0 {9 8}}
  177: 
  178: # If the other thread (the one that does not hold the transaction with
  179: # a RESERVED lock) tries to get a RESERVED lock, we do get a busy callback
  180: # as long as we were not orginally holding a READ lock.
  181: #
  182: do_test lock-2.3.1 {
  183:   proc callback {count} {
  184:     set ::callback_value $count
  185:     break
  186:   }
  187:   set ::callback_value {}
  188:   db2 busy callback
  189:   # db2 does not hold a lock so we should get a busy callback here
  190:   set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg]
  191:   lappend r $msg
  192:   lappend r $::callback_value
  193: } {1 {database is locked} 0}
  194: do_test lock-2.3.2 {
  195:   set ::callback_value {}
  196:   execsql {BEGIN; SELECT rowid FROM sqlite_master LIMIT 1} db2
  197:   # This time db2 does hold a read lock.  No busy callback this time.
  198:   set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg]
  199:   lappend r $msg
  200:   lappend r $::callback_value
  201: } {1 {database is locked} {}}
  202: catch {execsql {ROLLBACK} db2}
  203: do_test lock-2.4.1 {
  204:   proc callback {count} {
  205:     lappend ::callback_value $count
  206:     if {$count>4} break
  207:   }
  208:   set ::callback_value {}
  209:   db2 busy callback
  210:   # We get a busy callback because db2 is not holding a lock
  211:   set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg]
  212:   lappend r $msg
  213:   lappend r $::callback_value
  214: } {1 {database is locked} {0 1 2 3 4 5}}
  215: do_test lock-2.4.2 {
  216:   proc callback {count} {
  217:     lappend ::callback_value $count
  218:     if {$count>4} break
  219:   }
  220:   set ::callback_value {}
  221:   db2 busy callback
  222:   execsql {BEGIN; SELECT rowid FROM sqlite_master LIMIT 1} db2
  223:   # No busy callback this time because we are holding a lock
  224:   set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg]
  225:   lappend r $msg
  226:   lappend r $::callback_value
  227: } {1 {database is locked} {}}
  228: catch {execsql {ROLLBACK} db2}
  229: do_test lock-2.5 {
  230:   proc callback {count} {
  231:     lappend ::callback_value $count
  232:     if {$count>4} break
  233:   }
  234:   set ::callback_value {}
  235:   db2 busy callback
  236:   set r [catch {execsql {SELECT * FROM t1} db2} msg]
  237:   lappend r $msg
  238:   lappend r $::callback_value
  239: } {0 {2 1} {}}
  240: execsql {ROLLBACK}
  241: 
  242: # Test the built-in busy timeout handler
  243: #
  244: do_test lock-2.8 {
  245:   db2 timeout 400
  246:   execsql BEGIN
  247:   execsql {UPDATE t1 SET a = 0 WHERE 0}
  248:   catchsql {BEGIN EXCLUSIVE;} db2
  249: } {1 {database is locked}}
  250: do_test lock-2.9 {
  251:   db2 timeout 0
  252:   execsql COMMIT
  253: } {}
  254: integrity_check lock-2.10
  255: 
  256: # Try to start two transactions in a row
  257: #
  258: do_test lock-3.1 {
  259:   execsql {BEGIN TRANSACTION}
  260:   set r [catch {execsql {BEGIN TRANSACTION}} msg]
  261:   execsql {ROLLBACK}
  262:   lappend r $msg
  263: } {1 {cannot start a transaction within a transaction}}
  264: integrity_check lock-3.2
  265: 
  266: # Make sure the busy handler and error messages work when
  267: # opening a new pointer to the database while another pointer
  268: # has the database locked.
  269: #
  270: do_test lock-4.1 {
  271:   db2 close
  272:   catch {db eval ROLLBACK}
  273:   db eval BEGIN
  274:   db eval {UPDATE t1 SET a=0 WHERE 0}
  275:   sqlite3 db2 ./test.db
  276:   catchsql {UPDATE t1 SET a=0} db2
  277: } {1 {database is locked}}
  278: do_test lock-4.2 {
  279:   set ::callback_value {}
  280:   set rc [catch {db2 eval {UPDATE t1 SET a=0}} msg]
  281:   lappend rc $msg $::callback_value
  282: } {1 {database is locked} {}}
  283: do_test lock-4.3 {
  284:   proc callback {count} {
  285:     lappend ::callback_value $count
  286:     if {$count>4} break
  287:   }
  288:   db2 busy callback
  289:   set rc [catch {db2 eval {UPDATE t1 SET a=0}} msg]
  290:   lappend rc $msg $::callback_value
  291: } {1 {database is locked} {0 1 2 3 4 5}}
  292: execsql {ROLLBACK}
  293: 
  294: # When one thread is writing, other threads cannot read.  Except if the
  295: # writing thread is writing to its temporary tables, the other threads
  296: # can still read.  -> Not so in 3.0.  One thread can read while another
  297: # holds a RESERVED lock.
  298: #
  299: proc tx_exec {sql} {
  300:   db2 eval $sql
  301: }
  302: do_test lock-5.1 {
  303:   execsql {
  304:     SELECT * FROM t1
  305:   }
  306: } {2 1}
  307: do_test lock-5.2 {
  308:   db function tx_exec tx_exec
  309:   catchsql {
  310:     INSERT INTO t1(a,b) SELECT 3, tx_exec('SELECT y FROM t2 LIMIT 1');
  311:   }
  312: } {0 {}}
  313: 
  314: ifcapable tempdb {
  315:   do_test lock-5.3 {
  316:     execsql {
  317:       CREATE TEMP TABLE t3(x);
  318:       SELECT * FROM t3;
  319:     }
  320:   } {}
  321:   do_test lock-5.4 {
  322:     catchsql {
  323:       INSERT INTO t3 SELECT tx_exec('SELECT y FROM t2 LIMIT 1');
  324:     }
  325:   } {0 {}}
  326:   do_test lock-5.5 {
  327:     execsql {
  328:       SELECT * FROM t3;
  329:     }
  330:   } {8}
  331:   do_test lock-5.6 {
  332:     catchsql {
  333:       UPDATE t1 SET a=tx_exec('SELECT x FROM t2');
  334:     }
  335:   } {0 {}}
  336:   do_test lock-5.7 {
  337:     execsql {
  338:       SELECT * FROM t1;
  339:     }
  340:   } {9 1 9 8}
  341:   do_test lock-5.8 {
  342:     catchsql {
  343:       UPDATE t3 SET x=tx_exec('SELECT x FROM t2');
  344:     }
  345:   } {0 {}}
  346:   do_test lock-5.9 {
  347:     execsql {
  348:       SELECT * FROM t3;
  349:     }
  350:   } {9}
  351: }
  352: 
  353: do_test lock-6.1 {
  354:   execsql {
  355:     CREATE TABLE t4(a PRIMARY KEY, b);
  356:     INSERT INTO t4 VALUES(1, 'one');
  357:     INSERT INTO t4 VALUES(2, 'two');
  358:     INSERT INTO t4 VALUES(3, 'three');
  359:   }
  360: 
  361:   set STMT [sqlite3_prepare $DB "SELECT * FROM sqlite_master" -1 TAIL]
  362:   sqlite3_step $STMT
  363: 
  364:   execsql { DELETE FROM t4 }
  365:   execsql { SELECT * FROM sqlite_master } db2
  366:   execsql { SELECT * FROM t4 } db2
  367: } {}
  368: 
  369: do_test lock-6.2 {
  370:   execsql { 
  371:     BEGIN;
  372:     INSERT INTO t4 VALUES(1, 'one');
  373:     INSERT INTO t4 VALUES(2, 'two');
  374:     INSERT INTO t4 VALUES(3, 'three');
  375:     COMMIT;
  376:   }
  377: 
  378:   execsql { SELECT * FROM t4 } db2
  379: } {1 one 2 two 3 three}
  380: 
  381: do_test lock-6.3 {
  382:   execsql { SELECT a FROM t4 ORDER BY a } db2
  383: } {1 2 3}
  384: 
  385: do_test lock-6.4 {
  386:   execsql { PRAGMA integrity_check } db2
  387: } {ok}
  388: 
  389: do_test lock-6.5 {
  390:   sqlite3_finalize $STMT
  391: } {SQLITE_OK}
  392: 
  393: # At one point the following set of conditions would cause SQLite to 
  394: # retain a RESERVED or EXCLUSIVE lock after the transaction was committed:
  395: # 
  396: #   * The journal-mode is set to something other than 'delete', and
  397: #   * there exists one or more active read-only statements, and
  398: #   * a transaction that modified zero database pages is committed.
  399: # 
  400: set temp_status unlocked
  401: if {$TEMP_STORE>=2} {set temp_status unknown}
  402: do_test lock-7.1 {
  403:   set STMT [sqlite3_prepare $DB "SELECT * FROM sqlite_master" -1 TAIL]
  404:   sqlite3_step $STMT
  405: } {SQLITE_ROW}
  406: do_test lock-7.2 {
  407:   execsql { PRAGMA lock_status }
  408: } [list main shared temp $temp_status]
  409: do_test lock-7.3 {
  410:   execsql {
  411:     PRAGMA journal_mode = truncate;
  412:     BEGIN;
  413:     UPDATE t4 SET a = 10 WHERE 0;
  414:     COMMIT;
  415:   }
  416:   execsql { PRAGMA lock_status }
  417: } [list main shared temp $temp_status]
  418: do_test lock-7.4 {
  419:   sqlite3_finalize $STMT
  420: } {SQLITE_OK}
  421: 
  422: do_test lock-999.1 {
  423:   rename db2 {}
  424: } {}
  425: 
  426: finish_test

FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>