Hi,
Here is my problem. I have sqlite3 as the db backend for rails. In
addition to Rails, I also have another process that is writing to the db
file so that rails can read from the same db file and get updates. The
rails is responsible mostly for reading (and displaying) but also it
occasionally make modifications to it.
However it seems that the activerecord adapter for sqlite3 is locking
the db file. In other words when I try to access the db file, I kept on
getting “database is locked” error.
Am I wrong in choosing sqlite3 as the db backend? Also how do I make
the activerecord to only lock the file when it write to the file?
Thanks,
ed
Ed wrote:
Hi,
Here is my problem. I have sqlite3 as the db backend for rails. In
addition to Rails, I also have another process that is writing to the db
file so that rails can read from the same db file and get updates. The
rails is responsible mostly for reading (and displaying) but also it
occasionally make modifications to it.
However it seems that the activerecord adapter for sqlite3 is locking
the db file. In other words when I try to access the db file, I kept on
getting “database is locked” error.
Am I wrong in choosing sqlite3 as the db backend? Also how do I make
the activerecord to only lock the file when it write to the file?
Thanks,
ed
Can anyone please help?
Thanks.
Ed wrote:
Ed wrote:
However it seems that the activerecord adapter for sqlite3 is
locking the db file. In other words when I try to access the db
file, I kept on getting “database is locked” error.
That’s not surprising. SQLIte3 is designed for single-process access.
From sqlite.org:
SQLite uses reader/writer locks on the entire database file. That
means if any process is reading from any part of the database, all
other processes are prevented from writing any other part of the
database. Similarly, if any one process is writing to the database,
all other processes are prevented from reading any other part of the
database. For many situations, this is not a problem. Each
application does its database work quickly and moves on, and no lock
lasts for more than a few dozen milliseconds. But there are some
applications that require more concurrency, and those applications
may need to seek a different solution.
Am I wrong in choosing sqlite3 as the db backend?
In a word, yes. If you want more than one point of access to the
database, you either want a different database engine, or to force all
access through Rails.
Also how do I make
the activerecord to only lock the file when it write to the file?
I believe it does. However, reading also causes a lock on the entire
database.
You can still use sqlite if you can arrange your other process to use
Rails to make the writes.
If you read the latest version 3.3.4 supports multi threaded access.
2006 February 11 (3.3.4)
* Fix a blunder in the Unix mutex implementation that can lead
to deadlock on multithreaded systems.
* Fix an alignment problem on 64-bit machines
* Added the fullfsync pragma.
* Fix an optimizer bug that could have caused some unusual LEFT
OUTER JOINs to give incorrect results.
* The SUM function detects integer overflow and converts to
accumulating an approximate result using floating point numbers
* Host parameter names can begin with ‘@’ for compatibility
with SQL Server.
* Other miscellaneous bug fixes
2006 January 31 (3.3.3)
* Removed support for an ON CONFLICT clause on CREATE INDEX -
it never worked correctly so this should not present any backward
compatibility problems.
* Authorizer callback now notified of ALTER TABLE ADD COLUMN
commands
* After any changes to the TEMP database schema, all prepared
statements are invalidated and must be recreated using a new call to
sqlite3_prepare()
* Other minor bug fixes in preparation for the first stable
release of version 3.3
2006 January 24 (3.3.2 beta)
* Bug fixes and speed improvements. Improved test coverage.
* Changes to the OS-layer interface: mutexes must now be
recursive.
* Discontinue the use of thread-specific data for out-of-memory
exception handling
2006 January 16 (3.3.1 alpha)
* Countless bug fixes
* Speed improvements
* Database connections can now be used by multiple threads, not
just the thread in which they were created.