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

[Ignas Mikalajunas]

  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