SQLAlchemyのautocommitについて

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

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

* SQLAlchemy : 1.3.3
* MySQL      : 8.0.16

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

```python
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の挙動を確認します。

```python: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を発行するか検証してみました。

```python: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**

```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の場合です。

```python: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**

```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()でトランザクションの開始を入れてみた場合はどうか

```python: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**

```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でも同じような挙動になってそう。。機会があったら検証してみます。