Re: [racket-users] How do db handles interact with threads and parameters
On 7/24/17 9:11 AM, George Neuner wrote:
Hi David,
On 7/24/2017 8:18 AM, David Storrs wrote:
What happens in the following code?
(define dbh (postgresql-connect ...))
;; Use the DBH in a new thread
(thread (thunk
(while ...some long-running condition...
(sleep 1) ; don't flood the DB
(query-exec dbh "insert into users ..."
;; And in the main thread
(query-exec dbh ...)
I now have a database object that's being shared between two threads,
yes? Or is the object copied when the new thread is created and, if
so, what will that do to the underlying connection to the DB?
The single DBMS connection is being shared by the 2 threads. That's a
recipe for disaster if both try to use it simultaneously.
To clarify "disaster": Connections are thread-safe, so the queries
performed by the 2 threads will be interleaved in some order, which is
fine if both threads are just doing reads. But the threads are not
isolated ("session-safe" or "conversation-safe"?). For example, if one
thread changes the time zone, it affects queries made by the other
thread. If one thread starts and later rolls back a transaction, it
might undo modifications made by the other thread. And so on.
Ryan
--
You received this message because you are subscribed to the Google Groups "Racket
Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to racket-users+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Re: [racket-users] How do db handles interact with threads and parameters
On Mon, Jul 24, 2017 at 9:11 AM, George Neuner wrote: > Hi David, > > On 7/24/2017 8:18 AM, David Storrs wrote: > > What happens in the following code? > > (define dbh (postgresql-connect ...)) > > ;; Use the DBH in a new thread > (thread (thunk > (while ...some long-running condition... > (sleep 1) ; don't flood the DB > (query-exec dbh "insert into users ..." > > ;; And in the main thread > (query-exec dbh ...) > > I now have a database object that's being shared between two threads, > yes? Or is the object copied when the new thread is created and, if so, > what will that do to the underlying connection to the DB? > > > The single DBMS connection is being shared by the 2 threads. That's a > recipe for disaster if both try to use it simultaneously. > > > New scenario: > > (define dbh (postgresql-connect ...)) > (define current-dbh (make-parameter dbh)) > (query-value (current-dbh) "select ...") > (sleep 100) > (query-value (current-dbh) "select ...") > > I would expect the first query-value to work but by the time the second > runs the handle has probably been disconnected by the database and the > query will throw an exception. Am I understanding that properly? > > > After sleeping 11.6 days? Probably. But it depends on the connection > keep-alive settings. By default, Postgresql uses the OS settings, which > are roughly ~130 minutes assuming that the client understands and > participates [which Racket does]. However, on most systems, keep-alive > timeouts can be set far higher. > > Aside: I'm not aware of any published limits on keep-alive settings, only > the defaults are known for various systems. If the settings are just > integral seconds and counts, and (theoretically) could go to MAX_INT, that > would represent ~3^20 years on a 32-bit machine ... for all practical > purposes, infinite. > > > Final scenario, identical to the previous except that the parameter is a > promise: > (define dbh (postgresql-connect ...)) > (define current-dbh (make-parameter (delay dbh))) > (query-value (force (current-dbh)) "select ...") > (sleep 100) > (query-value (force (current-dbh)) "select ...") > > Would this make any difference? I can't see why but thought I should ask. > > > There's no difference wrt the above. You aren't delaying the *opening* of > the connection - you are simply performing unnecessary gyrations to get at > the handle. There would be a difference if instead you wrote: > >(define dbh (delay (postgresql-connect ...))) >(define current-dbh (make-parameter dbh)) >(query-value (force (current-dbh)) "select ...") >(sleep ...) >(query-value (force (current-dbh)) "select ...") > > which would try to open the connection at the point of each query. > However, at the 2nd query, if the connection were still open due to > keep-alive, the repeated open attempt might fail. > > > George > Okay, good. That's all as I expected. Thanks, George. > -- > You received this message because you are subscribed to the Google Groups > "Racket Users" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to racket-users+unsubscr...@googlegroups.com. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "Racket Users" group. To unsubscribe from this group and stop receiving emails from it, send an email to racket-users+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: [racket-users] How do db handles interact with threads and parameters
Hi David, On 7/24/2017 8:18 AM, David Storrs wrote: What happens in the following code? (define dbh (postgresql-connect ...)) ;; Use the DBH in a new thread (thread (thunk (while ...some long-running condition... (sleep 1) ; don't flood the DB (query-exec dbh "insert into users ..." ;; And in the main thread (query-exec dbh ...) I now have a database object that's being shared between two threads, yes? Or is the object copied when the new thread is created and, if so, what will that do to the underlying connection to the DB? The single DBMS connection is being shared by the 2 threads. That's a recipe for disaster if both try to use it simultaneously. New scenario: (define dbh (postgresql-connect ...)) (define current-dbh (make-parameter dbh)) (query-value (current-dbh) "select ...") (sleep 100) (query-value (current-dbh) "select ...") I would expect the first query-value to work but by the time the second runs the handle has probably been disconnected by the database and the query will throw an exception. Am I understanding that properly? After sleeping 11.6 days? Probably. But it depends on the connection keep-alive settings. By default, Postgresql uses the OS settings, which are roughly ~130 minutes assuming that the client understands and participates [which Racket does]. However, on most systems, keep-alive timeouts can be set far higher. Aside: I'm not aware of any published limits on keep-alive settings, only the defaults are known for various systems. If the settings are just integral seconds and counts, and (theoretically) could go to MAX_INT, that would represent ~3^20 years on a 32-bit machine ... for all practical purposes, infinite. Final scenario, identical to the previous except that the parameter is a promise: (define dbh (postgresql-connect ...)) (define current-dbh (make-parameter (delay dbh))) (query-value (force (current-dbh)) "select ...") (sleep 100) (query-value (force (current-dbh)) "select ...") Would this make any difference? I can't see why but thought I should ask. There's no difference wrt the above. You aren't delaying the *opening* of the connection - you are simply performing unnecessary gyrations to get at the handle. There would be a difference if instead you wrote: (define dbh (delay (postgresql-connect ...))) (define current-dbh (make-parameter dbh)) (query-value (force (current-dbh)) "select ...") (sleep ...) (query-value (force (current-dbh)) "select ...") which would try to open the connection at the point of each query. However, at the 2nd query, if the connection were still open due to keep-alive, the repeated open attempt might fail. George -- You received this message because you are subscribed to the Google Groups "Racket Users" group. To unsubscribe from this group and stop receiving emails from it, send an email to racket-users+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
