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 -- Posted via http://www.ruby-forum.com/.
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, > edCan anyone please help? Thanks. -- Posted via http://www.ruby-forum.com/.
Alex Young
2006-Feb-20 07:57 UTC
[Rails] Re: Using Sqlite3 Activerecord with multiple clients
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. -- Alex
Vikrant Rathore
2006-Feb-20 09:52 UTC
[Rails] Re: Using Sqlite3 Activerecord with multiple clients
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.
Alex Young wrote:> 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.
>