[yunqa.de] Re: Thread Safety of Disqlite 3 Pro

  • From: Rémi MEVAERE <remi.mevaere@xxxxxxxxx>
  • To: yunqa@xxxxxxxxxxxxx
  • Date: Sun, 1 Apr 2012 13:14:28 +0200

Hi,

I have found some documentation about SQLite, I think it will answer all my
questions. Could you just confirm ?

*Can multiple applications or multiple instances of the same application
access a single database file at the same time?*

Multiple processes can have the same database open at the same time.
Multiple processes can be doing a SELECT at the same time. But only one
process can be making changes to the database at any moment in time,
however.

SQLite uses reader/writer locks to control access to the database. (Under
Win95/98/ME which lacks support for reader/writer locks, a probabilistic
simulation is used instead.) But use caution: this locking mechanism might
not work correctly if the database file is kept on an NFS filesystem. This
is because fcntl() file locking is broken on many NFS implementations. You
should avoid putting SQLite database files on NFS if multiple processes
might try to access the file at the same time. On Windows, Microsoft's
documentation says that locking may not work under FAT filesystems if you
are not running the Share.exe daemon. People who have a lot of experience
with Windows tell me that file locking of network files is very buggy and
is not dependable. If what they say is true, sharing an SQLite database
between two or more Windows machines might cause unexpected problems.

We are aware of no other *embedded* SQL database engine that supports as
much concurrency as SQLite. SQLite allows multiple processes to have the
database file open at once, and for multiple processes to read the database
at once. When any process wants to write, it must lock the entire database
file for the duration of its update. But that normally only takes a few
milliseconds. Other processes just wait on the writer to finish then
continue about their business. Other embedded SQL database engines
typically only allow a single process to connect to the database at once.

However, client/server database engines (such as PostgreSQL, MySQL, or
Oracle) usually support a higher level of concurrency and allow multiple
processes to be writing to the same database at the same time. This is
possible in a client/server database because there is always a single
well-controlled server process available to coordinate access. If your
application has a need for a lot of concurrency, then you should consider
using a client/server database. But experience suggests that most
applications need much less concurrency than their designers imagine.

When SQLite tries to access a file that is locked by another process, the
default behavior is to return SQLITE_BUSY. You can adjust this behavior
from C code using the
sqlite3_busy_handler()<http://www.sqlite.org/c3ref/busy_handler.html>
 orsqlite3_busy_timeout() <http://www.sqlite.org/c3ref/busy_timeout.html> API
functions.

* Is SQLite threadsafe?*

Threads are 
evil<http://www.eecs.berkeley.edu/Pubs/TechRpts/2006/EECS-2006-1.pdf>.
Avoid them.

SQLite is threadsafe. We make this concession since many users choose to
ignore the advice given in the previous paragraph. But in order to be
thread-safe, SQLite must be compiled with the SQLITE_THREADSAFE
preprocessor macro set to 1. Both the Windows and Linux precompiled
binaries in the distribution are compiled this way. If you are unsure if
the SQLite library you are linking against is compiled to be threadsafe you
can call the sqlite3_threadsafe()<http://www.sqlite.org/c3ref/threadsafe.html>
interface
to find out.

Prior to version 3.3.1 <http://www.sqlite.org/releaselog/3_3_1.html>, an *
sqlite3* structure could only be used in the same thread that called
sqlite3_open() <http://www.sqlite.org/c3ref/open.html> to create it. You
could not open a database in one thread then pass the handle off to another
thread for it to use. This was due to limitations (bugs?) in many common
threading implementations such as on RedHat9. Specifically, an fcntl() lock
created by one thread cannot be removed or modified by a different thread
on the troublesome systems. And since SQLite uses fcntl() locks heavily for
concurrency control, serious problems arose if you start moving database
connections across threads.

The restriction on moving database connections across threads was relaxed
somewhat in version 3.3.1 <http://www.sqlite.org/releaselog/3_3_1.html>.
With that and subsequent versions, it is safe to move a connection handle
across threads as long as the connection is not holding any fcntl() locks.
You can safely assume that no locks are being held if no transaction is
pending and all statements <http://www.sqlite.org/c3ref/stmt.html> have
been finalized <http://www.sqlite.org/c3ref/finalize.html>.

Under Unix, you should not carry an open SQLite database across a fork()
system call into the child process. Problems will result if you do.


Kind regards,
;)

2012/3/31 Rémi MEVAERE <remi.mevaere@xxxxx>

> Hello,
>
> I would like to have information about Thread Safety in Disqlite.
> I searched in Help and I see that Disqlite 3 is thread Safety
> (sqlite3_threadsafe() return 1).
>
> The problem is if I have 2 Applications with ten threads each one. Could I
> Open, Prepare, Check on the SAME database without problem and without
> precaution ?
>
> what is the goal of the DISQLite3_Busy_Timeout Demo ? It strange, i was
> conviced that it was a workaround to have a thread safe access to the
> database.
>
> Thanks a lot,
>
> Kind regards,
> ;)
>
>


-- 
MEVAERE Rémi
Phone : +33 665601787

Other related posts: