[database-devel] Multi-threaded access to an SQLite database

[Leon Smith]

Err, that's kind of a silly idea on my part;  just using an MVar to
serialize everything is probably easier and more efficient.

Leon

On Fri, Jan 4, 2013 at 6:10 PM, Leon Smith <leon.p.smith at gmail.com> wrote:
> As quick and easy workaround,  you could use a single haskell thread
> to talk to SQLite,  and then snaplet-sqlite-simple code would then
> talk to this thread instead of issuing sqlite-simple calls directly.
> I've done the same thing to interface with zeromq,  using my
> split-channel library.
>
> Leon
>
> On Fri, Jan 4, 2013 at 5:54 PM, Janne Hellsten <jjhellst at gmail.com> wrote:
>> Moving the discussion over to database-devel - others may find this
>> interesting / may have run into this.
>>
>> FWIW - I also tried setting up a test case which would have multiple
>> concurrent writers, but couldn't get this to fail.  I suspect though
>> that my test case wasn't good enough to hit this problem.
>>
>> Background: This originated from [1], also collected some notes on [2].
>>
>>   [1]: https://github.com/nurpax/snaplet-sqlite-simple/issues/5
>>   [2]: here: http://nurpax.github.com/posts/2013-01-04-note-to-self.html
>>
>> Janne
>>
>>
>> On Sat, Jan 5, 2013 at 12:38 AM, Joey Adams <joeyadams3.14159 at gmail.com> wrote:
>>> On Fri, Jan 4, 2013 at 4:43 PM, Janne Hellsten <jjhellst at gmail.com> wrote:
>>>>
>>>> I was just wondering if you have any past experience on multi-threaded
>>>> use of SQLite?  I hit some issues last week, started seeing
>>>> SQLITE_BUSY on inserts and wasn't quite sure what to think about it.
>>>> I wrote some notes about it here
>>>> http://nurpax.github.com/posts/2013-01-04-note-to-self.html.  Have you
>>>> by any chance had to deal with such problems before?
>>>
>>>
>>> According to the SQLite FAQ [1], only one writer can access the database at
>>> a time.  I haven't run into this myself, though.
>>>
>>> Be careful with transactions.  If you allow multiple threads to access a
>>> database handle, and to issue BEGIN/COMMIT on their own terms, then one
>>> thread might issue BEGIN and interfere with other transactions.  Thus, make
>>> sure the MVar lock is held for the duration of the transaction.
>>>
>>> PS: Mind CCing this to database-devel at haskell.org ?
>>>
>>>  [1]: http://www.sqlite.org/faq.html#q5
>>
>> _______________________________________________
>> database-devel mailing list
>> database-devel at haskell.org
>> http://www.haskell.org/mailman/listinfo/database-devel