субота, 27 квітня 2013 р.

Isolation level in RDBMS

In the famous ACID abbreviation, I means Isolation (Isolation level). According to wikipedia, isolation is a property that defines how/when the changes made by one operation become visible to other concurrent operations.

To understand isolations level better, lets review the main colitions that may happend in concurrent
  • lost update - A second transaction writes a second value of a data on top of a first value written by a first concurrent transaction, and the first value is lost to other transactions running concurrently which need, by their precedence, to read the first value.
  • dirty read - insert or update data by transaction that will be rollbacked further
  • non-repeatable read - first transaction read data, then read them again and the same data is modified
  • phantom reads - on the second read in transaction, new data is appearing

So, now that's easy to describe isolation levels with the following table:
Isolation levelJDBC equivalentDirty readsNon-repeatable readsPhantomsLost update
NoneTRANSACTION_NONEmay occurmay occurmay occurmay occur
Read UncommittedTRANSACTION_READ_UNCOMMITTEDmay occurmay occurmay occur-
Read CommittedTRANSACTION_READ_COMMITTED-may occurmay occur-
Repeatable ReadTRANSACTION_REPEATABLE_READ--may occur-

In Java world you can set up any of them on JDBC driver, however there is not guarantee that this level will be applied! Test your database programmatically using the supportsTransactionIsolationLevel() method in java.sql.DatabaseMetaData to be sure.

RDBMSs are supporting isolation levels according to the following table:

Isolation level MySQL (InnoDb)  Oracle   DB2   H2   Postgres 
Read Uncommitted             +      -    +   +       +
Read Committed             +      +    +   +       +
Repeatable Read             +      +    +   -       +
Serializible             +      +    +   +       +

More about transactions in MySQL
More about transactions in Oracle
More about transaction isolation level in IBM DB2
More about transaction in H2
More about transactions in Postgres

Немає коментарів:

Дописати коментар