SQLAlchemyのautocommitについて

概要

SQLAlchemyのautocommitについて理解できてなかったので調査しました。

調査した時の各バージョン

• SQLAlchemy : 1.3.3
• MySQL : 8.0.16

autocommitの疑問点

autocommitはSQLAlchemyのSessionが持っている機能です。それ自体は問題ないのですが、例えばDB側(MySQLやPostgreSQL等)もautocommit機能を持っているのでSQLAlchemyのautocommitの有効/無効はそれらと連携している？というのが疑問でした。

session = Session(
    bind = ENGINE,
    autocommit = False,
    autoflush = True)

例えば上記のようにSessionのautocommitをFalseに指定したら、DB側のautocommitも無効になる？又は無効にしておかなければならない？等の疑問が沸きました。

まず結論

SQLAlchemyのautocommitは、MySQLのデフォルトのautocommitの有効/無効に関わらず動作します。

他にPostgreSQLでも簡単に検証してみましたが同じ結果になりました。（こちらはあまりがっつりは検証してませんが・・）
それ以外のDBについても未検証ですが同じような結果になりそう。※要検証

ではどのようにしてautocommitを実現しているのかMySQLでの検証結果から説明していきます。

検証用modelの作成

シンプルなuser_listテーブルを作成しこのテーブルをSQLAlchemyから操作することでautocommitの挙動を確認します。

model.py

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String

Base = declarative_base()

class User(Base):
    __tablename__ = 'user_list'
    id = Column('id', Integer, primary_key=True)
    name = Column('name', String(100))

Sessionのautocommit=Falseの場合

以下のコードを実行した場合にMySQLに対してどのようなSQLを発行するか検証してみました。

main.py

from sqlalchemy.orm import Session
from model import User

# ～ 中略 ～

session = Session(
    bind = ENGINE,
    autocommit = False, # autocommitを無効
    autoflush = True)

#1 user_listテーブルのレコード数確認
user_list = session.query(User)
print(user_list.count()) # ⇒ "0"出力

#2 1レコード追加
user = User(id = 1, name = 'hoge')
session.add(user)

#3 user_listテーブルのレコード数確認
print(user_list.count()) # ⇒ "1"出力

#4 コミット
session.commit()

発行されたSQL

-- Sessionの初期化処理：autocommitを0(無効)にする
set autocommit=0
-- rollbackすることで、次のSQLから新規トランザクション開始
rollback
--#1 のSQL発行
SELECT count(*) AS count_1 FROM ( ～略～ ) AS anon_1
--#2 のSQL発行
INSERT INTO user_list (id, name) VALUES (1, 'hoge')
--#3 のSQL発行
SELECT count(*) AS count_1 FROM ( ～略～ ) AS anon_1
--#4 のSQL発行
commit

※注1 Sessionの初期化処理ではいくつかSQLを発行していますが、autocommitに関係しているところだけ抜粋しています
※注2 上記のSQLは、MySQLのデフォルトのautocommitが有効/無効に関わらず同じものが発行されていました

Sessionの初期化処理としてset autocommit=0を発行することでautocommit=False(無効)を実現しています。
これはまあわかりやすいです。

Sessionのautocommit=Trueの場合

続いてautocommit=Trueの場合です。

main.py

from sqlalchemy.orm import Session
from model import User

# ～ 中略 ～

session = Session(
    bind = ENGINE,
    autocommit = True, # autocommitを有効
    autoflush = True)

#1 user_listテーブルのレコード数確認
user_list = session.query(User)
print(user_list.count()) # ⇒ "0"出力

#2 1レコード追加
user = User(id = 1, name = 'hoge')
session.add(user)

#3 user_listテーブルのレコード数確認
print(user_list.count()) # ⇒ "1"出力

発行されたSQL

-- Sessionの初期化処理：autocommitを0(無効)にする
set autocommit=0
-- rollbackすることで、次のSQLから新規トランザクション開始
rollback
--#1 のSQL発行
SELECT count(*) AS count_1 FROM ( ～略～ ) AS anon_1
-- rollbackすることでトランザクションをリセット
rollback
--#2 のSQL発行
INSERT INTO user_list (id, name) VALUES (1, 'hoge')
-- insert文の場合はrollbackの前にcommitを発行
commit
-- rollbackすることでトランザクションをリセット
rollback
--#3 のSQL発行
SELECT count(*) AS count_1 FROM ( ～略～ ) AS anon_1
-- rollbackすることでトランザクションをリセット
rollback

※注1 Sessionの初期化処理ではいくつかSQLを発行していますが、autocommitに関係しているところだけ抜粋しています
※注2 上記のSQLは、MySQLのデフォルトのautocommitが有効/無効に関わらず同じものが発行されていました

autocommit=Trueにしても、初期化処理でset autocommit=0を発行することは変わりません。
ではどうやってautocommit=Trueを実現しているかというと、上記の発行されたSQLにあるようにSQLを発行するごとにrollbackすることでautocommit=Trueを実現しているようです。
加えてinsert文等はcommitを先に発行することで即時commitしています。

Sessionのautocommit=Trueで且つSession.begin()使用の場合

最後に、autocommit=Trueで且つSession.begin()でトランザクションの開始を入れてみた場合はどうか

main.py

from sqlalchemy.orm import Session
from model import User

# ～ 中略 ～

session = Session(
    bind = ENGINE,
    autocommit = True, # autocommitを有効
    autoflush = True)

#0 トランザクションの開始
session.begin()

#1 user_listテーブルのレコード数確認
user_list = session.query(User)
print(user_list.count()) # ⇒ "0"出力

#2 1レコード追加
user = User(id = 1, name = 'hoge')
session.add(user)

#3 user_listテーブルのレコード数確認
print(user_list.count()) # ⇒ "1"出力

#4 コミット
session.commit()

発行されたSQL

-- Sessionの初期化処理：autocommitを0(無効)にする
set autocommit=0
-- rollbackすることで、次のSQLから新規トランザクション開始
rollback
--#1 のSQL発行
SELECT count(*) AS count_1 FROM ( ～略～ ) AS anon_1
--#2 のSQL発行
INSERT INTO user_list (id, name) VALUES (1, 'hoge')
--#3 のSQL発行
SELECT count(*) AS count_1 FROM ( ～略～ ) AS anon_1
--#4 のSQL発行
commit

※注1 Sessionの初期化処理ではいくつかSQLを発行していますが、autocommitに関係しているところだけ抜粋しています
※注2 上記のSQLは、MySQLのデフォルトのautocommitが有効/無効に関わらず同じものが発行されていました

autocommit=Falseの場合と同じSQLが発行されていました。
おそらく、session.begin()を実行するとsession.commit()が実行されるまではSQL発行毎のrollbackやcommitは抑制する、というような挙動になっているのではないかと思います。

なぜMySQLのautocommitを使用しないのか

パッと考えただけだと、autocommit=Trueにしたらset autocommit=1を発行すればいいじゃん！と思うんですが、実際は常にautocommit=0を発行して、rollback/commit発行の有無でautocommitの有効/無効を実現していました。
推測としては、MySQL(DB側)のautocommitの仕様に引きずられたくなくて、SQLAlchemyとしてのautocommitを実現したかった、という感じでしょうか。
ちなみにPostgreSQLで検証した際も細部は違いますが同じようなSQLを発行してたので(SQL毎にrollbackとか)、他のDBでも同じような挙動になってそう。。機会があったら検証してみます。