SQLAlchemyを用いたPostgreSQLにおける安全な更新操作

PostgreSQLでデフォルトの分離モードを使用している場合.Webアプリケーションでデータベースを更新しようとすると、酸取引は競合状態から安全ではありません.つ以上のプロセスを実行してコードを実行する場合.(複数のプロセスはWebサーバではとても一般的です).

from sqlalchemy import create_engine
from sqlalchemy.orm import Session
# setup
engine = create_engine(...)
session = Session(engine)
# read from the table Account
account = session.query(Account).get(1)
# modify the record, account is decimal
account.amount = account.amount + 100
session.commit()

なぜこれは安全ではないのですか?これはSELECTが行レベルでロックを発生しないからです.更新が発生した場合にのみブロックをロックします.FOR UPDATE/FOR NO KEY UPDATE ). この条件で競合状態が発生する可能性があります.
トランザクション1 ( T 1 )
トランザクション2 ( T 2 )
スタート
選択金額
スタート
数量＋100
選択金額
更新金額
数量＋100
コミット
更新金額
コミットSAME value as T1T 1がT 2読み込み前にコミットしないので、T 2はT 1と同じ値を選択します.これは最終的には再び同じことを行う.
したがって、デフォルトの分離モードでこの状況を防ぐ方法.( read commit mode )

解決1:ちょうど読みません
上記のスニペットは、アンチパターンread-modify-write . それを防ぐための1つの方法は、カラム値で直接値を読み書きしないことです.与えられた読み込みは、このシナリオでは必要ありません.

# same session setting as above
session.query(Account).filter_by(id=1)\
     .update({"amount": Account.amount + 100})
session.commit()

ソリューション2 :アップデートロック
いくつかのシナリオでは、複雑な変更を行うには、まず最初に読む必要があります.この場合、データベースから読み込むときに更新ロックを使用できます.SQLAlchemyでは、と呼ばれる方法がありますwith_for_update を使用すると、FOR UPDATE ロック.FOR UPDATE ロックは自己互換性がありません.このトランザクションがロックを解放するまで、別のトランザクションが待機しなければなりません.

# same session setting as above
# locks the row that id = 1
account = session.query(Account).filter_by(id=1)\
     .with_for_update().one()
account.amount = account.amount + 100
session.commit()  # save and release the lock

解決3 :バージョン追跡(楽観的なロック)
同じ行の更新を実行する他のすべてのプロセスをロールバックしたい場合.この列の更新を追跡するには、テーブルにバージョン列を追加できます.いくつかの行でバージョンvを持っているとしましょう.行を更新したい場合は、バージョンVとともに行を検索し、バージョンをV + 1に更新します.

update account set version = v + 1, ... where version = v ...;

これは私たち自身を実装するのに非常に迷惑です.SqlAlchemyでは、Mapperでバージョン列名を設定することができます.

class Account(Base):
    __tablename__ = "account"
    ...
    version = Column(Integer, nullable=False)

    __mapper_args__ = {"version_id_col": version}

def version_tracking(change):
    try:
        account = session.query(Account).get(1)
        account.amount = account.amount + change
        print_account(account, change)
        session.commit()
    except StaleDataError:
        print("someone has changed the account, plz retry.")
        # some actions...

上記のすべての解決策は、Postgresのデフォルト分離モデルであるread commit modeを使用していると仮定します.また、ターゲット行を選択した場合、任意の集約を使用していないと仮定します.トランザクションがシリアルに動作するように動作するように、厳密な分離モードを使用するなど、同じ問題を防ぐ方法もいくつかあります.しかし、我々はこの記事でこれを通過しません.

を参照してください.
All Source Code
SQLAlchemy for update (kite)
SQLAlchemy Version Tracking
PSQL Transaction Isolation
PSQL Locking