
SQL transaction isolation levels explained - astdb
https://elliot.land/sql-transaction-isolation-levels-explained
======
kelvich
There are some controversy about definitions of isolation levels in SQL
standard. For example absence of mentioned phenomenas doesn't guarantees
serialisability, as it shown in Berenson paper [1]. Implementation independent
description of weak isolation levels can be found in Adya phd thesis [2] (and
that's a tough read). Also nice evaluation of modern databases in terms of [2]
can be found in Martin Kleppmann's github [3].

[1] [https://www.microsoft.com/en-
us/research/publication/a-criti...](https://www.microsoft.com/en-
us/research/publication/a-critique-of-ansi-sql-isolation-levels/) [2]
[http://pmg.csail.mit.edu/papers/adya-
phd.pdf](http://pmg.csail.mit.edu/papers/adya-phd.pdf) [3]
[https://github.com/ept/hermitage](https://github.com/ept/hermitage)

------
IgorPartola
Correct me if I'm wrong, but I thought that under Repeatable Reads you also
could get rollbacks and had to make sure that the transaction would be
retried?

Also, I would like to point out that at least MySQL sand Postgres also support
explicit named locks. This can often be a better choice than trying to rely on
the implicit locks associated with the isolation levels when dealing with
large "objects" \- that is things like an order on a shopping site that spans
many tables and rows. Unless you are very careful, concurrent transactions
against things like this would access them in different order, causing
deadlocks. Creating an explicit named lock for the whole object is often a
very good way to fix this.

~~~
chris_wot
You can get rollbacks anywhere. Repeatable Read isn't going to stop that...

Under repeatable read, read and write locks will be held till then end of the
transaction. You can get deadlocks in SQL Server if you have a transaction you
run over multiple sessions that do a SELECT on a set of rows that you then
later update.

This occurs because the SELECT puts shared locks on the rows, then attempts to
upgrade this lock to an update lock with the UPDATE statement. However, as
another transaction also holds a shared lock on the same rows, then your
transaction waits for the shared lock to be ended from the other transaction
before it runs the update -and of course so does the other transaction, so you
get a read/update deadlock.

You get around that by forcing the SELECT to use an update lock.

You can also get a cycle deadlock.

I'm not entirely following what you mean by named locks vs. implicit locks.
This sounds like lock hinting, where you specify the type of lock that each
query runs. Isolation levels only really specify what locks are held for the
duration of the transaction.

You can actually enforce a serializable isolation level on an individual table
in SQL Server from a transaction that runs a different isolation level - to
prevent phantom reads in repeatable read transactions use the hint WITH
(UPDLOCK, HOLDLOCK) - the HOLDLOCK will put a key range lock on the table.
This prevents anyone inserting rows into that range, thus ensuring you always
get the same results from the SELECT on that particular range you care about.

