Forum: Ruby on Rails Using Sqlite3 Activerecord with multiple clients

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
A35b73b4fa189379f912dcd6818b7e44?d=identicon&s=25 Ed (Guest)
on 2006-02-18 01:59
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
A35b73b4fa189379f912dcd6818b7e44?d=identicon&s=25 Ed (Guest)
on 2006-02-20 06:50
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.
Ad7805c9fcc1f13efc6ed11251a6c4d2?d=identicon&s=25 Alex Young (Guest)
on 2006-02-20 08:58
(Received via mailing list)
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.
B34e109290c7907c5df370eab11c7a4a?d=identicon&s=25 Vikrant Rathore (Guest)
on 2006-02-20 10:53
(Received via mailing list)
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.
Ad7805c9fcc1f13efc6ed11251a6c4d2?d=identicon&s=25 Alex Young (Guest)
on 2006-02-20 10:59
(Received via mailing list)
Vikrant Rathore wrote:
> If you read the latest version 3.3.4 supports multi threaded access.
Threads, yes.  Processes?
This topic is locked and can not be replied to.