Hi all,
a few weeks ago I discovered sqlite3 and found it extremely
exiting and I decided to make an attempt to use it as a
replacement for an access-based (via ODBC) database server.
Our server is actually a quite simple application but using
access via ODBC of course may run into serious limitations
due to the non threadsafe nature of the jet driver.
Now, since a few weeks I read a lot concerning the thread issue
within sqlite, I parsed this mailing list and read about
some limitations and recommendations and I came to the conclusion
that for my project it should work. To validate this I started
to integrate sqlit3 into my code and did some tests to see
wether I should go on.
I want to share my findings and hopefully get some comments from
the users because I'm still not really sure if I should go on.
Here is a rough description about the server (Win32) application:
The server can be seen as a main application that listens on a
socket for incoming connections from a client PC. Each running
connection creates a thread inside the main application.
Within the "line-threads" it is required to access the database,
same is true for the main process that frequently queries the
database as well.
First attempt was to open only one DB connection using sqlite3_open_v2
within the main process and having all threads using this single
connection:
This seems to work quite good -- of course it has a major performance
issue when one thread makes an UPDATE statement while another simply
tries to query a table. Although the client connections sleeps most of
the time I want to avoid that in case of a collision the server times out.
In this mode it looks that also only one thread is able to query the database,
if such a query will take 500ms the others are often blocked for this time.
I read that it is recommended to open one DB connection for every thread, so
I tried this and run into the famous SQLITE_BUSY and SQLITE_LOCKED issue which
drives me crasy. anyway, here is the simple structure:
1. server-main opens one connection using sqlite_open_v2, this remains open
for the life time of the server.
2. each time a connect request starts the server opens a new connection using
sqlite_open_v2 and assigns this to the thread, if the connection is closed
the DB connection is closed as well using sqlite3_close.
To test this approach I connect 3 clients to the server and let them do
queries with maximum speed (in order to increase the likelyhood
of a collision). What the clients do during this test are 95% SELECT statements
on a single Table and probably 5% UPDATES on a single table row. The tables
are not very big -- One has about 10000 rows, the other one about 100000.
The first connections runs quite good and queries the database with high speed.
Then I connect a 2nd and 3rd test client: The problem here was that the sqlite
DB
almost immediately runs into the SQLITE_BUSY state, sometimes during a SELECT
fetch cycle sometimes in the UPDATE statement.
Okay, the doku tells that in this case I should try again -- So I added a
loop around all sqlite3_step() that tests for busy state
and retries the sqlite3_step() for some time: No way! - I can wait for minutes
to get a return value other than SQLITE_BUSY, there is a dead lock. Seems
that one thread wait to get his UPDATE statement throuh meanwhile another is
waiting for his SELECT fetch and none of them gets a lock on the database.
I added the busy_timeout: It doesn't really help neither do the busy_handler.
Actually it looks that the busy handler is not even called when a sqlite3_step()
returns SQLITE_BUSY -- This is confirmed by some statements in the sqlite
dokumentation and indicates a deadlock rather than a real busy state.
But why ? Even if all lines wait in a busy loop and sleep none of them gets
back the right
to access the database!
Please note that during this testing the database is busy all the time with
maximum speed that can be achieved -- to find these kind of collision.
Okay, looking deeper into the documents I found some functions to try that may
help
and I finally got it running without the deadlocks -- but I don't really
understand
why and that's where I would like to share this with other:
Seems that a call of
sqlite3_enable_shared_cache( 1 );
after I open the main database connection, does something useful for my case...
After this I got "real" busy states and for about 30 minutes the server runs
with maximum load, after this I got more SQLITE_LOCKED return values and
sooner or later the server stops working. Probably related to a weak error
handling on my side.
Anyway, then I added:
PRAGMA read_uncommitted = true;
because I don't care if a client thread doesn't get the last updated table
content.
With this statement the busy-handler situation improves further.
Now my server can run under heavy load, deals with some busy loops after
sqlite3_step()
returns that state, but finally runs for a couple of hours and I stop the test.
So the final way to open a sqlite database is:
1. On server startup it opens the main database connection with:
sqlite3_open_v2(..., &MainDB, SQLITE_OPEN_READWRITE);
sqlite3_enable_shared_cache( 1 );
sqlite3_busy_timeout(MainDB, 100);
execute "PRAGMA read_uncommitted = true;" on the MainDB
2. A thread connection is opened via:
sqlite3_open_v2(..., &ThreadDB, SQLITE_OPEN_READWRITE);
sqlite3_busy_timeout(ThreadDB, 100);
execute "PRAGMA read_uncommitted = true;" on the ThreadDB
Please note that the linethread may use his DB connection within
additional working threads, so the ThreadID used within the mutex handling
inside sqlite3 may differ from the one that openes the database connection.
I know that this is not recommened but I can't fully avoid this.
However, there is no pararallel usage of a single DB connection.
Now to my main question:
The above way seems to work -- any comments if this is a good way to
use sqlite ?
Why the enable_share cache has an effect on the busy handling inside
the library ? Actually I use this call only to improve the deadlock
situation -- from the memory point of view I have no need to save
memory...
In this way the server may open upto 50 connections on the same
database file... Is there any known issue about this ? I'm wondering
if it is healthy to open a file 50 times...
Maybe the way I test my server is not fair because I use the
maximum traffic that I can produce which is not the normal way the
server will work later on. But the database (or my implementation)
should be able to deal with this, right?
I'm not saying that I'm looking for a "real" server based database
like MySQL! I don't need that and sqlite3 would perfectly fit in. :-)
I think sqlite is a wonderful piece of software -- and I really
want to use it for my project. But I'm a bit nervous because
it turns out to be a bit "try and error" like, for newbies like me.
Any comment is welcome
Thank you!
Marcus
PS: I'm using the latest sqlite3 code on WinXP or Vista
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
