1

The quote below from Wikipedia (here) says that READ COMMITTED doesn't cause serialization failure! Can you please explain the reason? Which isolation levels cause serialization failure?

Under multiversion concurrency control, at the SERIALIZABLE isolation level, both SELECT queries see a snapshot of the database taken at the start of Transaction 1. Therefore, they return the same data. However, if Transaction 2 then attempted to UPDATE that row as well, a serialization failure would occur and Transaction 1 would be forced to roll back.

At the READ COMMITTED isolation level, each query sees a snapshot of the database taken at the start of each query. Therefore, they each see different data for the updated row. No serialization failure is possible in this mode (because no promise of serializability is made), and Transaction 1 will not have to be retried.

Improve this question
2
  • 1
    This is not clear. What is your 1 (specific researched non-duplicate) question? What do you mean by "serialization failure"? As to what the article means, we don't know, because it's sloppy. But it seems like they mean, an error that the DBMS could not produce a serializable schedule. It seems to be trying to say that if you get an error it won't be about serializability--"No serialization failure is possible in this mode"--because the user having picked some non-SERIALIZED mode, the DBMS is not trying to produce serializable schedules--"because no promise of serializability is made".
    – philipxy
    Jan 17, 2021 at 21:12
  • 1
    That article is a mess.
    – philipxy
    Jan 17, 2021 at 23:28

1 Answer 1

Reset to default
6

It's impossible to give a brief answer to this.

I suggest that you start reading here:

If you can read and grasp all of that material (and the links within - the book is the cherry on the cake but not strictly required just to answer this question), you'll be well on the way to understanding transactions and isolation levels!

Edit:

In response to the OP's comments - by serialization failure, what I believe the author of the wiki (which is here - please quote references in future if possible) means is that in the first case, there will be a failure of T1 due to the isolation level being set to SERIALIZABLE - whereas, in the second case, a level of READ COMMITTED will mean that the T1 will not fail.

The important think to realise about SERIALIZABLE transactions is that (from here):

a transaction schedule is serializable if its outcome (e.g., the resulting database state) is equal to the outcome of its transactions executed serially, i.e. without overlapping in time.

So, T1 will fail because its entire view of the data can't be consistent over the entire time (i.e. for all of the queries) of the transaction due to the modification by T2.

I have found (and this confused me for a while until I teased it through) is that there is a BIG DIFFERENCE between a query AND a transaction - a query may or may not be a proper subset of a transaction. On most modern RDBMS's, if you don't explicitly specify a BEGIN TRANSACTION or an isolation level, the default is that:

  • a) a transaction is started for you automatically beneath the hood and,

  • b) that transaction is read-committed - which normally doesn't matter much since it's one query.

The distinction really becomes important when you start running multi-query transactions - that's when the wheat is sorted from the chaff and the developer has to understand what's going on in the server re. transaction isolations levels

There is, as with all things in IT and databases in particular there are balances, trade-offs and compromises. Why, you might ask, is SERIALIZABLE not the default and/or not always specified? The answer is simple: Performance - there's your trade-off!

I would urge you now to read the references I've provided - do some experiments with two CLI windows on your server of choice (or multiple servers if you really want to get into it!) running some of the code referenced!

Improve this answer
11
  • 1
    @learn9909 - that will depend on the internals of the database server - I believe that Microsoft implements both a locking and an MVCC strategy whereas Oracle, PostgreSQL and MySQL (InnoDB) have an exclusively MVCC one - but don't take my word for it - again, kick-start the search engine and/or hit the books - it's a complex topic but well worth investigating!
    – Vérace
    Jan 17, 2021 at 13:31
  • 1
    I found the default to be meaningless due to pool bugs anyway. Running the SET TRANSACTION ISOLATION LEVEL ... command each time you open a connection checks if the connection is in a good state or not.
    – Joshua
    Jan 17, 2021 at 21:05
  • 1
    BTW, you can get serialization failure within a query. I think I've seen it and I know I've chased bugs where that was the only reasonable explanation for the failure mode where the bug origin was a messed-up isolation level.
    – Joshua
    Jan 17, 2021 at 21:06
  • 2
    The asker should tell us what they mean by "serialization failure", because they are using it. If they only want to know what the wiki means by it, we can't read the wiki writer's mind, and the asker hasn't asked an answerable question. If the asker has an interpretation or hypothesis re the article, they should write it & a question about it clearly. The phrase isn't a specific technical term. Simply googling "serialization failure" turns up a DBMS-specific error, but they don't reflect that either.
    – philipxy
    Jan 17, 2021 at 21:32
  • 1
    @learn9909 Why are you asking that after reading my comments on this answer & the question? "The phrase isn't a specific technical term" "The asker should tell us what they mean" "If they only want to know what the wiki means by it, we can't read the wiki writer's mind" "What is your 1 (specific researched non-duplicate) question?" Etc.
    – philipxy
    Jan 17, 2021 at 22:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.