Haskell中的并发数据库连接池 [英] Concurrent DB connection pool in Haskell

问题描述

我是学习Haskell的Java程序员。

我在使用Happstack并通过HDBC与数据库进行对话的小型网络应用程序。

我写了 select 和 exec 函数，并且像这样使用它们：

 模块Main其中
 
导入Control.Exception（抛出）
 
导入Database.HDBC 
导入Database.HDBC.Sqlite3  - 仅用于这个例子中，我使用MySQL生产
 
 main = do 
 execCREATE TABLE IF NOT EXISTS users（name VARCHAR（80）NOT NULL）[] 
 
 execINSERT INTO users VALUES（'John'）[] 
 execINSERT INTO users VALUES（'Rick'）[] 
 
 rows<  -  selectSELECT name FROM用户[] 
 
 let toS x =（fromSql x）:: String 
 let names = map（toS。head）rows 
 
 print names 
  

非常简单，如您所见。有查询， params 和结果。
连接创建和提交/回滚的内容隐藏在select和exec中。

这很好，我不想在我的逻辑代码中关心它。

  exec :: String  - > [SqlValue]  - > IO Integer 
 exec query params = withDb $ \c  - >运行c查询参数
 
 select :: String  - > [SqlValue]  - > IO [[SqlValue]] 
 select query params = withDb $ \c  - > quickQuery'c query params 
 
 withDb ::（Connection  - > IO a） - > IO a 
 withDb f = do 
 conn<  -  handleSqlError $ connectSqlite3users.db
 catchSql 
（do r < -  f conn 
 commit conn 
 disconnect conn 
 return r）
（\e @（SqlError _ _ m） - > do 
回滚连接
断开连接
 throw e） 
  

坏点：

• 每次通话都会创建一个新连接 - 这会导致重负载时的性能


• DB urlusers.db是硬编码的 - 我无法重复使用这些连接在没有编辑的情况下跨其他项目的功能

问题1：定义（最小，最大）并发连接数，所以连接将在select / exec调用之间重用？

问题2：使users.db字符串可配置？ （如何将其移动到客户端代码？）

它应该是一个透明的功能：用户代码不应该要求明确的连接处理/发布。$ b $问题2：我从来没有使用过HDBC，但是我可能会写这样的东西。

b
$ b

  trySql :: Connection  - > （连接 - > IO a） - > IO a 
 trySql conn f = handleSql捕获器$ do 
 r <-f conn 
提交conn 
返回r 
其中catcher e = rollback conn>>抛出e 
  

打开连接函数，并且不要在函数内断开连接。

问题1：嗯，连接池似乎并不那么难执行...

$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $' a =
Pool {poolMin :: Int，poolMax :: Int，poolUsed :: Int，poolFree :: [a]}

newConnPool low high newConn delConn = do
cs < - handleSqlError。序列 。复制低newConn
mPool < - newMVar $ Pool低高0 cs
返回（mPool，newConn，delConn）

delConnPool（mPool，newConn，delConn）=
pool< - takeMVar mPool
if length（poolFree pool）/ = poolUsed pool
then putMVar mPool pool>>失败pool in use
else mapM_ delConn $ poolFree pool

takeConn（mPool，newConn，delConn）= modifyMVar mPool $ \pool - >
案例pool $
的免费池conn：cs - >
return（pool {poolUsed = poolUsed pool + 1，poolFree = cs}，conn）
_ |池用水池< poolMax池 - >
conn< - handleSqlError newConn
return（pool {poolUsed = poolUsed pool + 1}，conn）
_ - >失败pool is exhausted

putConn（mPool，newConn，delConn）conn = modifyMVar_ mPool $ \pool - >
让used = poolUsed池在
中（如果使用）> poolMin conn
then handleSqlError（delConn conn）>> return（pool {poolUsed = used - 1}）
else return $ pool {poolUsed = used - 1，poolFree = conn：poolFree pool}
$ b with conn connPool =括号（takeConn connPool）（ putConn conPool）

你可能不应该接受这个逐字，因为我甚至没有对它进行编译测试（和失败有非常不友好的），但想法是做一些像

  connPool < -  newConnPool 0 50（connectSqlite3user.db）disconnect 
  

