Transaction are used to access data concurrently with consistency and by maintaining data integrity. You can view a database transaction as a set of interactions with the database system independent of other transactions, which are either completed or aborted. This set of interactions is called unit of work. A unit of work can be defined as a set of SQL statements, which need to be executed successfully to complete a task. Any failure from these SQL statements should lead to failure of the transaction (unit of work), and the database system should be restored to the state where it is before starting the transaction.
A single transaction can contain a set of queries, which reads and writes to the database. To complete a transaction, an application should make sure that all of the queries are executed successfully before ending the transaction. This is required to maintain the integrity of the data. For example, for an order, if the customer bought x quantity of the product y, the inventory details for product y should be decreased by the x amount to complete the purchase order transaction. If the purchase order is successful without updating the inventory, the data in the inventory details becomes inconsistent.
A typical transaction has the following steps:
To maintain the consistency and integrity in a transaction, JDBC includes the following concepts:
Auto commit mode
A transaction is started whenever an SQL statement requires one and there is no transaction in place. There is no explicit API defined to start a transaction. Auto commit mode is set to indicate when to end a transaction. Auto commit mode can be set by setting the auto-commit attribute of the Connection bject.
This attribute is set to either enable or disable. Enabling this attribute makes each SQL statement a separate transaction. So whenever an SQL statement is issued by the application, a new transaction is started and once the statement is executed successfully, the transaction is completed by committing any updates done to the database. Disabling this attribute gives the application flexibility to end the transaction any time by calling the commit method of the Connection object. if any of the SQL statements in the transaction failed, the application needs to restore the state of the database by calling the rollback method of the Connection object. Disabling this attribute gives the flexibility to the application to include multiple SQL statements in a transaction and commit and roll back the transaction whenever needed.
The value of the auto-commit attribute of the Connection object can be set by calling the Connection object method setAutoCommit. The default value for this attribute is true (enable). If the value of the auto-commit attribute is changed in the middle of the transaction, the current transaction is committed and a new transaction with the changed value is started.
Transaction isolation level
The isolation level specifies how the data is visible to the transactions running concurrently while the transactions update the data in the database. Isolation level also relates to how the data is locked for a particular transaction. They directly impact the level of concurrent access to the same database object and how the different transactions interact with each other while accessing the same data source. The different kinds of interaction possibilities are:
These interactions between different transactions can cause unpredictable results. Setting isolation levels to appropriate values restricts these interactions. DB2 provides the following isolation levels. Each isolation level is described on two parameters:
All the transactions acquire an exclusive lock whenever they update a row in the table, so the lost update is not possible at all.
Uncommitted read (UR)
This is the lowest level of isolation:
Any kind of interaction except lost update is possible with this isolation level.
Cursor stability (CS)
The characteristics of CS are:
Read stability (RS)
The characteristics of RS are:
Repeatable read (RR)
This is the highest level of isolation. This isolation level completely isolates the concurrent transactions:
Example shows how to set the isolation level for a transaction.
Setting islolation levelConnection con = null; Class.forName(“com.ibm.db2.jcc.DB2Driver”).newInstance(); con = DriverManager.getConnection(“jdbc:db2:sample”); // Set the isolation level to RR con.setTransactionIsolation(TRANSACTION_SERIALIZABLE);
Below shows the JDBC equivalent variable for the different isolation levels.
Equivalent JDBC and DB2 isolation levels
A savepoint defines a particular state of the database during a unit of work or transaction. A savepoint is required if we want to roll back the transaction to a particular state instead of rolling back to the start of the transaction. DB2 Universal driver provides the method set Savepoint of the Connection object to set a savepoint during the transaction.
A savepoint can be release by calling the method releaseSavePoint method of the Connection object. Releasing a savepoint will release all the savepoints created after the released savepoint. After releasing the savepoint, the transaction cannot be rolled back to the released savepoint or any of the savepoints created subsequently.
A transaction can be rolled back to a savepoint by calling the rollback method and giving the savepoint variable as an argument to this method. Any cursor opened inside a savepoint will remain open even after rollback to the savepoint but can go to an invalid state if they depend on some DDL statements which were rolled back.Example shows how to create the savepoint.
Savepointcon.setAutoCommit(false); Statement stmt=con.createStatement(); stmt.executeUpdate("create table order(id int, description varchar(100))"); con.commit(); stmt.executeUpdate("insert into order values(1, 'first order of the day')"); Savepoint svpt1=con.setSavepoint(); stmt.executeUpdate("insert into order values(2, 'second order')"); Savepoint svpt2=con.setSavepoint(); stmt.executeUpdate("insert into order values(3, 'third order')"); con.rollback(svpt2); con.releaseSavepoint(svpt1); stmt.close(); con.commit();
IBM DB2 Related Interview Questions
|IBM Websphere Interview Questions||IBM-REXX Interview Questions|
|IBM Cognos Interview Questions||IBM DB2 Interview Questions|
|J2EE Interview Questions||COBOL Interview Questions|
|IBM-JCL Interview Questions||DB2 Using SQL Interview Questions|
|IBM WAS Administration Interview Questions||IBM WebSphere Administration Interview Questions|
|Database Administration Interview Questions||DB2 SQL Programming Interview Questions|
|Mainframe DB2 Interview Questions|
IBM DB2 Related Practice Tests
|IBM Websphere Practice Tests||IBM Cognos Practice Tests|
|IBM DB2 Practice Tests||J2EE Practice Tests|
|COBOL Practice Tests||IBM-JCL Practice Tests|
|DB2 Using SQL Practice Tests||IBM WAS Administration Practice Tests|
|Database Administration Practice Tests||DB2 SQL Programming Practice Tests|
Ibm Db2 Tutorial
Db2 Application Development Overview
Application Development With Db2 Purexml
Application Development With Php
Application Development With C/c++
Application Development With Java
Application Development With .net
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.