Ken wrote:
> Marcus,
>
Ken,
thanks for this.
> I'm not using a "SERVER" but I do run a threaded application.
might be the same, anyhow.
> I'd suggest removing the cache and pragma for now.
why? Do you see a problem with this ?
(I did use them as a result of try and error and I'm not sure if the have
a negative side effect)
>
> Here is what I do in my app to handle the "locking".
> Each thread creates it's own db connection.
Ok. I do the same.
> 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.
>
ok
> 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.
Are you sure that you will ever see a SQLITE_BUSY from a sqlite3_prepare_v2
call ? I never did, as far as I can remeber.
> 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!
So basically you block all write (and thus parallel read) operation in a global
way and
allow read operations in parallel, right ? That would be okay since writing to
the
database occurs rather seldom.
But, I'm not sure if I can do this -- my current implementation for example
sometimes
does something like this:
sqlite3_prepare_v2(DB, "SELECT * FROM table1", -1, &stmt1, 0);
while(sqlite3_step(stmt1) = SQLITE_ROW)
{
read_row_data();
/** Do an update in table2 based on data in table1 **/
if( certain_condition_in_table1() )
{
sqlite3_prepare_v2(DB, "UPDATE Table2 SET V1 = 42 WHERE ID = 1234", -1,
&stmt2, 0);
sqlite3_step(stmt2)
sqlite3_finalize(stmt2);
break;
}
}
sqlite3_finalize(stmt1);
(Of course in above pseudocode I skipped all error and busy handling!)
With your suggestion, the inner statement would need an exclusive transaction,
I'm not sure -- but I guess it will result in an deadlock because the outer
loop is still running.
The above is probably bad database style but that how it is implemented right
now and I would of course like to avoid to much changes... ;)
Thank for the hint, I'll make a quick try and let you know.
Marcus
>
> 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
>
--
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
