Re: [sqlite] Threads and locking
Increasing priority does not speed up anything, it just denies time to
lower priority threads. If you use a lock to prevent the background
thread from running while you have foreground activity then you won't
get a busy situation and your foreground thread will run unimpeded.
Having your background thread at a low priority and sharing the database
between threads will give you more BUSY states, not fewer.
You could make a third thread which services SQLite and feed it from a
queue which gives absolute priority to your foreground requests. Then
you cannot have a BUSY and get maximum throughput. I would make the
server thread the same priority as the user ones since it runs
synchronously. The priority of your background thread is unimportant,
and could be the same as the others.
I had a case recently where an ASP couldn't figure out why his server
ran so badly and kept "freezing", despite the fact that he had raised
the priority of the processes to maximum. Of course putting the
priority back down solved his problem and stopped the "freezing". A
high priority process in a busy wait or polling creates an ice age for
everything else.
JS
Barry Paul wrote:
Yes, but I think that will just lead to the same problem. Essentially that
is what SQLite is doing for me already.
What is happening is that the high priority user interface thread is waiting
for the low priority worker thread to complete its transaction. This
effectively is reducing the priority of the user interface which either
times out or becomes sluggish...
In the busy handler can you find out what thread has the lock? If so, I
could probably temporarily increase the priority of the locking thread and
speed up the transaction processing/unlocking...
Thanks again,
--
BP
<< www.planet-hood.com >> Welcome to our world <<
-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED]
Sent: Friday, December 16, 2005 1:07 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Threads and locking
Have you thought of using a lock to synchronise access to the
databaseso that only one thread at a time could change the
database although both could read simultaneously?
JS
Barry Paul wrote:
Hi, I am having some unexpected locking issues with SQLite.
I have a desktop application that uses SQLite. The
application has a
low priority worker thread that is constantly
analyzing/adding/updating/deleting
records in the database. The main application thread mainly
reads from
the database but also does some updating/deleting. Both
threads have
their own SQLite connection.
My problem is that when I do updates in the main
application thread I
quite often fail with a return value of SQLITE_BUSY. I have messed
around with busy_timeouts and busy_handlers without much
success. My
current busy handler (culled either from this list or the web) is:
int busyHandler(void *pArg1, int iPriorCalls) {
// sleep if handler has been called less than
threshold value
if (iPriorCalls < 20)
{
// adding a random value here greatly
reduces locking
if (pArg1 < 0)
Sleep((rand() % 500) + 400);
else Sleep(500);
return 1;
}
// have sqlite3_exec immediately return SQLITE_BUSY
return 0;
}
If I increase the transaction size on the low priority thread I get
more update failures on the main thread.
My schema is fairly simple and my tables contain < 90,000 rows. It
would seem to me that with just two threads and this busy handler I
should never (or very rarely) get SQLITE_BUSY.
My theory is that the main application thread is getting locked out
because it is waiting for the low priority thread to
release the lock
on the database. Meanwhile something else is happing on the
machine at
a higher priority and not letting the low priority thread
back in to
finish the transaction and release the lock.
Does this sound reasonable and is there a good way of dealing with
this situation? Should I try to increase the priority of the
background thread when I get a lock? Or is there some way
to make sure
that transactions in the low priority thread are executed
all at once without interruption?
Thanks for your time,
--
BP
<< www.planet-hood.com >> Welcome to our world <<
RE: [sqlite] Threads and locking
I used to have the same issue. I finally did two things:
1. The background worker thread is at least normal priority. If you own
the DB, you need to get in and get out. I put sleeps in to make sure I
wasn't hitting the DB too often from this thread
2. All connections to the database happen inside a "begin exclusive"
transaction. I'm guaranteed not to hit deadlock this way.
With those two in place, I believe you can let a busy handler spin (ie
keep trying) forever because it is guaranteed to get in sooner or later.
-Original Message-
From: Barry Paul [mailto:[EMAIL PROTECTED]
Sent: Friday, December 16, 2005 2:20 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Threads and locking
Hi, I am having some unexpected locking issues with SQLite.
I have a desktop application that uses SQLite. The application has a low
priority worker thread that is constantly
analyzing/adding/updating/deleting
records in the database. The main application thread mainly reads from
the database but also does some updating/deleting. Both threads have
their own SQLite connection.
My problem is that when I do updates in the main application thread I
quite often fail with a return value of SQLITE_BUSY. I have messed
around with busy_timeouts and busy_handlers without much success. My
current busy handler (culled either from this list or the web) is:
int busyHandler(void *pArg1, int iPriorCalls) {
// sleep if handler has been called less than threshold value
if (iPriorCalls < 20)
{
// adding a random value here greatly reduces locking
if (pArg1 < 0)
Sleep((rand() % 500) + 400);
else Sleep(500);
return 1;
}
// have sqlite3_exec immediately return SQLITE_BUSY
return 0;
}
If I increase the transaction size on the low priority thread I get more
update failures on the main thread.
My schema is fairly simple and my tables contain < 90,000 rows. It would
seem to me that with just two threads and this busy handler I should
never (or very rarely) get SQLITE_BUSY.
My theory is that the main application thread is getting locked out
because it is waiting for the low priority thread to release the lock on
the database. Meanwhile something else is happing on the machine at a
higher priority and not letting the low priority thread back in to
finish the transaction and release the lock.
Does this sound reasonable and is there a good way of dealing with this
situation? Should I try to increase the priority of the background
thread when I get a lock? Or is there some way to make sure that
transactions in the low priority thread are executed all at once without
interruption?
Thanks for your time,
--
BP
<< www.planet-hood.com >> Welcome to our world <<
To find out more about Reuters visit www.about.reuters.com
Any views expressed in this message are those of the individual sender, except
where the sender specifically states them to be the views of Reuters Ltd.
RE: [sqlite] Threads and locking
Yes, but I think that will just lead to the same problem. Essentially that
is what SQLite is doing for me already.
What is happening is that the high priority user interface thread is waiting
for the low priority worker thread to complete its transaction. This
effectively is reducing the priority of the user interface which either
times out or becomes sluggish...
In the busy handler can you find out what thread has the lock? If so, I
could probably temporarily increase the priority of the locking thread and
speed up the transaction processing/unlocking...
Thanks again,
--
BP
<< www.planet-hood.com >> Welcome to our world <<
> -Original Message-
> From: John Stanton [mailto:[EMAIL PROTECTED]
> Sent: Friday, December 16, 2005 1:07 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Threads and locking
>
> Have you thought of using a lock to synchronise access to the
> databaseso that only one thread at a time could change the
> database although both could read simultaneously?
> JS
>
> Barry Paul wrote:
> >
> > Hi, I am having some unexpected locking issues with SQLite.
> >
> > I have a desktop application that uses SQLite. The
> application has a
> > low priority worker thread that is constantly
> > analyzing/adding/updating/deleting
> > records in the database. The main application thread mainly
> reads from
> > the database but also does some updating/deleting. Both
> threads have
> > their own SQLite connection.
> >
> > My problem is that when I do updates in the main
> application thread I
> > quite often fail with a return value of SQLITE_BUSY. I have messed
> > around with busy_timeouts and busy_handlers without much
> success. My
> > current busy handler (culled either from this list or the web) is:
> >
> > int busyHandler(void *pArg1, int iPriorCalls) {
> >
> > // sleep if handler has been called less than
> threshold value
> > if (iPriorCalls < 20)
> > {
> > // adding a random value here greatly
> reduces locking
> > if (pArg1 < 0)
> > Sleep((rand() % 500) + 400);
> > else Sleep(500);
> > return 1;
> > }
> >
> > // have sqlite3_exec immediately return SQLITE_BUSY
> > return 0;
> > }
> >
> > If I increase the transaction size on the low priority thread I get
> > more update failures on the main thread.
> >
> > My schema is fairly simple and my tables contain < 90,000 rows. It
> > would seem to me that with just two threads and this busy handler I
> > should never (or very rarely) get SQLITE_BUSY.
> >
> > My theory is that the main application thread is getting locked out
> > because it is waiting for the low priority thread to
> release the lock
> > on the database. Meanwhile something else is happing on the
> machine at
> > a higher priority and not letting the low priority thread
> back in to
> > finish the transaction and release the lock.
> >
> > Does this sound reasonable and is there a good way of dealing with
> > this situation? Should I try to increase the priority of the
> > background thread when I get a lock? Or is there some way
> to make sure
> > that transactions in the low priority thread are executed
> all at once without interruption?
> >
> > Thanks for your time,
> >
> > --
> > BP
> > << www.planet-hood.com >> Welcome to our world <<
> >
> >
>
>
>
Re: [sqlite] Threads and locking
Have you thought of using a lock to synchronise access to the databaseso
that only one thread at a time could change the database although both
could read simultaneously?
JS
Barry Paul wrote:
Hi, I am having some unexpected locking issues with SQLite.
I have a desktop application that uses SQLite. The application has a low
priority worker thread that is constantly analyzing/adding/updating/deleting
records in the database. The main application thread mainly reads from the
database but also does some updating/deleting. Both threads have their own
SQLite connection.
My problem is that when I do updates in the main application thread I quite
often fail with a return value of SQLITE_BUSY. I have messed around with
busy_timeouts and busy_handlers without much success. My current busy
handler (culled either from this list or the web) is:
int busyHandler(void *pArg1, int iPriorCalls)
{
// sleep if handler has been called less than threshold value
if (iPriorCalls < 20)
{
// adding a random value here greatly reduces locking
if (pArg1 < 0)
Sleep((rand() % 500) + 400);
else Sleep(500);
return 1;
}
// have sqlite3_exec immediately return SQLITE_BUSY
return 0;
}
If I increase the transaction size on the low priority thread I get more
update failures on the main thread.
My schema is fairly simple and my tables contain < 90,000 rows. It would
seem to me that with just two threads and this busy handler I should never
(or very rarely) get SQLITE_BUSY.
My theory is that the main application thread is getting locked out because
it is waiting for the low priority thread to release the lock on the
database. Meanwhile something else is happing on the machine at a higher
priority and not letting the low priority thread back in to finish the
transaction and release the lock.
Does this sound reasonable and is there a good way of dealing with this
situation? Should I try to increase the priority of the background thread
when I get a lock? Or is there some way to make sure that transactions in
the low priority thread are executed all at once without interruption?
Thanks for your time,
--
BP
<< www.planet-hood.com >> Welcome to our world <<
