A Techno Blog, mainly about Java

Transactions methodoloy

A transaction that is isolated means that the transaction in progress is invisible to other transactions until the transaction has been committed. SQL92 supports four different isolation levels. Hopwever the measning of the different isolation levels differ between databases.

I am working on an web application that uses DB2 and sets a transaction isolation level as follows: transactionIsolationLevel of READ_UNCOMMITTED
– the data is visible as soon as it is written to the table. Basically, data is visible by other transactions even if commit() has not been invoked. This mode provides basic transaction support, but dirty reads , nonrepeatable reads, and phantom reads are all possible. Note that the Oracle database does not support this mode.

  • Dirty reads: A transaction reads a row in a database table containing uncommitted changes from another transaction.
  • Nonrepeatable reads: A transaction reads a row in a database table, a second transaction changes the same row and the first transaction rereads the row and gets a different value.
  • Phantom reads: A transaction re-executes a query, returning a set of rows that satisfies a search condition and finds that another committed transaction has inserted additional rows that satisfy the condition.
  • Locking – we do optimistic.This allows concurrent access to the data, but must implement a stratgey for competing updates (an update must be rejected if the data to be updated has changed since the prospective updater last read it).

    for updates: for the row you plan to update, save the data asoociated with the primary key as ‘the original’  , then when you enter the update method, you check to see if the row has been updated by checking ‘the original’ bean data for the entry, and doing a lookup on the data in the database using the primary key from ‘the original’ data. if its finds data based on the originals primary key, the data is the one you stored away as original, and has not been updated. However, if it does not find the entry based on ‘the original’ primary key, means it no longer exists. Next, we continue on with other update checks by seeing if any of the data for the current entry has been changed by someone else since we saved out ‘the original’. If any of the data has changed, we don’t allow the update. Furthermore, you might check if the changes you are making don’t duplicate an existing entry in the table.

    for inserts: you might check if the entry you are adding don’t duplicate an existing entry in the table.

    for deletes: it pretty much similar to updates except you dont have to check field by field on the entry to see if it has been changed by someone else. All you are checking here is if the entry still exists. You might have a check to disallow certain entries from being deleted.

    Link: programmer to programmer

    May 31, 2005 - Posted by | J2EE

    No comments yet.

    Leave a Reply

    Fill in your details below or click an icon to log in: Logo

    You are commenting using your account. Log Out /  Change )

    Google photo

    You are commenting using your Google account. Log Out /  Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out /  Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out /  Change )

    Connecting to %s

    %d bloggers like this: