Weak transaction isolation
Concurrency bugs caused by weak transaction isolation are not just a theoretical problem. They may cause customer data to be corrupted.
Many popular relational databases, which are usually considered ACID use weak isolation, so they would not necessarily have prevented these bugs from occurring.
What exactly the Isolation guarantee in the SQL standard means based on what they call “read phenomena”. There are three types of phenomena:
The reason they don’t use a higher default level is that both Repeatable Read and Serializable both introduce cases where a transaction cannot be completed and must be aborted (race conditions). In these cases, the DBMS aborts the transaction, rolls it back, and returns an error.
When this happens, the application has to know how to retry the transaction. In some cases it may be difficult to retry a transaction if it had side effects on the application’s side: talking to external services or other data stores. In these cases, the application would have to use something like a two-phase commit protocol, or else manually roll back the side effects.
There are two solutions to this: either you use row locks (probably a shared lock, but possibly exclusive) to prevent any concurrent access to the rows, or you use stricter transaction isolation.
Details: http://www.michaelmelanson.net/2014/12/01/transactions/
Many popular relational databases, which are usually considered ACID use weak isolation, so they would not necessarily have prevented these bugs from occurring.
What exactly the Isolation guarantee in the SQL standard means based on what they call “read phenomena”. There are three types of phenomena:
- Dirty reads – If another transaction writes, but does not commit, during your transaction, is it possible that you will see their data?
- Non-repeatable reads – If you read the same row twice, is it possible that you might get different data the second time?
- Phantom reads – If you read a collection of rows twice, is it possible that different rows will be returned the second time?
- Read Uncommitted – A transaction can see the results of another transaction’s INSERT or UPDATE queries before they are committed. This allows all read phenomena.
- Read Committed – A transaction is guaranteed to only see rows that are committed. This prevents dirty reads but allows the other phenomena.
- Repeatable Read – A transaction is gets a snapshot of all rows it reads during the transaction. This prevents dirty reads and non-repeatable reads, but allows phantom reads.
- Serializable – A transaction gets a “range lock” any time it queries a collection of rows, guaranteeing that it sees a snapshot of that data. This prevents phantom reads as well as dirty reads and non-repeatable reads.
The reason they don’t use a higher default level is that both Repeatable Read and Serializable both introduce cases where a transaction cannot be completed and must be aborted (race conditions). In these cases, the DBMS aborts the transaction, rolls it back, and returns an error.
When this happens, the application has to know how to retry the transaction. In some cases it may be difficult to retry a transaction if it had side effects on the application’s side: talking to external services or other data stores. In these cases, the application would have to use something like a two-phase commit protocol, or else manually roll back the side effects.
There are two solutions to this: either you use row locks (probably a shared lock, but possibly exclusive) to prevent any concurrent access to the rows, or you use stricter transaction isolation.
Details: http://www.michaelmelanson.net/2014/12/01/transactions/
Comments
Post a Comment