CODEWITHSUNDEEP
oracle-databasejdbc
Parth
Parth

Reputation: 519

Oracle DB's Isolation levels and JDBC's TRANSACTION_NONE attribute

I am trying to understand the isolation levels in Oracle. I have a question about the NONE isolation level.

I can understand the other 4 cases and what it means. Basically each one has a type of lock that gets applied to either the row or table for locking access to other transactions. I tested this in code and I see the updates blocking on other transactions running on the same row.

If I set the isolation level to none, does it mean that if there are two different processes writing to the same row in database, it will overwrite each other or may be corrupt the data? What does it mean to have no transactions? And what implications could it have?

ref: http://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html#transactions_data_integrity

Upvotes: 0

Views: 2463

Answers (2)

gpeche
gpeche

Reputation: 22524

The document you link to talks about RDBMS in general. Oracle is designed in a specific way (called multiversion concurrency control, or MVCC) that further restricts the isolation levels you effectively work with. This is hinted in your linked doc:

Note: A JDBC driver might not support all transaction isolation levels. If a driver does not support the isolation level specified in an invocation of setTransactionIsolation, the driver can substitute a higher, more restrictive transaction isolation level. If a driver cannot substitute a higher transaction level, it throws a SQLException. Use the method DatabaseMetaData.supportsTransactionIsolationLevel to determine whether or not the driver supports a given level.

To summarize, in Oracle you have only two effective isolation levels:

  1. TRANSACTION_READ_COMMITTED
  2. TRANSACTION_SERIALIZABLE (and this does not work as you intuitively might think)

To find out what Oracle is actually doing, you can use this mapping :

  • TRANSACTION_NONE becomes TRANSACTION_READ_COMMITTED
  • TRANSACTION_READ_UNCOMMITTED becomes TRANSACTION_READ_COMMITTED
  • TRANSACTION_READ_COMMITTED becomes TRANSACTION_READ_COMMITTED
  • TRANSACTION_REPEATABLE_READ becomes TRANSACTION_SERIALIZABLE
  • TRANSACTION_SERIALIZABLE becomes TRANSACTION_SERIALIZABLE

UPDATE

Actually this is a bit wrong: javadoc says that you cannot set the isolation level to TRANSACTION_NONE. See also this question

Upvotes: 6

Justin Cave
Justin Cave

Reputation: 231831

JDBC, a cross-database API standard that is part of the Java language owned by Oracle Corp., supports a number of transaction isolation levels that Oracle the relational database also owned by Oracle Corp. does not. Specifically, the Oracle database only supports the read committed and serializable transaction isolation levels (Oracle documentation will also talk about a non-standard read only transaction isolation level which is really serializable without the ability to make changes). So in Oracle, you cannot use the TRANSACTION_NONE isolation level.

In databases that do support TRANSACTION_NONE, each statement would essentially be its own transaction-- your changes would be committed as soon as the statement completed successfully. Realistically, if you have two sessions modifying the same row with TRANSACTION_NONE, whichever update happened last would rule but you wouldn't corrupt your database.

The real impact of not having transactions is that it becomes very easy to introduce logical corruption into your data. In the classic case, you're operating a bank with accounts A and B. A wants to transfer $100 to B. In order to do that, you would generally do something like one DML statement to record a transaction that removes $100 from A's account and a second DML statement to record a transaction that adds $100 to B's account. If you don't do this inside a transaction, however, eventually you'd come across a situation where the first statement succeeds, and A finds himself $100 poorer, while the second statement fails, and B finds himself no richer, and $100 has magically disappeared from your bank. This is logical corruption in the sense that you can look at the transaction history and conclude that something went wrong but it is not corruption in the sense of your database not knowing what the current value is or not being able to operate on the data.

Upvotes: 1

Related Questions