My web application uses SQLAlchemy with a PostgreSQL database. Now there is a need to use the equivalent of Microsoft SQL Server's SNAPSHOT transaction isolation level, but I did not find a solution in the SQLAlchemy v1.4.44 documentation.

CodePudding user response：

Microsoft SQL Server's “snapshot isolation” is documented as

The term "snapshot" reflects the fact that all queries in the transaction see the same version, or snapshot, of the database, based on the state of the database at the moment in time when the transaction begins. No locks are acquired on the underlying data rows or data pages in a snapshot transaction, which permits other transactions to execute without being blocked by a prior uncompleted transaction. Transactions that modify data do not block transactions that read data, and transactions that read data do not block transactions that write data, as they normally would under the default READ COMMITTED isolation level in SQL Server. This non-blocking behavior also significantly reduces the likelihood of deadlocks for complex transactions.

Snapshot isolation uses an optimistic concurrency model. If a snapshot transaction attempts to commit modifications to data that has changed since the transaction began, the transaction will roll back and an error will be raised. You can avoid this by using UPDLOCK hints for SELECT statements that access data to be modified.

That behavior is exactly what you get if you use the transaction isolation level REPEATABLE READ in PostgreSQL, so that's what you should use. Note that PostgreSQL always uses multiversioning, so there is no way in PostgreSQL to emulate the read locks that Microsoft SQL server takes for other isolation levels.