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

[Robert L. Read]

Dear Ignas,
    Forgive me being a little confused; I had to get my mind back into
this after some time off.
    Basically, would should not open multiple store controllers in
different threads onto the same database.
Onto different databases, this would be acceptable; onto the same
database, you are definitely creating
the danger of inconsistent state.  The code below the pattern I use.

(open-store *testbdb-spec*)
(defun test ()
  (format t "store-controller ~A~%" *store-controller*)
  (with-transaction (:store-controller *store-controller*)
    (ele::add-to-root "x" (gensym))
    (format t "Here's one try~A!~%" (ele::get-from-root "x"))
    ))

(sb-thread:make-thread #'test) 

(dotimes (i 5) (sb-thread:make-thread #'test)) 

At first it might seem quite objectionably to lave the store-controller
object open for a very long duration---but
I have done for days and days without a problem.  So I am basically
recommending one store controller.  Operations
in multiple threads against one store controller should be threadsafe; I
don't think this has really been tested much
but that was the plan of the original authors, I think.  It is possible
that the extensive changes Ian and I have made
in the last six months have messed that up.

The other problem, of course, is the potential SQLite3 problem you
mention.  This may keep the above 
code from working on your installation (please try it for me and report
the result.)

In that case, I would much rather address the SQLite3-specific problem
than workaround things in 
a very complicated way.

I could not open the url to trouble ticket that you posted.

If it is a question of a compilation switch in SQLite3, is that an
option for you?  Could you recompile
it in your environment?


n 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mailman.common-lisp.net/pipermail/elephant-devel/attachments/20060419/5c71d1bb/attachment.html>