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
