Transactions SQL statements - IBM DB2

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:

  1. Start the transaction.
  2. Execute some queries. (Any updates to the database are not visible to the outside world yet.)
  3. Complete the transaction by committing. (Any updates to the database become visible now.)

To maintain the consistency and integrity in a transaction, JDBC includes the following concepts:

  • Auto commit mode
  • Transaction isolation level
  • Savepoints

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:

  • Lost updates
  • Two applications, Application A and Application B, read the same row and calculate the new value for a column for that row. Application A updates the row with the new value, and just after that Application B updates its new value. The value updated by Application A is lost.
  • Access to uncommitted data
  • Application A updates a row without committing, and Application B reads this uncommitted value. Now, if Application A rolled back the transaction, Application B has already read the data and has the wrong set of data.
  • Nonrepeatable reads
  • Application A reads from the database and then goes on to process another SQL statement in the transaction. In the meantime, Application B has updated or deleted the same row. Now if Application A came back and read the same data again, it found either the value is updated or deleted from the database. This type of interaction is possible when Application B updates or deletes the row, which was the part of the result set of transaction A.
  • Phantom read
  • Application A selects some data based on some condition. Application B inserts another row to the same table, which satisfies the condition of Application A and commits the changes. If Application A selects the data again, it will find the extra rows in the result of the query. This kind of transaction is possible if Application B adds some more rows to the table, which can be part of the result set of transaction A if the result set is created again (by running the statement again).

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:

  • How the data is seen by the other transactions while this transaction with this specified isolation level is updating or reading the data.
  • How the current transaction with the specified isolation level can see the data read or updated by other transactions.

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 row already read by the current transaction can be updated by another transaction.
  • The current transaction can see any uncommitted data from other transactions.

Any kind of interaction except lost update is possible with this isolation level.

Cursor stability (CS)
The characteristics of CS are:

  • Any row except the current row read by this transaction can be updated by the other transactions.
  • The current transaction cannot see the uncommitted data from other transactions.
  • Non-Repeatable read and phantom read are possible with this isolation level.

Read stability (RS)
The characteristics of RS are:

  • Any row read by the current transaction cannot be updated by any other transaction.
  • The current transaction cannot see the uncommitted data from other transactions.
  • Interactions of type phantom read are possible between concurrent transactions.

Repeatable read (RR)
This is the highest level of isolation. This isolation level completely isolates the concurrent transactions:

  • Any row read by the current transaction cannot be updated by any other transaction. At the same time, this isolation level makes sure that the ResultSet of the same statement remains constant even if the statement is executed twice in the same transaction.
  • Current transactions cannot see the uncommitted data from other transactions.
  • The isolation level for a transaction can be set by calling the method set Transaction Isolation of Connection object.

Example shows how to set the isolation level for a transaction.

Setting islolation level

Connection 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

Equivalent JDBC and DB2 isolation levels

Savepoints

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.

Savepoint

con.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();

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

IBM DB2 Topics