/ Check-in [1a3e7417]
Login

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

Overview
Comment:Add file test_superlock.c with example code for obtaining an exclusive lock on either rollback or wal mode databases.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | superlock
Files: files | file ages | folders
SHA1: 1a3e7417a2184188fe21c3284e58720da9ca11cf
User & Date: dan 2010-11-19 18:20:10
Context
2010-11-19
18:36
Merge in the superlock demonstration changes. check-in: 570e79a8 user: drh tags: trunk
18:20
Add file test_superlock.c with example code for obtaining an exclusive lock on either rollback or wal mode databases. Closed-Leaf check-in: 1a3e7417 user: dan tags: superlock
14:37
Add the SQLITE_FCNTL_FILE_POINTER verb to sqlite3_file_control(). check-in: 4425b064 user: drh tags: superlock
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to main.mk.

   247    247     $(TOP)/src/test_osinst.c \
   248    248     $(TOP)/src/test_pcache.c \
   249    249     $(TOP)/src/test_quota.c \
   250    250     $(TOP)/src/test_rtree.c \
   251    251     $(TOP)/src/test_schema.c \
   252    252     $(TOP)/src/test_server.c \
   253    253     $(TOP)/src/test_stat.c \
          254  +  $(TOP)/src/test_superlock.c \
   254    255     $(TOP)/src/test_tclvar.c \
   255    256     $(TOP)/src/test_thread.c \
   256    257     $(TOP)/src/test_vfs.c \
   257    258     $(TOP)/src/test_wsd.c
   258    259   
   259    260   #TESTSRC += $(TOP)/ext/fts2/fts2_tokenizer.c
   260    261   #TESTSRC += $(TOP)/ext/fts3/fts3_tokenizer.c

Changes to src/tclsqlite.c.

  3576   3576       extern int Sqlitetestbackup_Init(Tcl_Interp*);
  3577   3577       extern int Sqlitetestintarray_Init(Tcl_Interp*);
  3578   3578       extern int Sqlitetestvfs_Init(Tcl_Interp *);
  3579   3579       extern int SqlitetestStat_Init(Tcl_Interp*);
  3580   3580       extern int Sqlitetestrtree_Init(Tcl_Interp*);
  3581   3581       extern int Sqlitequota_Init(Tcl_Interp*);
  3582   3582       extern int Sqlitemultiplex_Init(Tcl_Interp*);
         3583  +    extern int SqliteSuperlock_Init(Tcl_Interp*);
  3583   3584   
  3584   3585       Sqliteconfig_Init(interp);
  3585   3586       Sqlitetest1_Init(interp);
  3586   3587       Sqlitetest2_Init(interp);
  3587   3588       Sqlitetest3_Init(interp);
  3588   3589       Sqlitetest4_Init(interp);
  3589   3590       Sqlitetest5_Init(interp);
................................................................................
  3607   3608       Sqlitetestbackup_Init(interp);
  3608   3609       Sqlitetestintarray_Init(interp);
  3609   3610       Sqlitetestvfs_Init(interp);
  3610   3611       SqlitetestStat_Init(interp);
  3611   3612       Sqlitetestrtree_Init(interp);
  3612   3613       Sqlitequota_Init(interp);
  3613   3614       Sqlitemultiplex_Init(interp);
         3615  +    SqliteSuperlock_Init(interp);
  3614   3616   
  3615   3617       Tcl_CreateObjCommand(interp,"load_testfixture_extensions",init_all_cmd,0,0);
  3616   3618   
  3617   3619   #ifdef SQLITE_SSE
  3618   3620       Sqlitetestsse_Init(interp);
  3619   3621   #endif
  3620   3622     }

