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

[Ignas Mikalajunas]

    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
-------------- next part --------------
While accessing database #<SQLITE3-DATABASE /home/ignas/src/common-blog/dev.db OPEN {B386C11}>
  with expression "UPDATE _CLSQL_SEQ_PERSISTENT_SEQ SET last_value=1033":
  Error 21 / library routine called out of sequence
  has occurred.


0: (BACKTRACE 536870911 #<SB-IMPL::STRING-OUTPUT-STREAM {A716771}>)
1: (TBNL::GET-BACKTRACE #<unavailable argument>)
2: ((LAMBDA (COND)) #<CLSQL-SYS:SQL-DATABASE-DATA-ERROR {A7164B9}>)
3: ((LAMBDA (COND)) #<CLSQL-SYS:SQL-DATABASE-DATA-ERROR {A7164B9}>)
4: (SIGNAL #<CLSQL-SYS:SQL-DATABASE-DATA-ERROR {A7164B9}>)
5: (ERROR CLSQL-SYS:SQL-DATABASE-DATA-ERROR)
6: ((SB-PCL::FAST-METHOD CLSQL-SYS:DATABASE-EXECUTE-COMMAND
                         (T CLSQL-SQLITE3:SQLITE3-DATABASE))
    #<unavailable argument>
    #<unavailable argument>
    "UPDATE _CLSQL_SEQ_PERSISTENT_SEQ SET last_value=1033"
    #<CLSQL-SQLITE3:SQLITE3-DATABASE /home/ignas/src/common-blog/dev.db OPEN {B386C11}>)
7: ((LAMBDA
        (SB-PCL::.PV-CELL. SB-PCL::.NEXT-METHOD-CALL. SB-PCL::.ARG0.
         SB-PCL::.ARG1.))
    #<unavailable argument>
    #<unavailable argument>
    "UPDATE _CLSQL_SEQ_PERSISTENT_SEQ SET last_value=1033"
    #<CLSQL-SQLITE3:SQLITE3-DATABASE /home/ignas/src/common-blog/dev.db OPEN {B386C11}>)
8: ((FLET #:WITHOUT-INTERRUPTS-BODY-138))
9: ((SB-PCL::FAST-METHOD CLSQL-SYS:DATABASE-SEQUENCE-NEXT (T T))
    #<unavailable argument>
    #<unavailable argument>
    "PERSISTENT_SEQ"
    #<CLSQL-SQLITE3:SQLITE3-DATABASE /home/ignas/src/common-blog/dev.db OPEN {B386C11}>)
10: ((SB-PCL::FAST-METHOD CLSQL-SYS:DATABASE-SEQUENCE-NEXT
                          (T "#<...>" . "#<...>"))
     #<unavailable argument>
     #<unavailable argument>
     "PERSISTENT_SEQ"
     #<CLSQL-SQLITE3:SQLITE3-DATABASE /home/ignas/src/common-blog/dev.db OPEN {B386C11}>)
11: ((SB-PCL::FAST-METHOD INITIALIZE-INSTANCE :BEFORE (ELEPHANT:PERSISTENT))
     #<unavailable argument>
     #<unavailable argument>
     #<ELEPHANT:SQL-BTREE {B713E91}>
     (:SC #<ELEPHANT:SQL-STORE-CONTROLLER {B712DE9}>))
12: ((LAMBDA (SB-PCL::|.P0.|)) #<unavailable argument>)
13: (ELEPHANT:MAKE-SQL-BTREE #<ELEPHANT:SQL-STORE-CONTROLLER {B712DE9}>)
14: ((SB-PCL::FAST-METHOD ELEPHANT:OPEN-CONTROLLER
                          (ELEPHANT:SQL-STORE-CONTROLLER))
     (#(NIL 2 1) . #())
     #<unavailable argument>
     #<ELEPHANT:SQL-STORE-CONTROLLER {B712DE9}>
     (:RECOVER NIL :RECOVER-FATAL NIL :THREAD T))
15: (ELEPHANT::GET-CONTROLLER (:SQLITE3 "/home/ignas/src/common-blog/dev.db"))
16: (COMMON-BLOG::COMMON-BLOG-VIEW-WRAPPER COMMON-BLOG::ENTRY)
17: (TBNL::PROCESS-REQUEST
     (("content-stream"
       . #<SB-SYS:FD-STREAM for "a constant string" {B4627E9}>)
      ("server-protocol" . "HTTP/1.1") ("url" . "/entry/gtk-tutorial")
      ("method" . "GET") ("server-ip-port" . "9980")
      ("Cookie"
       . "zope3_cs_8aa2da3=J7bMX78FLS0bh-3MMCMigC0-yuMxtVntJOUMci.xjDZBdR.8ZvgWfQ")
      ("Connection" . "keep-alive") ("Keep-Alive" . "300")
      ("Accept-Charset" . "ISO-8859-1,utf-8;q=0.7,*;q=0.7")
      ("Accept-Encoding" . "gzip,deflate")
      ("Accept-Language" . "en-us,en;q=0.5")
      ("Accept"
       . "text/xml,application/xml,application/xhtml+xml,text/html;q=0.9,text/plain;q=0.8,image/png,*/*;q=0.5")
      ("User-Agent"
       . "Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.8.0.2) Gecko/20060308 Firefox/1.5.0.2")
      ("Host" . "localhost:9980")))
18: (TBNL::LISTEN-FOR-REQUEST
     #<SB-SYS:FD-STREAM for "a constant string" {B4627E9}>
     TBNL::PROCESS-REQUEST)
19: ((LAMBDA ()))
20: ((LAMBDA ()))
21: ("foreign function: call_into_lisp")
22: ("foreign function: funcall0")
23: ("foreign function: new_thread_trampoline")
24: ("foreign function: #xB7FC6341")