Ken,
thanks for your hint -- I removed the pragma statement, the share
cache switch and even the busytimeout value and then tried the exclusive
transaction encapsulation of my select and update loop.
This seems to do the job -- No deadlocks anymore.
I found one little issue in my implementation that I'm not
sure how to handle:
To get the exclusive transaction I do:
sqlite3_prepare_v2(DB, "BEGIN EXCLUSIVE TRANSACTION;", -1, &stmt, 0);
n = 0;
do
{
rc = sqlite3_step(stmt);
if( rc != SQLITE_DONE )
Sleep(50);
n++;
}while( (rc != SQLITE_DONE) && (n < 10) );
/** Now free the statement **/
while( (rc = sqlite3_finalize(stmt) != SQLITE_OK )
Sleep(50);
I thought the loop around sqlite_finalize is necessary for the
case that I get a SQLITE_BUSY from it.
In case the upper loop runs into the timeout the finalize-loop
will result in a crash in the 2nd try because sqlite3_finalize returns
BUSY but actually it succeded to free the stmt structure.
So the question is:
Is it necessary to test the error code of sqlite3_finalize ?
The docs say that sqlite3_finalize will return the same error
as it was during the previous function in sqlite -- so actually
there is no way to understand if sqlite3_finalize internally failes
or if it just copied the old error code.
I assume sqlite3_finalize will never fail to free the structure, right ?
THanks
Marcus
> Marcus.
>
> Blocking reads during a write is how sqlite operates. Read the sqlite
> locking page:
> http://www.sqlite.org/lockingv3.html
>
> The biggest problem I have with the locking is getting a handle on the
> "spills to disk" portion. So rather than allowing sqlite to escalate the
> locking when it is ready to spill to disk I take control by using a
> transaction (which also improves performance) at the beginning of any
> write operations.
>
> So writes no matter what always block reads.
> Your pseudo code could be considered as a " select for updating " so
> either
> do all of the reading into a temporary table for the select so that it
> is in memory and then perform the update. Or you could wrap the whole
> thing with a begin immediate lock to start the transaction which would
> run a whole lot faster.
>
> BEGIN Immediate
> Prepare "SELECT * FROM table1"
> prepare update statement
> while (step == sqlite_row) {
> read row data
> if (condition) {
> bind variables for update
> Step the update.
> reset the update.
> }
> }
> finalize select statement
> finalize update statement
> Commit;
>
> Neither the select nor updates need any testing for busy since the
> connection already acquired an exclusive lock and a transaction is
> started.
>
> HTH
>
> Ken
>
>
> --- On Fri, 11/7/08, Marcus Grimm <[EMAIL PROTECTED]> wrote:
>
>> From: Marcus Grimm <[EMAIL PROTECTED]>
>> Subject: Re: [sqlite] sqlite3 for server (experience)
>> To: "General Discussion of SQLite Database" <[email protected]>
>> Date: Friday, November 7, 2008, 11:07 AM
>> 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
> _______________________________________________
> 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
