To write to the database use a single thread with a single DB instance
operating sync'd on a mutex to force exclusive write. Reads can be
anytime, provided they wait on the mutex and set it as a read lock.
You might find that to let writes queue until either an alarm triggers
or the queue hits a trigger (high water) point and then perform the
writes in one transactions will give you maximum productivity and
acceptable read latency. Use prepared SQL statements to remove the
repetition of constantly compiling the same SQL.
When a write is in process on the one writer thread, everything else is
locked out. When one or more reader threads are reading the writer
thread is blocked. Lumping writes into say 50 item transactions or a
transaction every 500 mS would greatly speed performance without
creating noticeable pauses.
You probably need to establish some form of task sentry as a timer or
alarm signal to ensure that a reader or writer cannot hog the resources.
By implementing those classic OS methods you might be happily surprised
at how much throughput you can get from Sqlite.
Gussimulator wrote:
I cannot delay my thread.
Also, if I made a thread per event, then that wouldnt work either, since
every thread would open the database to try a write on it.
We are talking about 100 events in a second. This thread handles the
queries as it gets the events.
----- Original Message ----- From: "Jay Sprenkle" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Wednesday, July 12, 2006 2:57 PM
Subject: Re: [sqlite] Problems with Multi-Threaded Application.
On 7/12/06, Gussimulator <[EMAIL PROTECTED]> wrote:
About the "cant read while writing", how to avoid this?, I cant stop my
system while using the database. There would be no point on having a
database then.
check the return code from operation, if it says Busy, or Locked,
the redo the operation. You may need to retry several times
// here's an example with a 1/2 second delay:
sqlite3_busy_timeout(db, 500);
bool locked = true;
for ( int i = 0; ( i < 10 ) && ( locked ); i++ )
switch ( sqlite3_step() )
{
case SQLITE_BUSY:
case SQLITE_LOCKED:
break;
case SQLITE_ROW:
case SQLITE_DONE:
locked = false;
break;
default:
throw Exception( "Cannot execute sql" );
break;
}
