I have read about 4 levels of isolation:
Isolation Level Dirty Read Nonrepeatable Read Phantom Read
READ UNCOMMITTED Permitted Permitted Permitted
READ COMMITTED -- Permitted Permitted
REPEATABLE READ -- -- Permitted
SERIALIZABLE -- -- --
I want to understand the lock each transaction isolation takes on the table
READ UNCOMMITTED - no lock on table
READ COMMITTED - lock on committed data
REPEATABLE READ - lock on block of sql(which is selected by using select query)
SERIALIZABLE - lock on full table(on which Select query is fired)
below are the three phenomena which can occur in transaction isolation
Dirty Read– no lock
Nonrepeatable Read – no dirty read as lock on committed data
Phantom Read – lock on block of sql(which is selected by using select query)
I want to understand where we define these isolation levels : only at jdbc/hibernate level or in DB also
PS: I have gone through the links in Isolation levels in oracle, but they looks clumsy and talk on database specific
Best Answer
For example, you have 3 concurrent processes A, B and C. A starts a transaction, writes data and commit/rollback (depending on results). B just executes a
SELECT
statement to read data. C reads and updates data. All these process work on the same table T.WHERE aField > 10 AND aField < 20
, A inserts data whereaField
value is between 10 and 20, then B reads the data again and get a different result.Using JDBC, you define it using
Connection#setTransactionIsolation
.Using Hibernate:
Where
Hibernate configuration is taken from here (sorry, it's in Spanish).
By the way, you can set the isolation level on RDBMS as well:
SET ISOLATION TO DIRTY READ
sentence.)and on and on...