Isolation levels are really a poor design decision, because they imply the use of locks. Serializable is great, but impossible to implement efficiently. Repeatable read, read committed, and read uncommitted can be implemented efficiently, but allow for various unpleasant isolation artifacts.
The one we're implementing is really the one everyone wants - snapshot isolation. It can be implemented very efficiently, and is stronger than repeatable read, read committed, and read uncommitted (so you should never want these three). It's not as strong as serializable, but nobody can give you a scalable serializable isolation level.
Snapshot isolation also guarantees consistency, but requires all transactions to be idempotent (so they could be rerun in case of a conflict). It's the best of both worlds, in practice most other databases already behave this way anyway.
so for inserts do you need to have some kind of uniqueness constraint that makes sure that a repeated insert is rejected (the first example that came into my head when i read "idempotent" was a simple insert, which isn't, as far as i can tell)?
[sorry if this seems like an interrogation - it's just interesting stuff you're doing...]
Essentially, it means that any transaction might potentially be rolled back and rerun. This isn't a problem for SQL, but suppose I select some stuff, get back into the host programming language, fire off some rockets into space from the Kennedy Space Center, and then insert some data about the launch into the database. This is a big problem, because if the insertion fails because of potential conflicts, the whole thing needs to be rolled back (including the rocket launch), and rerun. A lot of software is written to account for this (i.e. don't perform any external state modification you can't roll back until you've confirmed the transaction is committed), but a lot of software isn't. To really have great isolation and performance, you need to write software this way. For people that don't, we'll support serializable level, but there are very strong limitations as to how efficient this can be.
Comments
Isolation levels are really a poor design decision, because they imply the use of locks. Serializable is great, but impossible to implement efficiently. Repeatable read, read committed, and read uncommitted can be implemented efficiently, but allow for various unpleasant isolation artifacts.
The one we're implementing is really the one everyone wants - snapshot isolation. It can be implemented very efficiently, and is stronger than repeatable read, read committed, and read uncommitted (so you should never want these three). It's not as strong as serializable, but nobody can give you a scalable serializable isolation level.
Snapshot isolation also guarantees consistency, but requires all transactions to be idempotent (so they could be rerun in case of a conflict). It's the best of both worlds, in practice most other databases already behave this way anyway.
so for inserts do you need to have some kind of uniqueness constraint that makes sure that a repeated insert is rejected (the first example that came into my head when i read "idempotent" was a simple insert, which isn't, as far as i can tell)?
[sorry if this seems like an interrogation - it's just interesting stuff you're doing...]
Essentially, it means that any transaction might potentially be rolled back and rerun. This isn't a problem for SQL, but suppose I select some stuff, get back into the host programming language, fire off some rockets into space from the Kennedy Space Center, and then insert some data about the launch into the database. This is a big problem, because if the insertion fails because of potential conflicts, the whole thing needs to be rolled back (including the rocket launch), and rerun. A lot of software is written to account for this (i.e. don't perform any external state modification you can't roll back until you've confirmed the transaction is committed), but a lot of software isn't. To really have great isolation and performance, you need to write software this way. For people that don't, we'll support serializable level, but there are very strong limitations as to how efficient this can be.
ah, ok, i misunderstood how broadly you were using the word "transaction". makes sense, thanks.