Database Systems

Transaction Isolation Levels:- Defines the scope of data visibility between 2 or more concurrent transactions.



READ UNCOMITTED 


Transactions can read uncommitted (garbage/dirty) data. 
Provides higher throughput. No Locking/Synchronization.
Dirty read is possbile.
Not supported in Oracle


READ COMITTED


Transactions can read only commited data but that data can change during the transaction life cycle.
Provides higher throughput/ No locking Synchronization.
On every read you may get different data.
Oracle Database meets the READ COMMITTED isolation standard. 
This is the default mode for all Oracle Database applications.


REPEATABLE READ


Transactions will get the same data (in rows) for every read. But there may be new rows.
No protection against INSERTS from other transactions.
At any point in a trasaction the read rows are locked so on future reads there may be new rows.
High chances of DEADLOCK. Performance hit (Low throughput)
Oracle Database does not normally support this isolation level, except as provided by SERIALIZABLE.


SERIALIZABLE


Transactions lock/synchronize on the table. 
At any point in the given transaction always same data is read. There are no new rows.
Protects against INSERTS from other transactions.
Very High chances of DEADLOCK. Very low throughput.
Oracle Database does not normally support this isolation level, except as provided by SERIALIZABLE.


Practically, transaction isolation level => READ COMMITTED on the database + use SELECT FOR - UPDATE OR TABLE LOCKING (for data synchronization)


http://docs.oracle.com/cd/B13789_01/appdev.101/b10795/adfns_sq.htm#1024753




SELECT FOR - UPDATE


Use it for locking selective rows that you are working on.
The lock is released on commit or rollback.


-------------------------------------------------------------------------------------------------------------------------------------------------------


CURSORS - is a view of data (window of data)


IMPLICIT -  DML's are implicit cursor (Select/update). DB behind the scenes will start a cursor for every SELECT or UPDATE


EXPLICIT - They can be used in the apps like ProC or in Stored Procs etc where you explicityly define a cursor and then provide a query for data selection.




STATIC  - opens a new cursor every time a new query comes in to the DB.


DYNAMIC - Reuses the same cursor for the similar (with parameters) or same queries.
Database System will create such cursors (process) in shared area. By doing this Databases optimize resource allocation by sharing resources.


Statement s = new Connection.createStatement()

Will always create a new static cursor
Database will never spend time looking for whether the cursor (for query) already exists.

Prepared Statement p = Connection.prepareStatement()

Will check with the database if the compiled query (cursor) already exists. If it does then
the database will returns the existing cursor context.
On first run it will always create a new statement since it wont exist.