John, thanks for the reply.
you mean you do the synchronization by your selve rather than let it do sqlite internally ? Marcus John Stanton wrote: > We have been using such a server embadding Sqlite for some time with > success. We actually use pthreads exclusive and read only mutexes for > synchronization since all Sqlite access is threaded in the one process. > JS > > Marcus Grimm wrote: >> 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 > -- Marcus Grimm, MedCom GmbH Darmstadt, Rundeturmstr. 12, 64283 Darmstadt Tel: +49(0)6151-95147-10 Fax: +49(0)6151-95147-20 -------------------------------------------------- MedCom slogans of the month: "Vacation ? -- Every day at MedCom is a paid vacation!" "Friday I have monday in my mind." "MedCom -- Every week a vacation, every day an event, every hour a cliffhanger, every minute a climax." "Damned, it's weekend again!" _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
