hi,
Despite the threads-are-evil claim...
What happens with respect to the page cache if I have an application (= one
multi-threaded process) opening two DB handles (sqlite3_open()) - one DB handle
dedicated to one thread. Do multilpe db handles in the same process cause
sqlite to reload the whole data set from disk after another db handle in the
same process has written to the database.
Note: Semantically, I always translate DB handle in the context of sqlite to
"connection". However, let's use the term DB handle to make
things maybe a little less confusing. I hope I stick to db handle for the rest
of the e-mail. Also assume "standard" Posix threads (pthreads)
semantics under Unix/Linux.
thread t1 -> db_handle1
thread t2 -> db_handle2
Now I start a "normal write transaction" (BEGIN, not BEGIN IMMEDIATE,
not BEGIN EXCLUSIVE) on the db_handle1:
thread 1{
sqlite3_open ("/mnt/ext3/mydatabase", &dbhandle1)
sqlite3_exec (dbhandle1, "BEGIN", ..)
sqlite3_exec (dbhandle1, "insert into table1 values (1)", ..)
sqlite3_exec (dbhandle1, "COMMIT", ..)
}
and read transactions in thread 2
thread 2{
sqlite3_open ("/mnt/ext3/mydatabase", &dbhandle2)
sqlite3_exec (dbhandle2, "BEGIN", ..)
sqlite3_exec (dbhandle2, "select * from table1", ..)
sqlite3_exec (dbhandle2, "COMMIT", ..)
}
and they get eventually executed like this:
1: t1: sqlite3_open ("/mnt/ext3/mydatabase", &dbhandle1)
2: t2: sqlite3_open ("/mnt/ext3/mydatabase", &dbhandle2)
3: t2: sqlite3_exec (dbhandle2, "BEGIN", ..)
4: t2: sqlite3_exec (dbhandle2, "select * from table1", ..)
5: t2: sqlite3_exec (dbhandle2, "COMMIT", ..)
6: t1: sqlite3_exec (dbhandle1, "BEGIN", ..)
7: t1: sqlite3_exec (dbhandle1, "insert into table1 values
(1)", ..)
8: t1: sqlite3_exec (dbhandle1, "COMMIT", ..)
9: t2: sqlite3_exec (dbhandle2, "BEGIN", ..)
10: t2: sqlite3_exec (dbhandle2, "select * from table1", ..)
11: t2: sqlite3_exec (dbhandle2, "COMMIT", ..)
My understanding of the documentation regarding the locking protocol
(http://www.sqlite.org/lockingv3.html) is that threads are not treated
differently than processes. Which would mean that eventhough both threads could
have access to same page cache (in the same process) they don't use it.
However when running the test as described below against sqlite 3.5.9
(amalgamation), noweher between step 8 and 11 does it do any file access (not
even fcntl()s). Which makes me suspect that thread1's db_handle1 and
thread2's db_handle2 use the same page cache as thread2 clearly sees the
changes done in steps 6-8.
So the question is: is the page cache actually shared even with an explicit
sqlite3_enable_shared_cache(0) before any other sqlite3 operation? Naturally
with sqlite3_enable_shared_cache(1) sqlite3 goes into table-locking mode which
is not want I'm loooking for. My bigger concern here is the reloading of
the page cache from disk (or OS's buffer cache) after write transactions in
the same process. Do read transactions on different db handles in the same
process context cause a reload of the page cache?
I tried to dig through the source code, but that's, eventhough nicely
documented, still to confusing to easily follow up what's going on. Could
you point out some key points in the source code to help in understanding
what's going on.
Markus
PS: Below some sample code. Simply compile and run with "sampleapp
db-file". Note that it deletes the file sfirst if it exists. Run the
application with e.g. "strace" 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 write and the second one reads only.
#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;
}
<img src="http://www.bigstring.com/refer.php?img=60"; width="1"
height="1">BigString.com, a smarter way to email. Signup for a free email
account today.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
