psycopg2のconnectionオブジェクトのclosedフラグが0でもPostgreSQLがロックされない場合がある

`psycopg2`ライブラリを使ってPostgreSQLを参照している際に不思議な事象に見舞われたのでメモしておきます。

# 環境

- PostgreSQL: 10.4
- Python: 3.6.6
- psycopg2:  2.8.2 (dt dec pq3 ext lo64)
- pandas:  0.24.2

# 準備

PostgreSQLに検証用テーブルを作成しておきます。
`monitoring`スキーマに`lock_test`という1カラムのテーブルを作ります。

```sql

create table monitoring.lock_test
as
select generate_series(1,3) as num
```


# 検証実施
`psycopg2`の`connection`の`close`方法によって`connection`オブジェクトがどう変わるのか？
実際にDB側でロック待ちが発生するのか？を検証していきます。

## ライブラリのインポートと接続情報の設定

```python

import psycopg2
import pandas as pd

# PostgreSQLの接続情報dict作成
param = {
    'port': '5432',
    'user': '*****',
    'password': '*****',
    'host': 'localhost',
    'dbname': '*****'
}
```

## DBがロックされる悪い例
以下を実行した場合、`connection`オブジェクトが`close`していないためDBがロックされます。

```python

# 悪い例(connectionオブジェクトをcloseしない場合)
conn = psycopg2.connect(**param)
query='''
select * from monitoring.lock_test;
'''
pd.read_sql(query,con=conn)
```
実際にDB側でロック待ちが発生していないか確認します。
以下のSQLを実行します。

```sql
SELECT l.pid, db.datname, c.relname, l.locktype, l.mode
FROM pg_locks l
        LEFT JOIN pg_class c ON l.relation=c.relfilenode
        LEFT JOIN pg_database db ON l.database = db.oid
ORDER BY l.pid;
```
実行結果：
<img width="423" alt="スクリーンショット 2019-09-09 20.57.58.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/337794/e7646d28-537f-badd-4e8c-72c6997a5a0b.png">
想定通り、lock_testテーブルで`Exclusivelock`になっています。
つまり、 **lock_testテーブルに関してDB側でロック待ちが発生しています。**
次に、`connection`オブジェクトの状態も確認してみます。

```python
print(conn)
```
実行結果：
<img width="759" alt="スクリーンショット 2019-09-09 20.58.56.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/337794/65af5464-4269-6608-6c6f-c8e2532f4699.png">
`closed`フラグが0になっており、`connection`オブジェクトが`close`されていないことが分かります。
（`connection`オブジェクトを`close`していないので当然なのですが、、、）

では、明示的に`connection`オブジェクトを`close`するとどうなるのか確認してみます。

```python
conn.close()
print(conn)
```
<img width="759" alt="スクリーンショット 2019-09-09 20.59.29.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/337794/ad8016ca-8d0c-9b80-1637-036fbbd8d38e.png">
`closed`フラグが1になっており、`connection`オブジェクトが`close`されています。
実際にDB側でロック待ちが解消していることを確認してみます。
<img width="416" alt="スクリーンショット 2019-09-09 20.59.53.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/337794/1b8ed9ca-dafb-0290-a320-8cddfea04f0a.png">
先ほどの`Exclusivelock`が消えており、ロック待ちが解消されていることがわかります。


## DBがロックされない良い例
いやいや普通はwith構文を使うだろう？
ってことでwith構文で`connection`オブジェクトを`close`してみます。

```python
# with構文でconnectionを勝手にcloseさせる
with psycopg2.connect(**param) as conn:
    query='''
    select * from monitoring.lock_test;
    '''
    pd.read_sql(query,con=conn)

print(conn)
```
実行結果：
<img width="759" alt="スクリーンショット 2019-09-09 20.58.56.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/337794/65af5464-4269-6608-6c6f-c8e2532f4699.png">
なんと、`connection`オブジェクトが`close`されていません。
これはどういうことだろうか？実際にDB側でロック待ちが発生しているかを確認してみます。
<img width="416" alt="スクリーンショット 2019-09-09 20.59.53.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/337794/1b8ed9ca-dafb-0290-a320-8cddfea04f0a.png">
ロック待ちは発生していません、、、
`connection`オブジェクトが`close`されているということでしょうか？
実際、lock_testテーブルを`drop`することもできたので、ロック待ちは本当に発生していないようです。
`connection`オブジェクトの`closed`フラグは当てにならないのだろうか、、、。

# まとめ
`closed`フラグが1にならない原因はよく分かりませんが、`psycopg2`の`connection`オブジェクトはwith構文で自動的に閉じるべきだと思います。
with構文を使わずに`close`を実行しても良いのですが、`close`し忘れるとDBにロック待ちが発生してしまうので、リスク大です。
（自戒の念を込めて、、、）