4

In several places (1, 2) I find the following statement as to why I should use the pool package (https://github.com/rstudio/pool) to manage my database connections in a Shiny app:

Opening only one connection per app … cannot handle simultaneous requests (e.g. two sessions open, both querying the database at the same time);

My understanding of shiny apps is that they run in a single-threaded R process, hence there can never be two requests at the same time. Do I miss something here? Why would I want a pool of multiple connections per app if only a single one is used at any time anyways?

(I understand that a pool with a single connection may still be useful as the pool package handles automatic re-connection in case the connection drops.)

-- Thanks, David

David K
  • 78
  • 6
  • `pool` is not about multiple `R` processes but about connections to a database. – Trusky Dec 14 '20 at 03:42
  • @Trusky But I cannot think of a use-case for several connections (to the same DB) in a single R process (at least there's always a better alternative with only a single connection). – David K Dec 14 '20 at 16:41

1 Answers1

2

Shiny server can serve multiple end-users with a single-threaded R process. Please see the diagram and description at the start of this article. The R process alternates which user it handles at any particular instant. These multiple users can all be making different requests to your DB during overlapping time intervals.

Suppose you have a block of code that makes several requests to the DB. You might think of this big block as a single thing to compute, but maybe the R process thinks it should pause in the middle and serve a different user.

Michael Dewar
  • 2,553
  • 1
  • 6
  • 22
  • But shiny does not switch between user-sessions at any point in the execution, does it? It only switches in between reactive expressions. "Occupying" a DB connection across reactive expressions does not sound like a robust approach in the first place. --David – David K Dec 14 '20 at 16:26
  • It is possible to use two connections, though it's a bit contrived: if you shift from "connection" mindset to "result-set" (as in `dbSendStatement(...)` and then later and/or iteratively `dbFetch`), many (most?) connections in R do not support multiple resultsets at a time, so one might choose one connection for a long-return query, and then a second connection for either tangential or just "other" connections while iterating over the first. I don't do this, and its complexity can be avoided, but ... perhaps it's better for some situations. – r2evans Dec 14 '20 at 17:24
  • 1
    @r2evans Yes, that may be a use-case for multiple connections, but you'd still only serve one session at a time (although this one session is served by several connections). – David K Dec 14 '20 at 18:01