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

[Ian Eslick]

Robert, have you had this problem?  Any use of SQLite in a web app will
require multi-threaded use so I can't imagine you haven't run across
this issue or that it isn't a problem in your environment (as your code
fragment indicated).

Ignas, SQLite is really easy to compile so if you have to you can
download your own source distribution and compile it, perhaps that will
help.

It is critical that Elephant backends work across multiple threads so I
want to make sure we genuinely understand and have a clean resolution
for this issue.  If your blogging example is pretty simple, be nice to
use it as an example application of elephant!

Ian

Ignas Mikalajunas wrote:
>   Thanks for explaining me. So as I understand - even if i would fix
> the "one-thread opens the database connection, but because of
> incorrect special variable use another thread tries using the
> connection" it would not be reliable as elephant migh have/introduce
> problems when one database is used by multiple stores, and supporting
> that is not very feasible.
>
>
>   
>> Threading is not cleanly supported yet.  The model where you have a
>> single controller variable declared and opened in the parent thread and
>> many children using it should work under with-transaction.
>>     
>
> The sqlite on Ubuntu Dapper is compiled so that it disallows using the
> connection in the thread that didn't spawn it which means that:
>
> (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)
>
> fails on my computer:
>
> While accessing database #<SQLITE3-DATABASE /home/ignas/test.db OPEN {AE1F001}>
>   with expression "SELECT VALUE FROM KEYVALUE WHERE ((CLCTN_ID = 0)
> AND (KEY = 'FQQAAAB4AAAA'))":
>   Error 21 / library routine called out of sequence
>   has occurred.
>    [Condition of type CLSQL-SYS:SQL-DATABASE-DATA-ERROR]
>
> Restarts:
>   0: [TERMINATE-THREAD] Terminate this thread (#<THREAD {AB845B9}>)
>
> Backtrace:
>   0: ((SB-PCL::FAST-METHOD CLSQL-SYS:DATABASE-QUERY (T
> CLSQL-SQLITE3:SQLITE3-DATABASE T T)) #<unavailable lambda list>)
>   1: ((LAMBDA (SB-PCL::.PV-CELL. SB-PCL::.NEXT-METHOD-CALL.
> SB-PCL::.ARG0. SB-PCL::.ARG1. SB-PCL::.ARG2. SB-PCL::.ARG3.))
> #<unavailable argument> #<unavailable argument> "SELECT VALUE FROM
> KEYVALUE WHERE ((CLCTN_ID = 0) AND (KEY = 'FQQAAAB4AAAA'))"
> #<CLSQL-SQLITE3:SQLITE3-DATABASE /home/ignas/test.db OPEN {AE1F001}>
> :AUTO T)
>   2: ((SB-PCL::FAST-METHOD CLSQL-SYS:QUERY (STRING)) #<unavailable
> argument> #<unavailable argument> "SELECT VALUE FROM KEYVALUE WHERE
> ((CLCTN_ID = 0) AND (KEY = 'FQQAAAB4AAAA'))" (:DATABASE
> #<CLSQL-SQLITE3:SQLITE3-DATABASE /home/ignas/test.db OPEN {AE1F001}>
> :FLATP NIL :RESULT-TYPES :AUTO :FIELD-NAMES T))
>   3: (ELEPHANT-CLSQL::SQL-FROM-CLCN-EXISTSP 0 "x"
> #<CLSQL-SQLITE3:SQLITE3-DATABASE /home/ignas/test.db OPEN {AE1F001}>)
>   4: (ELEPHANT-CLSQL::SQL-ADD-TO-CLCN 0 "x" #:G3144 #<unused argument>
> #<CLSQL-SQLITE3:SQLITE3-DATABASE /home/ignas/test.db OPEN {AE1F001}>
> :INSERT-ONLY NIL)
>   5: ((LAMBDA NIL))
>   6: ((SB-PCL::FAST-METHOD ELEPHANT::EXECUTE-TRANSACTION
> (ELEPHANT-CLSQL::SQL-STORE-CONTROLLER T)) #<unavailable argument>
> #<unavailable argument> #<ELEPHANT-CLSQL::SQL-STORE-CONTROLLER
> {AC5C7F9}> #<FUNCTION (LAMBDA NIL) {AB71045}> #<unavailable argument>)
>   7: ((LAMBDA NIL))
>   8: ("foreign function: call_into_lisp")
>   9: ("foreign function: funcall0")
>  10: ("foreign function: new_thread_trampoline")
>  11: ("foreign function: #xB7FC6341")
>
> Apparently sqlite is not designed to support the model elephant wants:
>
> http://www.sqlite.org/faq.html#q8
>
> quote: "It is never safe to use the same sqlite3 structure pointer in
> two or more threads."
>
> thus even if i would compile my own binary - it is not safe to use it
> in the way that you suggest. Using a newer version of sqlite would
> allow to reuse the same database connection if i would make sure that
> only one thread is working with the controler (which would remove the
> overhead of opening a new controler for every request),but as all i
> want to do is - have a small easily deployable blogging application,
> having a custom binary of sqlite is not really an option. So i am
> using mutexes to ensure that only one store-controler is open at any
> time. Which gets me the overhead of opening the store on each http
> request, but at least it works.
>
> Thanks a lot for all the support you have provided. I guess the
> components sqlite/elephant/tbnl were not really designed to
> interoperate together nicely while all of them have the features i
> want which is more of a social problem not a programming one.
>
> Ignas Mikalajūnas
>
> P.S. the url i have posted seems to be up now (it was down an hour
> before so it must be problems with their server).
>
>  http://projects.edgewall.com/trac/ticket/2196
>