SQLAlchemyのトランザクションについて

SQLAlchemyのトランザクション周りで悩まされることがあったので、少しだけ調査・検証してみました。

 

all()などの取得系メソッドを使った時に、更新系の処理じゃないからコミットしなくて良いやと

思っていたのですが、公式ドキュメントの「Transactions and Connection Management」項目を読んでみると実際にはコミットする必要があるようです。

(Sessionオブジェクトがクローズまたは破棄された際にはSQLAlchemyにおけるトランザクション状態はリセットされるので、Sessionオブジェクトが短いライフサイクルの場合だとコミットしなくても通常通り動作すると思います。)

 

例えば下記のような状態で試してみると違いがわかります。

DBはMariaDB(10.0.33)を使用し、オートコミットはデフォルト値、タイムアウトを１分に設定しています。

MariaDB [(none)]> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

MariaDB [(none)]> set global wait_timeout=60;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show global variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 60    |
+---------------+-------+
1 row in set (0.00 sec)

SQLAlchemyでDBへアクセスするdb.py

import time
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker

Base = declarative_base()

class Names(Base):
    __tablename__ = 'names'
    id = Column(Integer, primary_key=True)
    name = Column(String(10))

if __name__ == '__main__':
    session = scoped_session(sessionmaker())
    engine = create_engine(
　　　'mysql://user:pass@127.0.0.1:3306/db_name?charset=utf8',
　　　echo=True, pool_recycle=30, pool_pre_ping=True)
    session.configure(bind=engine)
    Base.metadata.create_all(engine)

    res = session.query(Names).all()
    time.sleep(80)
    res = session.query(Names).all()

 

pool_recycleとpool_pre_pingを指定しているので、

time.sleepで待機した後もコネクションが再接続されて問題なくSQLが発行されるかと思いきや、

「(2013, 'Lost connection to MySQL server during query')」エラーが発生し、クエリ発行中にコネクションがロストしたことがわかります。

→恐らくpool_recycleとpool_pre_pingの設定が有効でも、トランザクション中のコネクションに対しては再接続処理をしてくれない為と思われる

 

ここで、下記のように取得の度にコミットをするようにすればエラーが発生しなくなります。

    res = session.query(Names).all()
    session.commit()
    time.sleep(80)
    res = session.query(Names).all()
    session.commit()

 

→MariaDB側でコネクションのタイムアウトが発生しても、トランザクションは完了しているので

コネクション再接続処理をしてくれる

  

DBの種類やAutoCommitモードの状態によっては、SELECT文単体でもトランザクションが張られたり張られなかったりで挙動が変わりそうですが、それはまた別の機会に・・

2018-02-13 20:18:44