Being a newbie to SQLite I've had the same problems working with SQLite
so maybe I can help,
It does not matter how well your database is synchronized, a common
pitfall I had was that I would have a query object with an open cursor
which prevents any other statement from committing to the database.
So for example:
THREAD1 THREAD2
LOCK
QUERY
UNLOCK LOCK
(Step through query) BEGIN TRANSACTION
INSERTS
COMMIT <- SQLite busy error here
UNLOCK
As you can see here that even thought there are Global critical sections
or Mutexes that completely locks on a global level without any other
interferences (external connections)
The query is busy stepping and has an open cursor, so commit or
(spillover) of inserts will fail.
In situations where this can be expected, I fetch all data into memory
inside the lock and reset the query (sqlite3_reset) releases cursor
lock.
Then step through data in memory.
The other solution you may hear is to use BEGIN IMMEDIATE before
performing an operation, this will give any thread an immediate error
when trying to begin the same transaction level, however I think that if
you have separate database connections then they might not know this
until they try to get an exclusive lock on the file for committing.
Solution:
THREAD1 THREAD2
LOCK
QUERY
(Read rows into memory)
SQLite3_reset
UNLOCK LOCK
BEGIN TRANSACTION
INSERTS
COMMIT (no error)
UNLOCK
Hope this helps my implementation is running smoothly but it's not as
concurrent as I would like it to be, but because SQLite is so fast, you
can lock globally get in and out as soon as you can, and you should
still be happy with the speed.
-----Original Message-----
From: Mark Brown [mailto:[EMAIL PROTECTED]
Sent: 14 August 2007 10:25 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] SQLITE_BUSY error in multi-threaded environment
Hi-
I've got an application that has many different SQLite databases. Each
database connection is opened in its own thread. Each database has only
one
connection.
I created some test cases that create a database and schema on the fly
and
perform various SELECT, INSERTS, UPDATES on it. The tests execute while
the
rest of the system is running normally.
What I am seeing is that while I only have one database connection to my
test case database, and my operations on this database are done
sequentially, I have seen at random times a return of SQLITE_BUSY on
either
a prepare or execute of a statement.
On a guess, I decided to stop all other database activity going on in
the
system (db activity on different threads on different databases), and so
far, my test cases pass just fine.
What I was wondering is if there is any chance that database activity
into
SQLite from other db connections could somehow influence my db activity
on
my test database in returning a SQLITE_BUSY error.
I'm using SQLite 3.4.1 with the dotlock mechanism for thread protection
on a
vxWorks custom hardware configuration. With other problems I have had,
they
turned out to be some file i/o method failing due to our custom h/w, so
most
likely this is the problem, but just thought I would ask.
Thanks,
Mark
------------------------------------------------------------------------
-----
To unsubscribe, send email to [EMAIL PROTECTED]
------------------------------------------------------------------------
-----
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
