Marcus, I'm not using a "SERVER" but I do run a threaded application. I'd suggest removing the cache and pragma for now.
Here is what I do in my app to handle the "locking". Each thread creates it's own db connection. Then for any update/insert/delete type of transaction where sqlite will escalate to an "EXCLUSIVE" lock I wrap this with a "BEGIN IMMEDIATE" the begin immediate contains a loop that simply retries on if SQLITE_BUSY OR SQLITE_LOCKED is returned. Then where select statement are begun, I test for sqlite_busy and sqlite_locked on the prepare if busy/locked then close the statement and retry. Also test on the first call to sqlite_step if busy, retry the step. After the first call is successful you should not need to retry. Try setting up your server like that if you can and see if it helps. Also after the "updates" complete you'll need to "commit"... This releases the exclusive lock on the database allowing the selects to run. Once that happens the selects can/should all be able to run concurrently! HTH, Ken --- On Fri, 11/7/08, Marcus Grimm <[EMAIL PROTECTED]> wrote: > From: Marcus Grimm <[EMAIL PROTECTED]> > Subject: [sqlite] sqlite3 for server (experience) > To: [email protected] > Date: Friday, November 7, 2008, 6:47 AM > 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 _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
