LoginSignup
3
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@k_hoso

Pythonのdataset（SQLAlchemyのラッパー）のclose系処理を調べる

Last updated at Posted at 2020-09-25

概要

datasetでクローズ処理した時の挙動がいまいちよく分からないので色々試して整理する。

dataset公式

環境

4.14.193-149.317.amzn2.x86_64 #1 SMP Thu Sep 3 19:04:44 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux

Python 3.8.5

下記がpip installされている状態。
dataset 1.3.2
SQLAlchemy 1.3.19
mysqlclient 1.4.6

結論

結論からまず先に。。

コネクションプールを残したい時は、↓（細かい話は "条件３" を参照）。

con.executable.close()
del con.local.conn

DB切断までやりたい時は、条件６の、

con.executable.invalidate()
con.executable.engine.dispose()

ちなみに、、検証結果の pool.status() の数字の意味は雰囲気的に↓かなぁ、、

Pool size: そのまま
Connections in pool: プールとして使用可能なDB接続している本数
Current Overflow: 最大で、あと何接続までできるか（max_overflowのデフォが10なので、-10からっぽい）
Current Checked out connections: スレッドが掴んでるDB接続数

調査内容

下記の流れ。

select実行
↓
close処理
↓
状態を見る
↓
同じconを使って再度、select実行
↓
close処理
↓
状態を見る

前提

↓が実行されていることを前提とする。

con = dataset.connect(
    'mysql://root:password@172.17.0.1:3306/hoge',
    engine_kwargs={
        'pool_size': 10,
        # 'max_overflow': 0,
    }
)

for record in con['table1']:
    print(record)

最初の"select実行"時点での状態は一律↓の通り。

con.executable.closed     : False
con.executable.invalidated: False
con.engine.pool.status()  : 
Pool size: 10  Connections in pool: 0 Current Overflow: -9 Current Checked out connections: 1

**"DB接続が切断されるかどうか"**の項目に関して

MySQLに対し、1秒ごとにshow processlist \gを実行して、DBとの接続がされているかどうか見てる。

備考

close処理の con.executable.xxxx() はSQLAlchemyの関数を実行している。

調査結果

条件０(close処理を実行しない)

closeを実行しない。

検証１

select実行
↓
conを使いまわしてもう一回、select
↓
接続が使い回される（Poolが効いてる）

初回実行後
con.engine.pool.status()  : 
Pool size: 10  Connections in pool: 0 Current Overflow: -9 Current Checked out connections: 1
↓
2回目実行後
con.engine.pool.status()  : 
Pool size: 10  Connections in pool: 0 Current Overflow: -9 Current Checked out connections: 1

検証２

10スレッド同時にSQLを流す
↓
スレッドが全部終わった後、conを使いまわしてもう一回、10スレッド同時にSQLを流す
↓
接続が使い回される（Poolが効いてる）

※`max_overflow`を`0`にしないと、デフォルト値で`10`は`pool_size`超えてくるので注意
※DB接続はスレッドが終了するタイミングで勝手に開放してる（Pythonのスレッドってどうなんだっけ。。）

初回実行後
con.engine.pool.status()  : 
Pool size: 10  Connections in pool: 9 Current Overflow: 0 Current Checked out connections: 1
↓
2回目実行後
con.engine.pool.status()  : 
Pool size: 10  Connections in pool: 10 Current Overflow: 1 Current Checked out connections: 1
※overflowが発動してしまっているけど。。

接続の使いまわしの確認方法

最初に流したSQLのMySQL側のプロセスIDと2回目のプロセスIDが一致することで確認している。
※show processlist \gのIDカラムの値が最初と2回目で一致する

スレッドで実行している場合も同様。
10スレッド同時実行時は初回でIDが 1,2,3,4,5,6,7,8,9,10 になっていたら、2回目でもIDが 1,2,3,4,5,6,7,8,9,10 になっていることを確認している。

余談

pool_size:10, max_overflow: 0 で、20スレッド実行とかした場合、ちゃんとpoo_sizeを超えないように制限される。
※上記設定の場合、同時に処理されるのが10接続分までになり、空いた接続が再利用される

まとめ

一見するとこれが正解のように思えるが、実はselectした結果をキャッシュしてしまうというトラップが発生している。
なので、実際には con.executable.close() をする必要がある。
ただ、con.executable.close() だけすれば良いという訳でもなく、詳細は "条件３" を参照。

条件１(con.close())

con.close()

DB接続が切断されるかどうか

切断されない。

close実行後

con.executable.closed     : False
con.executable.invalidated: False
con.engine.pool.status()  : 実行できない

close後、同じconを使ってselectするとどうなるか

実行できるし、結果もとれる（接続は使いまわされている）。
が、２回目の con.close() で↓のエラーがでる。

'NoneType' object has no attribute 'dispose'

まとめ

プールに接続を返し、プールで接続を1つ持っている状態（と思われる）。
pool.status()が見れなくなるのでプール管理がどうなっているのかの詳細が分からない。

