[elephant-devel] Sqlite-3 backend, threads and transactions

[Robert L. Read]

Dear Ignas,

    Thanks for this excellent and informative problem report.  Since I
do run sqlite3 and 
SBCL here at home, I should be able to use your test code to reproduce
the problem.
I will try to do this tonight and tomorrow morning; I doubt I willl have
much information
for your before tomorrow at noon.

    Looking at the stack trace you provide, it seems that this is a
SQLite error, possibly
cause by Elephant, or even CLSQL, not handling transactions properly.
Since I have 
only minimal knowledge of SQLite, I can't say anything else yet.

    Tonight I will run your test code against a postgres database as
well; this may not
solve your problem but will give us some additional information.

    I am a little surprised by your use of clsql:start-transaction; the
normal idiom for
this is:

       (with-transaction (:store-controller *store-controller*)
         (loop for obj in objs
               for key in keys
               do (setf (get-value key bt) obj))))
  (this is an example for testcollections.lisp)

Are you saying in your email that you could not use this way of doing
things?  The 
with-transaction macro should be indpendent of the type of the store-
controller.

You wrote:
As sqlite requires each thread to have it's own connection by throwing
an error if one thread tries to reuse a connection created by another
thread at least on Ubuntu Dapper. Google says that it depends on a
compile time flag for libsqlite3.

The current Elephant code has no notion of this, and I'm pretty sure it
will hand the same connection
to several threads.  Obviously, you could solve this problem by using
mutexes to make sure that
only one thread accesses the controller, but that might be quite
unpleasant for you code.

If these comments can help you provide anymore information about what
your possible solutions
are, please reply so I can think about it before I begin debugging it
tonight or tomorrow.

On Wed, 2006-04-19 at 18:34 +0300, Ignas Mikalajunas wrote:

>     Hi, I am trying to use elephant as a backend data store for my
> blogging application. And have encountered some problems with it ...
> As i want to only commit the transaction in cases of error and tbnl is
> using throw for redirects i need to manualy open/close transactions,
> but it seems (ele:start-transaction) is only designed to work with BDB
> backend, so i must use (clsql:start-transaction) instead.
> 
> At the moment the wrapper around each request looks like this:
> 
> (defun common-blog-view-wrapper (function)
>   (ele:with-open-store (`(:sqlite3 ,(format nil "~A"
> (get-config-option "database-path"))))
>     (let ((error nil)
>           (transaction nil))
>       (unwind-protect
>            (handler-bind ((error (lambda (cond)
>                                    (declare (ignore cond))
>                                    (setf error t))))
>              (clsql:start-transaction)
>              (setf transaction t)
>              (tbnl::string-to-octets (funcall function) :utf-8))
>         (when transaction
>           (if error
>               (clsql:rollback)
>               (clsql:commit)))))))
> 
> As sqlite requires each thread to have it's own connection by throwing
> an error if one thread tries to reuse a connection created by another
> thread at least on Ubuntu Dapper. Google says that it depends on a
> compile time flag for libsqlite3. Everything works more or less fine ,
> but when i am reloading the page multiple times in a row very fast i
> am getting a traceback (attached). And can't quite grok what went
> wrong ... Maybe some one who knows internals of elephant can help me
> out with this, as i could not really track where could a database
> connection appear in another thread.
> 
> A testcase for SBCL:
> 
> (defun test ()
>   (ele:with-open-store ('(:sqlite3 "/home/ignas/src/common-blog/dev.db"))))
> 
> (sb-thread:make-thread #'test) ;; a few times - works
> 
> (dotimes (i 3) (sb-thread:make-thread #'test)) ;; you get 3 tracebacks
> 
> By the way thanks for a wonderful library!
> 
> --
> Ignas Mikalajūnas
> _______________________________________________
> elephant-devel site list
> elephant-devel at common-lisp.net
> http://common-lisp.net/mailman/listinfo/elephant-devel

----
Robert L. Read, PhD                                     read &T
robertlread.net
Consider visiting Progressive Engineering:
http://robertlread.net/pe
In Austin: 912-8593                                        "Think
globally, Act locally." -- RBF


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mailman.common-lisp.net/pipermail/elephant-devel/attachments/20060419/a35c6d01/attachment.html>