search

prisma / quaint

SQL Query AST and Visitor for Rust
Apache License 2.0
583 stars 61 forks source link

Allow setting the SQL Server isolation level #190

Closed pimeys closed 3 years ago

pimeys commented 3 years ago

Helps our tests to use SNAPSHOT isolation, preventing deadlocks.

yoshuawuyts commented 3 years ago

@pimeys can you say more on why you're choosing SNAPSHOT isolation over SERIALIZABLE?

pimeys commented 3 years ago

SNAPSHOT isolation uses MVCC locking instead of the default and old 2-phase locking of READ COMMITTED. All our open source databases have been using MVCC since forever, but due to backwards compatibility reasons the default of SQL Server is still READ COMMITED.

This means when anybody writes things to the database, as in our case in the tests to the schema, the reads are locked and if we're unlucky, we get a deadlock and need to retry transactions.

With MVCC locking there's a separate version table that gives much more fine-grained locking, preventing deadlocks and giving fast concurrency. The downside of this then is how the tempdb of the server can grow, due to it needing to store the version information of the row before collected.

pimeys commented 3 years ago

Fixed all.

pimeys commented 3 years ago

And why not SERIALIZABLE

Range locks are placed in the range of key values that match the search conditions of each statement executed in a transaction. This blocks other transactions from updating or inserting any rows that would qualify for any of the statements executed by the current transaction. This means that if any of the statements in a transaction are executed a second time, they will read the same set of rows. The range locks are held until the transaction completes. This is the most restrictive of the isolation levels because it locks entire ranges of keys and holds the locks until the transaction completes. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.

We don't need this much of isolation for tests. We want as little locks as possible due to the tests running in parallel.