DBとの接続自体は切れていないので注意。
conの中のengineが削除される。(つまり、どういう事なのかは分からん。。)

あとclose()2回目でエラーが出てしまうので使いどころはない印象。

そもそもclose()しなくていい説ある。

条件２(con.close(); con.executable.engine.dispose())

con.close()
con.executable.engine.dispose()

DB接続が切断されるかどうか

切断されない。

close実行後

con.executable.closed     : False
con.executable.invalidated: False
con.engine.pool.status()  : 実行できない

close後、同じconを使ってselectするとどうなるか

実行できるし、結果もとれる（接続は使いまわされている）。
が、２回目の con.close() で↓のエラーがでる。

'NoneType' object has no attribute 'dispose'

まとめ

条件１と状態はかわらない。

条件３(con.executable.close())

con.executable.close()

DB接続が切断されるかどうか

切断されない。

close実行後

con.executable.closed     : True
con.executable.invalidated: False
con.engine.pool.status()  : 
Pool size: 10  Connections in pool: 1 Current Overflow: -9 Current Checked out connections: 0

close後、同じconを使ってselectするとどうなるか

↓が出て実行できない。

(sqlalchemy.exc.ResourceClosedError) This Connection is closed

まとめ

プールに接続を返し、プールが接続を1つ持っている状態。
con.executable.closed が True になるが、何を意味しているのかは分からん。。
selectの再実行ができないし、DB切断もされないので使いどころがない感じ。

と、、見せかけておいて、実は con.executable.close() 処理をしないと、selectを実行した結果をキャッシュしてしまう。。
で con.executable.close() するとそもそも再実行できないじゃんっていう話になるが、ローカルスレッドが保持している接続情報（というのかどうか）を削除することで、再実行可能になる。
※これが残っていると、closedなので再実行できないよっていう例のエラーが出る

コードで書くと下記の通り。

con.executable.close()
del con.local.conn

そして、この方式が恐らくコネクションプールを残す場合の正解だと思われる。

条件４(con.executable.close(); con.executable.engine.dispose())

con.executable.close()
con.executable.engine.dispose()

DB接続が切断されるかどうか

切断される。

close実行後

con.executable.closed     : True
con.executable.invalidated: False
con.engine.pool.status()  : 
Pool size: 10  Connections in pool: 0 Current Overflow: -10 Current Checked out connections: 0

close後、同じconを使ってselectするとどうなるか

↓が出て実行できない。

(sqlalchemy.exc.ResourceClosedError) This Connection is closed

まとめ

DBとの接続が切断される(プールされている接続が無くなる）)
selectの再実行ができなくなるので、最終処理用な気がする。

条件５(con.executable.invalidate())

con.executable.invalidate()

DB接続が切断されるかどうか

切断される。

close実行後

DB接続は切れているのにプールに接続が残っていることになっている。

con.executable.closed     : False
con.executable.invalidated: True
con.engine.pool.status()  : 
Pool size: 10  Connections in pool: 1 Current Overflow: -9 Current Checked out connections: 0

close後、同じconを使ってselectするとどうなるか

普通に実行できた。
一度切断しているので再接続している（MySQL側でSQLを実行しているプロセスのIDが変わる）。

# select実行後の状態

con.executable.closed     : False
con.executable.invalidated: False
con.engine.pool.status()  : 
Pool size: 10  Connections in pool: 0 Current Overflow: -9 Current Checked out connections: 1

# close実行後の状態

con.executable.closed     : False
con.executable.invalidated: True
con.engine.pool.status()  : 
Pool size: 10  Connections in pool: 1 Current Overflow: -9 Current Checked out connections: 0

まとめ

DBとの接続が切断される(プールされている接続が無くなる）。
実際のDB接続は切れているが、プール管理的には接続している体になっているのであまり良い状態ではない気がする。
※実際には切れているのに、Connections in pool: 1 になっている

条件６(con.executable.invalidate(); con.executable.engine.dispose())

con.executable.invalidate()
con.executable.engine.dispose()

DB接続が切断されるかどうか

切断される。

close実行後

con.executable.closed     : False
con.executable.invalidated: True
con.engine.pool.status()  : 
Pool size: 10  Connections in pool: 0 Current Overflow: -10 Current Checked out connections: 0

close後、同じconを使ってselectするとどうなるか

普通に実行できた。
一度切断しているので再接続している（MySQL側でSQLを実行しているプロセスのIDが変わる）。

# select実行後の状態

con.executable.closed     : False
con.executable.invalidated: False
con.engine.pool.status()  : 
Pool size: 10  Connections in pool: 0 Current Overflow: -9 Current Checked out connections: 1

# close実行後の状態

con.executable.closed     : False
con.executable.invalidated: True
con.engine.pool.status()  : 
Pool size: 10  Connections in pool: 0 Current Overflow: -10 Current Checked out connections: 0

まとめ

DBとの接続が切断される(プールされている接続が無くなる）。
実際のDB接続状態とプール管理状態が一致しており、close後のselect再実行まで含めると、一番健全っぽく見える。

3
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
3
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?