[re-repost after initial e-mail was mangled up with HTML/XML tags and second
was cut short by webmail client. Let's hope yahoo gets it right.]
hi,
Just to let you know. I did some more tests regarding the reload from disk and
skimmed through the source code. Here a quick description of how the reloading
from disk works in a multithreaded process:
(1)
If shared cache is disabled, each call to sqlite3_open() will result in a db
handle with its own btree and page cache. If the page cache is not shared, each
db handle needs to refresh its page cache from disk if some other thread or
process using some other db handle has written to the database. The magic piece
of code is in pager.c: pagerSharedLock().
if( memcmp(pPager->dbFileVers, dbFileVers, sizeof(dbFileVers))!=0 ){
pager_reset(pPager);
}
If the version number read from the file on disk is different than the version
number in memory, reset the pager cache.
(2)
If shared cache is enabled, and it's a disk-based database (not in-memory, not
temporary) each call to sqlite3_open() results in a db handle that points to
the same shared btree and page cache (the relevant code can be seen in the
implementation of sqlite3BtreeOpen() ). This also means that if there's a write
transaction from any of the db handles, the page cache version number is
increased. This eventually leads to the fact the version number on disk and
version number in the page cache are the same. I.e. no reload happens.
Hope that helps a bit.
Markus
PS: Below some sample code I used. Simply compile and run with "sampleapp
db-file". Note that it deletes the file first if it exists. Run the
application with e.g. "strace -f" (Linux distro) to see system calls. The
application creates two db handles to the same db (one in the main thread and
one in a newly started thread). The first one only writes and the second one
only reads.
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
#include <pthread.h>
#include <unistd.h>
/*
* Sample code. No guarantees for correctness
*/
/*
* some global variables that are quick'n'dirty but do the job
*/
char** argv_global;
/*
* A callback function needed for sqlite3_exec. Copied from sample code.
*/
static int callback(void *NotUsed, int argc, char **argv, char **azColName){
int i;
for(i=0; i<argc; i++){
printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
return 0;
}
/*
* A function that serves as thread entry point
*/
void * thread_body(void * x)
{
char *zErrMsg = 0;
int rc;
int status;
sqlite3 *db_thread;
printf ("%d: entered into thread\n", pthread_self());
//
// open DB
//
rc = sqlite3_open(argv_global[1], &db_thread);
if( rc ){
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db_thread));
sqlite3_close(db_thread);
exit(1);
}
//
// double check that table1 is there. This is executed still in autocommit
mode
//
rc = sqlite3_exec(db_thread, "select count(*) from table1", callback, 0,
&zErrMsg);
if( rc!=SQLITE_OK ){
fprintf(stderr, "BEGIN TRANSACTION: SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}
while (1)
{
printf ("\n%d: reader to sleep before start transactions\n",pthread_self());
sleep(6);
printf("\n%d: reader woke up and starts transaction\n",pthread_self());
//
// Begin transaction
//
rc = sqlite3_exec(db_thread, "BEGIN", callback, 0, &zErrMsg);
if( rc!=SQLITE_OK ){
fprintf(stderr, "BEGIN TRANSACTION: SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}
//
// execute statement
//
rc = sqlite3_exec(db_thread, "select * from table1", callback, 0, &zErrMsg);
if( rc!=SQLITE_OK ){
fprintf(stderr, "Statement: SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}
//
// Commit transaction
//
rc = sqlite3_exec(db_thread, "COMMIT", callback, 0, &zErrMsg);
if( rc!=SQLITE_OK ){
fprintf(stderr, "BEGIN TRANSACTION: SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}
printf ("\n%d: reader after commit\n", pthread_self());
}
return (void*)0;
}
int main(int argc, char **argv){
char *zErrMsg = 0;
int rc;
int status;
sqlite3 *db_main;
pthread_t t1;
int i;
char* buffer=(char*)malloc(100);
argv_global = argv;
rc=0;
if( argc!=2 ){
fprintf(stderr, "Usage: %s db-file \n", argv[0]);
exit(1);
}
printf("%d: is threasafe sqlite library: %d\n",pthread_self(),
sqlite3_threadsafe() );
printf("%d: deleting old DB file", pthread_self());
unlink(argv[1]);
// 0 = turn off shared cache (false) // seems to be the default
// 1 = turn on shared cache (true)
rc = sqlite3_enable_shared_cache(0);
if( rc ){
fprintf(stderr, "Unable to open db in shared cache mode: %s\n", \
sqlite3_errmsg(db_main));
exit(1);
}
//
// open DB
//
rc = sqlite3_open_v2(argv[1],
&db_main,
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE,
NULL);
if( rc ){
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db_main));
sqlite3_close(db_main);
exit(1);
}
//
// create table
//
rc = sqlite3_exec(db_main, "create table table1 (int i)", callback, 0,
&zErrMsg);
if( rc!=SQLITE_OK ){
fprintf(stderr, "Could not create table: SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}
//
// start new thread
//
status = pthread_create(&t1, (pthread_attr_t*)0, thread_body, (void*)0);
if (0 != status ) printf ("Could not start thread\n");
i=0;
while (1){
printf("\n%d: starting write transaction\n", pthread_self());
//
// Begin transaction
// normal BEGIN DEFERRED, also try BEGIN IMMEDIATE and BEGIN EXCLUSIVE
rc = sqlite3_exec(db_main, "BEGIN", callback, 0, &zErrMsg);
if( rc!=SQLITE_OK ){
fprintf(stderr, "BEGIN TRANSACTION: SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}
//
// some insert/delete/update/select statements
//
sprintf(buffer, "insert into table1 values (%d)", i);
printf ("%s", buffer);
rc = sqlite3_exec(db_main, buffer, callback, 0, &zErrMsg);
if( rc!=SQLITE_OK ){
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}
printf ("\n%d: writer going to sleep before commit\n", pthread_self());
sleep(3);
printf("\n%d: writer going to commit\n", pthread_self());
rc=5;
//
// Commit transaction ("COMMIT" or "ROLLBACK")
//
while (rc==5){ //not sure what the constant should be here (5=???)
rc = sqlite3_exec(db_main, "COMMIT" , callback, 0, &zErrMsg);
printf ("\n%d: commit returned with %d\n", pthread_self(), rc);
sleep (1);
}
if( rc!=SQLITE_OK ){
fprintf(stderr, "COMMIT TRANSACTION: SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}
printf("\n%d: inserted value (after commit): %d\n",pthread_self(), i);
i++;
} //while
sqlite3_close(db_main);
return 0;
}
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