和根据需要传递 connPool around。

I am a Java programmer who learns Haskell.
I work on a small web-app that uses Happstack and talks to a database via HDBC.

I've written select and exec functions and I use them like this:

module Main where

import Control.Exception (throw)

import Database.HDBC
import Database.HDBC.Sqlite3 -- just for this example, I use MySQL in production

main = do
    exec "CREATE TABLE IF NOT EXISTS users (name VARCHAR(80) NOT NULL)" []

    exec "INSERT INTO users VALUES ('John')" []
    exec "INSERT INTO users VALUES ('Rick')" []

    rows <- select "SELECT name FROM users" []

    let toS x = (fromSql x)::String
    let names = map (toS . head) rows

    print names

Very simple as you see. There is query, params and result.
Connection creation and commit/rollback stuff is hidden inside select and exec.
This is good, I don't want to care about it in my "logic" code.

exec :: String -> [SqlValue] -> IO Integer
exec query params = withDb $ \c -> run c query params

select :: String -> [SqlValue] -> IO [[SqlValue]]
select query params = withDb $ \c -> quickQuery' c query params

withDb :: (Connection -> IO a) -> IO a
withDb f = do
    conn <- handleSqlError $ connectSqlite3 "users.db"
    catchSql
        (do r <- f conn
            commit conn
            disconnect conn
            return r)
        (\e@(SqlError _ _ m) -> do
            rollback conn
            disconnect conn
            throw e)

Bad points:

• a new connection is always created for every call - this kills performance on heavy load
• DB url "users.db" is hardcoded - I can't reuse these functions across other projects w/o editing

QUESTION 1: how to introduce a pool of connections with some defined (min, max) number of concurrent connections, so the connections will be reused between select/exec calls?

QUESTION 2: How to make "users.db" string configurable? (How to move it to client code?)

It should be a transparent feature: user code should not require explicit connection handling/release.

解决方案

QUESTION 2: I've never used HDBC, but I'd probably write something like this.

trySql :: Connection -> (Connection -> IO a) -> IO a
trySql conn f = handleSql catcher $ do
    r <- f conn
    commit conn
    return r
  where catcher e = rollback conn >> throw e

Open the Connection somewhere outside of the function, and don't disconnect it within the function.

QUESTION 1: Hmm, a connection pool doesn't seem that hard to implement...

import Control.Concurrent
import Control.Exception

data Pool a =
    Pool { poolMin :: Int, poolMax :: Int, poolUsed :: Int, poolFree :: [a] }

newConnPool low high newConn delConn = do
    cs <- handleSqlError . sequence . replicate low newConn
    mPool <- newMVar $ Pool low high 0 cs
    return (mPool, newConn, delConn)

delConnPool (mPool, newConn, delConn) = do
    pool <- takeMVar mPool
    if length (poolFree pool) /= poolUsed pool
      then putMVar mPool pool >> fail "pool in use"
      else mapM_ delConn $ poolFree pool

takeConn (mPool, newConn, delConn) = modifyMVar mPool $ \pool ->
    case poolFree pool of
        conn:cs ->
            return (pool { poolUsed = poolUsed pool + 1, poolFree = cs }, conn)
        _ | poolUsed pool < poolMax pool -> do
            conn <- handleSqlError newConn
            return (pool { poolUsed = poolUsed pool + 1 }, conn)
        _ -> fail "pool is exhausted"

putConn (mPool, newConn, delConn) conn = modifyMVar_ mPool $ \pool ->
    let used = poolUsed pool in
    if used > poolMin conn
      then handleSqlError (delConn conn) >> return (pool { poolUsed = used - 1 })
      else return $ pool { poolUsed = used - 1, poolFree = conn : poolFree pool }

withConn connPool = bracket (takeConn connPool) (putConn conPool)

You probably shouldn't take this verbatim as I haven't even compile-tested it (and fail there is pretty unfriendly), but the idea is to do something like

connPool <- newConnPool 0 50 (connectSqlite3 "user.db") disconnect

and pass connPool around as needed.

这篇关于Haskell中的并发数据库连接池的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！