Added src/test_superlock.c.

            1  +/*
            2  +** 2010 November 19
            3  +**
            4  +** The author disclaims copyright to this source code.  In place of
            5  +** a legal notice, here is a blessing:
            6  +**
            7  +**    May you do good and not evil.
            8  +**    May you find forgiveness for yourself and forgive others.
            9  +**    May you share freely, never taking more than you give.
           10  +**
           11  +*************************************************************************
           12  +** Example code for obtaining an exclusive lock on an SQLite database
           13  +** file. This method is complicated, but works for both WAL and rollback
           14  +** mode database files. The interface to the example code in this file 
           15  +** consists of the following two functions:
           16  +**
           17  +**   sqlite3demo_superlock()
           18  +**   sqlite3demo_superunlock()
           19  +*/
           20  +
           21  +#include <sqlite3.h>
           22  +#include <string.h>               /* memset(), strlen() */
           23  +#include <assert.h>               /* assert() */
           24  +
           25  +/*
           26  +** A structure to collect a busy-handler callback and argument and a count
           27  +** of the number of times it has been invoked.
           28  +*/
           29  +struct SuperlockBusy {
           30  +  int (*xBusy)(void*,int);        /* Pointer to busy-handler function */
           31  +  void *pBusyArg;                 /* First arg to pass to xBusy */
           32  +  int nBusy;                      /* Number of times xBusy has been invoked */
           33  +};
           34  +typedef struct SuperlockBusy SuperlockBusy;
           35  +
           36  +/*
           37  +** The pCtx pointer passed to this function is actually a pointer to a
           38  +** SuperlockBusy structure. Invoke the busy-handler function encapsulated
           39  +** by the structure and return the result.
           40  +*/
           41  +static int superlockBusyHandler(void *pCtx, int UNUSED){
           42  +  SuperlockBusy *pBusy = (SuperlockBusy *)pCtx;
           43  +  if( pBusy->xBusy==0 ) return 0;
           44  +  return pBusy->xBusy(pBusy->pBusyArg, pBusy->nBusy++);
           45  +}
           46  +
           47  +/*
           48  +** This function is used to determine if the main database file for 
           49  +** connection db is open in WAL mode or not. If no error occurs and the
           50  +** database file is in WAL mode, set *pbWal to true and return SQLITE_OK.
           51  +** If it is not in WAL mode, set *pbWal to false.
           52  +**
           53  +** If an error occurs, return an SQLite error code. The value of *pbWal
           54  +** is undefined in this case.
           55  +*/
           56  +static int superlockIsWal(sqlite3 *db, int *pbWal){
           57  +  int rc;                         /* Return Code */
           58  +  sqlite3_stmt *pStmt;            /* Compiled PRAGMA journal_mode statement */
           59  +
           60  +  rc = sqlite3_prepare(db, "PRAGMA main.journal_mode", -1, &pStmt, 0);
           61  +  if( rc!=SQLITE_OK ) return rc;
           62  +
           63  +  *pbWal = 0;
           64  +  if( SQLITE_ROW==sqlite3_step(pStmt) ){
           65  +    const char *zMode = (const char *)sqlite3_column_text(pStmt, 0);
           66  +    if( zMode && strlen(zMode)==3 && sqlite3_strnicmp("wal", zMode, 3)==0 ){
           67  +      *pbWal = 1;
           68  +    }
           69  +  }
           70  +
           71  +  return sqlite3_finalize(pStmt);
           72  +}
           73  +
           74  +/*
           75  +** Obtain an exclusive shm-lock on nByte bytes starting at offset idx
           76  +** of the file fd. If the lock cannot be obtained immediately, invoke
           77  +** the busy-handler until either it is obtained or the busy-handler
           78  +** callback returns 0.
           79  +*/
           80  +static int superlockShmLock(
           81  +  sqlite3_file *fd,               /* Database file handle */
           82  +  int idx,                        /* Offset of shm-lock to obtain */
           83  +  int nByte,                      /* Number of consective bytes to lock */
           84  +  SuperlockBusy *pBusy            /* Busy-handler wrapper object */
           85  +){
           86  +  int rc;
           87  +  int (*xShmLock)(sqlite3_file*, int, int, int) = fd->pMethods->xShmLock;
           88  +  do {
           89  +    rc = xShmLock(fd, idx, nByte, SQLITE_SHM_LOCK|SQLITE_SHM_EXCLUSIVE);
           90  +  }while( rc==SQLITE_BUSY && superlockBusyHandler((void *)pBusy, 0) );
           91  +  return rc;
           92  +}
           93  +
           94  +/*
           95  +** Obtain the extra locks on the database file required for WAL databases.
           96  +** Invoke the supplied busy-handler as required.
           97  +*/
           98  +static int superlockWalLock(
           99  +  sqlite3 *db,                    /* Database handle open on WAL database */
          100  +  SuperlockBusy *pBusy            /* Busy handler wrapper object */
          101  +){
          102  +  int rc;                         /* Return code */
          103  +  sqlite3_file *fd = 0;           /* Main database file handle */
          104  +  void volatile *p = 0;           /* Pointer to first page of shared memory */
          105  +  int nBusy = 0;                  /* Number of calls already made to xBusy */
          106  +
          107  +  /* Obtain a pointer to the sqlite3_file object open on the main db file. */
          108  +  rc = sqlite3_file_control(db, "main", SQLITE_FCNTL_FILE_POINTER, (void *)&fd);
          109  +  if( rc!=SQLITE_OK ) return rc;
          110  +
          111  +  /* Obtain the "recovery" lock. Normally, this lock is only obtained by
          112  +  ** clients running database recovery.  
          113  +  */
          114  +  rc = superlockShmLock(fd, 2, 1, pBusy);
          115  +  if( rc!=SQLITE_OK ) return rc;
          116  +
          117  +  /* Zero the start of the first shared-memory page. This means that any
          118  +  ** clients that open read or write transactions from this point on will
          119  +  ** have to run recovery before proceeding. Since they need the "recovery"
          120  +  ** lock that this process is holding to do that, no new read or write
          121  +  ** transactions may now be opened. Nor can a checkpoint be run, for the
          122  +  ** same reason.
          123  +  */
          124  +  rc = fd->pMethods->xShmMap(fd, 0, 32*1024, 1, &p);
          125  +  if( rc!=SQLITE_OK ) return rc;
          126  +  memset((void *)p, 0, 32);
          127  +
          128  +  /* Obtain exclusive locks on all the "read-lock" slots. Once these locks
          129  +  ** are held, it is guaranteed that there are no active reader, writer or 
          130  +  ** checkpointer clients.
          131  +  */
          132  +  rc = superlockShmLock(fd, 3, SQLITE_SHM_NLOCK-3, pBusy);
          133  +  return rc;
          134  +}
          135  +
          136  +/*
          137  +** Obtain a superlock on the database file identified by zPath, using the
          138  +** locking primitives provided by VFS zVfs. If successful, SQLITE_OK is
          139  +** returned and output variable *ppLock is populated with an opaque handle
          140  +** that may be used with sqlite3demo_superunlock() to release the lock.
          141  +**
          142  +** If an error occurs, *ppLock is set to 0 and an SQLite error code 
          143  +** (e.g. SQLITE_BUSY) is returned.
          144  +**
          145  +** If a required lock cannot be obtained immediately and the xBusy parameter
          146  +** to this function is not NULL, then xBusy is invoked in the same way
          147  +** as a busy-handler registered with SQLite (using sqlite3_busy_handler())
          148  +** until either the lock can be obtained or the busy-handler function returns
          149  +** 0 (indicating "give up").
          150  +*/
          151  +int sqlite3demo_superlock(
          152  +  const char *zPath,              /* Path to database file to lock */
          153  +  const char *zVfs,               /* VFS to use to access database file */
          154  +  int (*xBusy)(void*,int),        /* Busy handler callback */
          155  +  void *pBusyArg,                 /* Context arg for busy handler */
          156  +  void **ppLock                   /* OUT: Context to pass to superunlock() */
          157  +){
          158  +  sqlite3 *db = 0;                /* Database handle open on zPath */
          159  +  SuperlockBusy busy = {0, 0, 0}; /* Busy handler wrapper object */
          160  +  int rc;                         /* Return code */
          161  +
          162  +  /* Open a database handle on the file to superlock. */
          163  +  rc = sqlite3_open_v2(
          164  +      zPath, &db, SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, zVfs
          165  +  );
          166  +
          167  +  /* Install a busy-handler and execute a BEGIN EXCLUSIVE. If this is not
          168  +  ** a WAL database, this is all we need to do.  
          169  +  **
          170  +  ** A wrapper function is used to invoke the busy-handler instead of
          171  +  ** registering the busy-handler function supplied by the user directly
          172  +  ** with SQLite. This is because the same busy-handler function may be
          173  +  ** invoked directly later on when attempting to obtain the extra locks
          174  +  ** required in WAL mode. By using the wrapper, we are able to guarantee
          175  +  ** that the "nBusy" integer parameter passed to the users busy-handler
          176  +  ** represents the total number of busy-handler invocations made within
          177  +  ** this call to sqlite3demo_superlock(), including any made during the
          178  +  ** "BEGIN EXCLUSIVE".
          179  +  */
          180  +  if( rc==SQLITE_OK ){
          181  +    busy.xBusy = xBusy;
          182  +    busy.pBusyArg = pBusyArg;
          183  +    sqlite3_busy_handler(db, superlockBusyHandler, (void *)&busy);
          184  +    rc = sqlite3_exec(db, "BEGIN EXCLUSIVE", 0, 0, 0);
          185  +  }
          186  +
          187  +  /* If the BEGIN EXCLUSIVE was executed successfully and this is a WAL
          188  +  ** database, call superlockWalLock() to obtain the extra locks required
          189  +  ** to prevent readers, writers and/or checkpointers from accessing the
          190  +  ** db while this process is holding the superlock.
          191  +  **
          192  +  ** Before attempting any WAL locks, commit the transaction started above
          193  +  ** to drop the WAL read and write locks currently held. Otherwise, the
          194  +  ** new WAL locks may conflict with the old.
          195  +  */
          196  +  if( rc==SQLITE_OK ){
          197  +    int bWal;                     /* True for a WAL database, false otherwise */
          198  +    if( SQLITE_OK==(rc = superlockIsWal(db, &bWal)) && bWal ){
          199  +      rc = sqlite3_exec(db, "COMMIT", 0, 0, 0);
          200  +      if( rc==SQLITE_OK ){
          201  +        rc = superlockWalLock(db, &busy);
          202  +      }
          203  +    }
          204  +  }
          205  +
          206  +  if( rc!=SQLITE_OK ){
          207  +    sqlite3_close(db);
          208  +    *ppLock = 0;
          209  +  }else{
          210  +    *ppLock = (void *)db;
          211  +  }
          212  +
          213  +  return rc;
          214  +}
          215  +
          216  +/*
          217  +** Release a superlock held on a database file. The argument passed to 
          218  +** this function must have been obtained from a successful call to
          219  +** sqlite3demo_superlock().
          220  +*/
          221  +void sqlite3demo_superunlock(void *pLock){
          222  +  sqlite3_close((sqlite3 *)pLock);
          223  +}
          224  +
          225  +/*
          226  +** End of example code. Everything below here is the test harness.
          227  +**************************************************************************
          228  +**************************************************************************
          229  +*************************************************************************/
          230  +
          231  +
          232  +#ifdef SQLITE_TEST
          233  +
          234  +#include <tcl.h>
          235  +
          236  +struct InterpAndScript {
          237  +  Tcl_Interp *interp;
          238  +  Tcl_Obj *pScript;
          239  +};
          240  +typedef struct InterpAndScript InterpAndScript;
          241  +
          242  +static void superunlock_del(ClientData cd){
          243  +  sqlite3demo_superunlock((void *)cd);
          244  +}
          245  +
          246  +static int superunlock_cmd(
          247  +  ClientData cd,
          248  +  Tcl_Interp *interp,
          249  +  int objc,
          250  +  Tcl_Obj *CONST objv[]
          251  +){
          252  +  if( objc!=1 ){
          253  +    Tcl_WrongNumArgs(interp, 1, objv, "");
          254  +    return TCL_ERROR;
          255  +  }
          256  +  Tcl_DeleteCommand(interp, Tcl_GetString(objv[0]));
          257  +  return TCL_OK;
          258  +}
          259  +
          260  +static int superlock_busy(void *pCtx, int nBusy){
          261  +  InterpAndScript *p = (InterpAndScript *)pCtx;
          262  +  Tcl_Obj *pEval;                 /* Script to evaluate */
          263  +  int iVal = 0;                   /* Value to return */
          264  +
          265  +  pEval = Tcl_DuplicateObj(p->pScript);
          266  +  Tcl_IncrRefCount(pEval);
          267  +  Tcl_ListObjAppendElement(p->interp, pEval, Tcl_NewIntObj(nBusy));
          268  +  Tcl_EvalObjEx(p->interp, pEval, TCL_EVAL_GLOBAL);
          269  +  Tcl_GetIntFromObj(p->interp, Tcl_GetObjResult(p->interp), &iVal);
          270  +  Tcl_DecrRefCount(pEval);
          271  +
          272  +  return iVal;
          273  +}
          274  +
          275  +/*
          276  +** Tclcmd: sqlite3demo_superlock CMDNAME PATH VFS BUSY-HANDLER-SCRIPT
          277  +*/
          278  +static int superlock_cmd(
          279  +  ClientData cd,
          280  +  Tcl_Interp *interp,
          281  +  int objc,
          282  +  Tcl_Obj *CONST objv[]
          283  +){
          284  +  void *pLock;                    /* Lock context */
          285  +  char *zPath;
          286  +  char *zVfs = 0;
          287  +  InterpAndScript busy = {0, 0};
          288  +  int (*xBusy)(void*,int) = 0;    /* Busy handler callback */
          289  +  int rc;                         /* Return code from sqlite3demo_superlock() */
          290  +
          291  +  if( objc<3 || objc>5 ){
          292  +    Tcl_WrongNumArgs(
          293  +        interp, 1, objv, "CMDNAME PATH ?VFS? ?BUSY-HANDLER-SCRIPT?");
          294  +    return TCL_ERROR;
          295  +  }
          296  +
          297  +  zPath = Tcl_GetString(objv[2]);
          298  +
          299  +  if( objc>3 ){
          300  +    zVfs = Tcl_GetString(objv[3]);
          301  +    if( strlen(zVfs)==0 ) zVfs = 0;
          302  +  }
          303  +  if( objc>4 ){
          304  +    busy.interp = interp;
          305  +    busy.pScript = objv[4];
          306  +    xBusy = superlock_busy;
          307  +  }
          308  +
          309  +  rc = sqlite3demo_superlock(zPath, zVfs, xBusy, &busy, &pLock);
          310  +  assert( rc==SQLITE_OK || pLock==0 );
          311  +  assert( rc!=SQLITE_OK || pLock!=0 );
          312  +
          313  +  if( rc!=SQLITE_OK ){
          314  +    Tcl_ResetResult(interp);
          315  +    Tcl_AppendResult(interp, sqlite3ErrStr(rc), 0);
          316  +    return TCL_ERROR;
          317  +  }
          318  +
          319  +  Tcl_CreateObjCommand(
          320  +      interp, Tcl_GetString(objv[1]), superunlock_cmd, pLock, superunlock_del
          321  +  );
          322  +  Tcl_SetObjResult(interp, objv[1]);
          323  +  return TCL_OK;
          324  +}
          325  +
          326  +int SqliteSuperlock_Init(Tcl_Interp *interp){
          327  +  Tcl_CreateObjCommand(interp, "sqlite3demo_superlock", superlock_cmd, 0, 0);
          328  +  return TCL_OK;
          329  +}
          330  +#endif

