Help us understand the problem. What is going on with this article?

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カラムのテーブルを作ります。

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

検証実施

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

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

import psycopg2
import pandas as pd

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

DBがロックされる悪い例

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

# 悪い例(connectionオブジェクトをcloseしない場合)
conn = psycopg2.connect(**param)
query='''
select * from monitoring.lock_test;
'''
pd.read_sql(query,con=conn)

実際にDB側でロック待ちが発生していないか確認します。
以下の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;

実行結果:
スクリーンショット 2019-09-09 20.57.58.png
想定通り、lock_testテーブルでExclusivelockになっています。
つまり、 lock_testテーブルに関してDB側でロック待ちが発生しています。
次に、connectionオブジェクトの状態も確認してみます。

print(conn)

実行結果:
スクリーンショット 2019-09-09 20.58.56.png
closedフラグが0になっており、connectionオブジェクトがcloseされていないことが分かります。
connectionオブジェクトをcloseしていないので当然なのですが、、、)

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

conn.close()
print(conn)

スクリーンショット 2019-09-09 20.59.29.png
closedフラグが1になっており、connectionオブジェクトがcloseされています。
実際にDB側でロック待ちが解消していることを確認してみます。
スクリーンショット 2019-09-09 20.59.53.png
先ほどのExclusivelockが消えており、ロック待ちが解消されていることがわかります。

DBがロックされない良い例

いやいや普通はwith構文を使うだろう?
ってことでwith構文でconnectionオブジェクトをcloseしてみます。

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

print(conn)

実行結果:
スクリーンショット 2019-09-09 20.58.56.png
なんと、connectionオブジェクトがcloseされていません。
これはどういうことだろうか?実際にDB側でロック待ちが発生しているかを確認してみます。
スクリーンショット 2019-09-09 20.59.53.png
ロック待ちは発生していません、、、
connectionオブジェクトがcloseされているということでしょうか?
実際、lock_testテーブルをdropすることもできたので、ロック待ちは本当に発生していないようです。
connectionオブジェクトのclosedフラグは当てにならないのだろうか、、、。

まとめ

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

Why do not you register as a user and use Qiita more conveniently?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away