In our particular application we use Sqlite embedded in a multi-threaded
application server. The databases are only accessed by that process.
We compile Sqlite without the fcntl file locking and place a pthread
read/write lock around accesses to the connection for each database.
That way we have no busies and never poll the connection for busy. The
calling thread blocks on the rwlock.
This method is not perfect because it loses some concurrency compared to
the Sqlite use of pending and reserved locks but for our purposes is
more efficient and less complex. Resolving busy contentions with
minumum latency with some form of busy wait is something to be avoided
if possible in our way of thinking. Using delays blows out the latency.
Using a single Sqlite connection for each database and holding the
connection open means that maximum effect is obtained from Sqlite's
cache. As far as we can ascertain avoiding fcntl removes any issues
regarding multiple threads accessing a single connection.
Benchmarks show an ability to handle over 1,000 concurrent application
users on a minimal server (a test machine recycled from a dumpster with
a 400MHz AMD processor, 384K of memory and a 5,400 rpm IDE disk).
Note that the application is designed to suit Sqlite and have short
transactions. For a different application we would not use Sqlite but
deploy PostgreSQL.
Larry Lewis wrote:
To summarize, if compiling with the THREADSAFE macro set to 1 and sharing a
memory database connection between multiple threads, SQLite will handle all
read/write synchronization without providing any external locking. Is that
basically what you're saying, John?
Are there still cases where an SQLITE_BUSY will be returned due to potential deadlocks as
mentioned by Igor (for example, thread 1 is reading, thread 2 is waiting for write,
thread 1 tries to upgrade to write without relinquishing read)? I'm confused by the
statement "ignore busy logic."
Thanks for your help.
Larry
----- Original Message ----
We implement pthread read/write locks around Sqlite in a multi-threaded
environment and disable the fcntl file locking and ignore busy logic.
It has the downside of losing some concurrency compared to the Sqlite
pending and reserved lock strategy, but we have not suffered a
performance hit yet.
Using a mutex alone restricts concurrent reading, not a good idea.
Larry Lewis wrote:
Igor,
Thanks for your help. I've tested the first case successfully.
For a multi-threaded application using an in-memory database (":memory:"),
would you recommend:
a) an external mutex to synchronize exclusive access to the database --
probably the safest
b) an external read-write lock to allow concurrent reads but only one write
(parallel of SQLite locking as I understand it)
c) rely on SQLite locking and handle SQLITE_BUSY cases as described below
Both SELECTs and UPDATEs will be occurring from multiple threads approximately
10 times/second, and my original question below will be quite common (SELECT a
group of records and make UPDATEs as I step through them).
Larry
----- Original Message ----
Larry Lewis <lewislp-/[EMAIL PROTECTED]> wrote:
If I am stepping through the results of a SELECT and want to UPDATE
values in the currently selected row prior to completion of the
SELECT query, will this work?
Yes, in the recent enough SQLite version.
What if there is already a pending writer lock on the database from a
different thread?
SQLite will detect the deadlock, and fail your UPDATE statement with
SQLITE_BUSY error. Your only option at this point would be to reset the
SELECT statement and finish the transaction this query was part of (if
any).
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
