# Using Sqlite3 Activerecord with multiple clients **URL:** \ **Category:** Rails\ **Created:** [February 18, 2006, 1:59am UTC](https://www.ruby-forum.com/t/using-sqlite3-activerecord-with-multiple-clients/53952 "2006-02-18T01:59:19Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\ **Post date:** [February 18, 2006, 1:59am UTC](https://www.ruby-forum.com/t/using-sqlite3-activerecord-with-multiple-clients/53952/1 "2006-02-18T01:59:19Z")
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 ---
**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\ **Post date:** [February 20, 2006, 6:50am UTC](https://www.ruby-forum.com/t/using-sqlite3-activerecord-with-multiple-clients/53952/2 "2006-02-20T06:50:04Z")
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. ---
**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\ **Post date:** [February 20, 2006, 8:58am UTC](https://www.ruby-forum.com/t/using-sqlite3-activerecord-with-multiple-clients/53952/3 "2006-02-20T08:58:59Z")
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](http://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. ---
**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\ **Post date:** [February 20, 2006, 10:53am UTC](https://www.ruby-forum.com/t/using-sqlite3-activerecord-with-multiple-clients/53952/4 "2006-02-20T10:53:46Z")
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. ---
**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\ **Post date:** [February 20, 2006, 10:59am UTC](https://www.ruby-forum.com/t/using-sqlite3-activerecord-with-multiple-clients/53952/5 "2006-02-20T10:59:54Z")
Vikrant Rathore wrote: > If you read the latest version 3.3.4 supports multi threaded access. > Threads, yes. Processes?