Added test/superlock.test.

            1  +# 2010 November 19
            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  +
           13  +set testdir [file dirname $argv0]
           14  +source $testdir/tester.tcl
           15  +source $testdir/lock_common.tcl
           16  +
           17  +set testprefix superlock
           18  +
           19  +do_execsql_test 1.1 {
           20  +  CREATE TABLE t1(a, b);
           21  +  INSERT INTO t1 VALUES(1, 2);
           22  +  PRAGMA journal_mode = DELETE;
           23  +} {delete}
           24  +
           25  +do_test 1.2 { sqlite3demo_superlock unlock test.db } {unlock}
           26  +do_catchsql_test 1.3 { SELECT * FROM t1 } {1 {database is locked}}
           27  +do_test 1.4 { unlock } {}
           28  +
           29  +do_execsql_test 2.1 { 
           30  +  INSERT INTO t1 VALUES(3, 4);
           31  +  PRAGMA journal_mode = WAL;
           32  +} {wal}
           33  +
           34  +do_test 2.2 { sqlite3demo_superlock unlock test.db } {unlock}
           35  +do_catchsql_test 2.3 { SELECT * FROM t1 }           {1 {database is locked}}
           36  +do_catchsql_test 2.4 { INSERT INTO t1 VALUES(5, 6)} {1 {database is locked}}
           37  +do_catchsql_test 2.5 { PRAGMA wal_checkpoint }      {1 {database is locked}}
           38  +do_test 2.6 { unlock } {}
           39  +
           40  +do_execsql_test 3.1 { INSERT INTO t1 VALUES(3, 4) } 
           41  +
           42  +do_test 3.2 { sqlite3demo_superlock unlock test.db } {unlock}
           43  +do_catchsql_test 3.3 { SELECT * FROM t1 }           {1 {database is locked}}
           44  +do_catchsql_test 3.4 { INSERT INTO t1 VALUES(5, 6)} {1 {database is locked}}
           45  +do_catchsql_test 3.5 { PRAGMA wal_checkpoint }      {1 {database is locked}}
           46  +do_test 3.6 { unlock } {}
           47  +
           48  +do_execsql_test 4.1 { PRAGMA wal_checkpoint } {}
           49  +
           50  +do_test 4.2 { sqlite3demo_superlock unlock test.db } {unlock}
           51  +do_catchsql_test 4.3 { SELECT * FROM t1 }           {1 {database is locked}}
           52  +do_catchsql_test 4.4 { INSERT INTO t1 VALUES(5, 6)} {1 {database is locked}}
           53  +do_catchsql_test 4.5 { PRAGMA wal_checkpoint }      {1 {database is locked}}
           54  +do_test 4.6 { unlock } {}
           55  +
           56  +do_multiclient_test tn {
           57  +  proc busyhandler {x} {
           58  +    switch -- $x {
           59  +      1 { sql1 "COMMIT" }
           60  +      2 { sql2 "COMMIT" }
           61  +      3 { sql3 "COMMIT" }
           62  +    }
           63  +    lappend ::busylist $x
           64  +    return 1
           65  +  }
           66  +  set ::busylist [list]
           67  +
           68  +  do_test 5.$tn.1 {
           69  +    sql1 {
           70  +      CREATE TABLE t1(a, b);
           71  +      PRAGMA journal_mode = WAL;
           72  +      INSERT INTO t1 VALUES(1, 2);
           73  +    }
           74  +  } {wal}
           75  +
           76  +  do_test 5.$tn.2 {
           77  +    sql1 { BEGIN ; SELECT * FROM t1 }
           78  +    sql2 { BEGIN ; INSERT INTO t1 VALUES(3, 4) }
           79  +    sql3 { BEGIN ; SELECT * FROM t1 }
           80  +  } {1 2}
           81  +
           82  +  do_test 5.$tn.3 {
           83  +    set ::busylist [list]
           84  +    sqlite3demo_superlock unlock test.db "" busyhandler
           85  +    set ::busylist
           86  +  } {0 1 2 3}
           87  +
           88  +  do_test 5.$tn.4 { csql2 { SELECT * FROM t1 } } {1 {database is locked}}
           89  +  do_test 5.$tn.5 { 
           90  +    csql3 { INSERT INTO t1 VALUES(5, 6) } 
           91  +  } {1 {database is locked}}
           92  +  do_test 5.$tn.6 { csql1 "PRAGMA wal_checkpoint" } {1 {database is locked}}
           93  +
           94  +  do_test 5.$tn.7 { unlock } {}
           95  +}
           96  +
           97  +
           98  +finish_test