This week's book giveaway is in the OO, Patterns, UML and Refactoring forum. We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line! See this thread for details.
Prabhakar Reddy Bokka wrote:what are the default isolation levels and where they are specified?
For isolation levels available to JDBC, see Oracle's JDBC Developer Guide (bottom of the page). Note that it may be very useful to read all of this book.
For description of these isolation levels in Oracle database, see Database Concepts Guide. Again, the complete guide is a very useful reading, even (or especially) if you are fluent in other databases. If you are on a different Oracle version, search for the documentation for your version.
Default isolation level for Oracle is TRANSACTION_READ_COMMITTED. Of course, frameworks could change this, so if you use any framework, you should consult its documentation. The same applies to other databases/drivers/frameworks - you need to look this up in the doco.
There is definitely not a single default isolation level for all databases. Moreover identically named isolation levels may work differently in different systems, eg. read commited in Oracle will never produce inconsistent results, which may not be true for databases that do not employ multiversioning. You need to read quite a lot about the database of your choice to understand the implications of the isolation levels, among other things.
While most of DDL operations in Oracle are atomic, the caveat is that any DDL operation issues an implicit commit. This may prove very confusing, as your transaction commits without calling commit() in your code.
However, you should seldom need to do DLL in an Oracle database. If your DDL concerns temporary tables, you definitely need to read this (search for documentation for your DB version if it is different from this one). Temporary tables in Oracle are different beasts from most other database systems. Very useful information can be also found on the AskTom site - search for "temporary table".
Edited: I've written a misleading description of SERIALIZABLE isloation level here previously. Here is what the Oracle documentation says on isolation levels:
The ISOLATION_LEVEL parameter specifies how transactions containing database modifications are handled. ISOLATION_LEVEL is a session parameter only, not an initialization parameter.
SERIALIZABLE indicates that transactions in the session use the serializable transaction isolation mode as specified in SQL92. That is, if a serializable transaction attempts to execute a DML statement that updates rows currently being updated by another uncommitted transaction at the start of the serializable transaction, then the DML statement fails. A serializable transaction can see its own updates.
READ COMMITTED indicates that transactions in the session will use the default Oracle Database transaction behavior. That is, if the transaction contains DML that requires row locks held by another transaction, then the DML statement will wait until the row locks are released.