Transactions

Figure 924. Isolation level Slide presentation
  • Level 0: Prevent other transactions from changing data that has already been modified by an uncommitted transaction.

    Other transactions can read uncommitted data resulting in dirty reads.

  • Level 1: Prevents dirty reads. (Default on many RDBMS)

  • Level 2: prevents non-repeatable reads.

  • Level 3: Data read by one transaction is valid until the end of that transaction, preventing phantom rows.



Figure 926. Setting the isolation level Slide presentation

exercise No. 13

Isolation level 1 vs. 2

Q:

What is the difference between Isolation level 1 and two?

A:

Consider the following schedule being prohibited in isolation level 1:

Transaction A Transaction B
- begin transaction
begin transaction -
- write X
read X (Dirty read) -
- rollback transaction
... -

Isolation level 2 will prohibit successfully committed intermediate results as well:

Transaction A Transaction B
begin transaction -
read X -
- begin transaction
- write X
- commit transaction
read X -
commit -

Note that the second read X operation in transaction A will access successfully committed data presumably different to its first read X result. This is compatible with isolation level 1 since no dirty data is being involved. Isolation level 2 prevents this behaviour.

exercise No. 14

JDBC and transactions

Q:

A:

A connection's default state is autocommit == true. In this state each individual SQL statement (SELECT, UPDATE, ...) defines a separate transaction.

The JDBC API does not provide a start transaction equivalent. Instead transactions are being started implicitly and last until connection.commit() is being executed.

Grouping two or more SQL statements into a transaction in turn requires:

  1. Calling connection.setAutoComit(false).

  2. All subsequent SQL statements will implicitly become part of the current transaction till either of the following three events happen:

    1. commit()

    2. rollback()

    3. The transaction gets aborted by the database server. This may for example happen in case of a deadlock conflict with a second transaction.

    Note

    Both commit() and rollback() are being initiated from the client side whereas aborting a transaction happens on behalf of the database server.

exercise No. 15

Aborted transactions

Q:

In the previous exercise we mentioned the possibility of a transaction aborts being issued by a database server. Which responsibility arises for an application programmer?

Tip

How may an implementation become aware of an «abort transaction» event?

A:

On aborting a transaction a database server will cause the corresponding JDBC client to throw a SQLException. An application is obliged to implement a sensible catch(...) clause.