With-connection and concurrent requests

[pyankoff]

I use (with-connection (db) CODE) to access database. I get the following error when there are concurrent requests accessing database. Thanks!

A failure in the SSL library occurred on handle #.(SB-SYS:INT-SAP #X007C3090) (return code: 1).
SSL error queue:
error:1408F119:SSL routines:SSL3_GET_RECORD:decryption failed or bad record mac
   [Condition of type CL+SSL::SSL-ERROR-SSL]

Restarts:
 0: [ABORT] abort thread (#<THREAD "hunchentoot-worker-127.0.0.1:62881" RUNNING {1009DA6F03}>)

Backtrace:
  0: (CL+SSL::SSL-SIGNAL-ERROR #.(SB-SYS:INT-SAP #X007C3090) #<FUNCTION CL+SSL::SSL-READ> 1 -1)
  1: ((SB-PCL::FAST-METHOD STREAM-READ-BYTE (CL+SSL::SSL-STREAM)) #<unavailable lambda list>) [fast-method]
  2: (READ-BYTE #<CL+SSL::SSL-STREAM for #<FD-STREAM for "socket 192.168.0.77:62879, peer: :5432" {100A6451B3}>> T NIL)
  3: (CL-POSTGRES::READ-UINT1 #<unavailable argument>)
  4: (CL-POSTGRES::SEND-EXECUTE #<CL+SSL::SSL-STREAM for #<FD-STREAM for "socket 192.168.0.77:62879, peer: :5432" {100A6451B3}>> #<unavailable argument> #<unavailable argument> #<unavailable arg..
  5: ((LABELS #:G736 :IN CL-POSTGRES:EXEC-PREPARED))
  6: ((:METHOD DBI.DRIVER:EXECUTE-USING-CONNECTION (DBD.POSTGRES:<DBD-POSTGRES-CONNECTION> DBD.POSTGRES:<DBD-POSTGRES-QUERY> T)) #<DBD.POSTGRES:<DBD-POSTGRES-CONNECTION> {100A65DC63}> #<DBD.POSTGRES:<DBD-P..
  7: (DATAFLY.DB::EXECUTE-WITH-CONNECTION #<unavailable argument> #<SXQL-STATEMENT: SELECT * FROM #1=phrases#1# WHERE (#1#id#1# = '1998')>)
  8: (DATAFLY.DB:RETRIEVE-ONE #<unavailable argument> :BY NIL := NIL :AS NIL :PRETTIF

[pyankoff]

Here is a code to reproduce it

(defun make-err ()
  (bt:make-thread
   #'(lambda ()
       (sleep 0.01)
       (dotimes (i 100)
         (sleep 0.01)
         (print
          (with-connection (db)
            (retrieve-one
             (select ((:count :id))
               (from :users))))))))
  (dotimes (i 100)
    (sleep 0.01)
    (print
     (with-connection (db)
       (retrieve-one
        (select ((:count :id))
          (from :users)))))))

[pyankoff]

I've tried not creating threads myself, but just use multiple WOO workers with:worker-num 4 parameter. I still get DB errors. <ERROR> [16:31:22] woo - PostgreSQL protocol error: Unexpected message received: 2 <ERROR> [16:29:16] woo - DB Error: This connection is still processing another query. (Code: NIL) Here is an example log:

Backtrace for: #<SB-THREAD:THREAD "woo-worker" RUNNING {10074EDCF3}>
0: ((LAMBDA NIL :IN SB-DEBUG::FUNCALL-WITH-DEBUG-IO-SYNTAX))
1: (SB-IMPL::CALL-WITH-SANE-IO-SYNTAX #<CLOSURE (LAMBDA NIL :IN SB-DEBUG::FUNCALL-WITH-DEBUG-IO-SYNTAX) {10095C7D1B}>)
2: (SB-IMPL::%WITH-STANDARD-IO-SYNTAX #<CLOSURE (LAMBDA NIL :IN SB-DEBUG::FUNCALL-WITH-DEBUG-IO-SYNTAX) {10095C7CEB}>)
3: (SB-DEBUG:PRINT-BACKTRACE :STREAM #<SYNONYM-STREAM :SYMBOL SB-SYS:*STDERR* {100013A9D3}> :START 0 :FROM :DEBUGGER-FRAME :COUNT 4611686018427387903 :PRINT-THREAD T :PRINT-FRAME-SOURCE NIL :METHOD-FRAME-STYLE NIL :EMERGENCY-BEST-EFFORT NIL)
4: ((LAMBDA NIL :IN UIOP/IMAGE:PRINT-BACKTRACE))
5: ((LAMBDA NIL :IN UIOP/STREAM:CALL-WITH-SAFE-IO-SYNTAX))
6: (SB-IMPL::%WITH-STANDARD-IO-SYNTAX #<CLOSURE (LAMBDA NIL :IN UIOP/STREAM:CALL-WITH-SAFE-IO-SYNTAX) {10095C7CCB}>)
7: (UIOP/IMAGE:PRINT-CONDITION-BACKTRACE #<CL-POSTGRES::PROTOCOL-ERROR {10095C7C23}> :STREAM #<SYNONYM-STREAM :SYMBOL SB-SYS:*STDERR* {100013A9D3}> :COUNT NIL)
8: (LACK.MIDDLEWARE.BACKTRACE::PRINT-ERROR #<CL-POSTGRES::PROTOCOL-ERROR {10095C7C23}> (:RAW-BODY #<FLEXI-STREAMS::VECTOR-INPUT-STREAM {10095C3A93}> :REQUEST-METHOD :POST :SCRIPT-NAME "" :SERVER-NAME "" :SERVER-PORT 80 :SERVER-PROTOCOL :HTTP/1.1 ...) #<SYNONYM-STREAM :SYMBOL SB-SYS:*STDERR* {100013A9D3}>)
9: ((FLET #:H0 :IN "/home/ubuntu/.roswell/impls/ALL/ALL/quicklisp/dists/quicklisp/software/lack-20160318-git/src/middleware/backtrace.lisp") #<CL-POSTGRES::PROTOCOL-ERROR {10095C7C23}>)
10: (SIGNAL #<CL-POSTGRES::PROTOCOL-ERROR {10095C7C23}>)
11: (ERROR CL-POSTGRES::PROTOCOL-ERROR :MESSAGE "Unexpected message received: 2")
12: (CL-POSTGRES::SEND-PARSE #<unavailable argument> #<unavailable argument> #<unavailable argument>)
13: ((:METHOD DBI.DRIVER:PING (DBD.POSTGRES:<DBD-POSTGRES-CONNECTION>)) #<DBD.POSTGRES:<DBD-POSTGRES-CONNECTION> {1003B4BE83}>) [fast-method]

I would really appreciate any help. Thanks!

[ghost]

Hi, I found this stackoverflow question http://stackoverflow.com/questions/21440048/postgres-concurrent-queries-in-a-connection.

The answer said that postgres does not handle concurrent queries in one connection.

You could try making a connection per worker though. You could also start with one connection and catch the db error and proceed to create additional connections to a pool as needed or simply wait for one query to complete if max connection count is reached. you could also initialize a said number of connections at start to pool for the workers to use. In the later cases you would have to create a query queue and the said pool as well.

To do the first I would start trying to redefine the db function in the <app_root>/src/db.lisp to default selecting the worker db connection based on some identifier rather than the :maindb.

Hope this helps.

[knobo]

Is this something that pgbouncer or something similar could fix?

[ghost]

Don't quote me on this but as far as I know pgbouncer provides session queue and the problem here seems to be related to transaction queue within a session.

In short I think that with pgbouncer I can utilize multiple connections that still are many more that the concurrent connection limit on the postgres server. However, in the current setting we only have one connection so I could argue that it is of little use.

I would definitely like to be proved wrong here as that would help me too. If not, you would have to use a queue manager on the application side and use the connection to input to a crafted queue on the database. Take a look at Que for reference. With this you wouldn't be using the database directly but inputting everything to a queue from where the database would pick up transactions to do. I'm not 100% sure of how it works, more research needs to be done.

You could also try to manage the queue completely in application and have it commit using the one connection we have by default. You would then have to use a separate thread for the queue manager or even its own process. Still there is only one transaction per connection at any given time.

[daninus14]

I'm getting this issue from a web client making multiple requests to the same defroute which uses mito to make a DB call.

Shouldn't the simple solution be to create a new thread for each time a new request is done? Why is the same thread handling multiple requests from the same client session?

Any workaround on how to request a new connection each time a route is hit from the same session?

@pyankoff Did you find anything?

[daninus14]

Any idea on how to force a defroute to wait for the DB call to finish from a previous request from the same session?

[daninus14]

fukamachi answered here: https://github.com/fukamachi/mito/issues/40#issuecomment-440144744