Transactions

Figure 930. 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 932. 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.

Prev  Up  Next
Handling surrogate keys  Home  JPA