5 ms·

SQL transaction isolation levels explained

73 points by astdb 10y ago

kelvich 10y ago
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-critique-of-ansi-sql-isolation-levels/ https://www.microsoft.com/en-us/research/publication/a-criti... [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 10y ago
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 10y ago
  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.

73 points by astdb 10y ago

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-critique-of-ansi-sql-isolation-levels/ https://www.microsoft.com/en-us/research/publication/a-criti... [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 10y ago

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 10y ago
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.