Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2002
    Location
    Pinellas County Florida
    Posts
    20

    Transaction Isolation Level

    I've been programming with JDBC for 7 years now and never really had to worry about transaction isolation level. Well, that time has come.

    We have a very big table (4 million rows) filled with inventory records. Our salespeople query this table looking for specific parts. So, at any one time, we may have a few dozen open Result Sets each referencing hundreds of rows in this table. But all of these queries are explicitly READ ONLY.

    At the same time, we want to update this table. The way we do this is to add new rows for a particular part manufacturer, one manufacturer at a time. Sometimes this results in the insertion of 250,000 rows. Each row is marked so that we know it is inserted today.

    Next, we delete the rows for this manufacturer that we put in yesterday. After the insertion and before the deletion, we have some duplicate rows, but that doesn't bother us.

    DELETE FROM BIG_TABLE WHERE MFR = 'My Manufacturer'

    The problem we're having is that when we delete yesterday's rows, while at the same time our salespeople are querying some of those same rows, we end up exceeding our lock space. The queries each issue row locks on their rows of interest. The delete issues row locks on its massive set of rows. When the lock space is nearly used up, the database (DB2) starts to free up row locks by issuing table locks and performance degrades rapidly.

    Our solution is to lower the isolation level on these transactions. I think that in the case of the queries, they can use UNCOMMITTED_READ. After all, they are read only queries and have no need to use transactions and lock rows at all. This should eliminate all of the row locks issued by these queries. (By the way, we'd like to use transaction isolation level of NONE, but this is not available with our JDBC driver).

    My question is, do I need to also change the isolation level on my big, fat delete statement, too? If so, what will it gain me? I should have enough lock space to lock all of the rows I want to delete and, so long as my queries are not also generating row locks, I should never escalate to a table lock.

    On the other hand, what's the harm of dropping the isolation level on the delete statement? We just don't care if the salespeople see rows in their results that no longer exist (so long as we handle any consequent errors). And no other process will be updating or deleting these rows (they may be updating or deleting rows for other manufacturers, but who cares?).

    In fact, to get the benefit of the low isolation level, should I be more worried about relaxing the delete statement(s) or the queries' isolation level? In other words, to permit dirty reads, don't I have to lower the isolation level of the delete statement?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Transaction Isolation Level

    Originally posted by milkchaser
    I've been programming with JDBC for 7 years now and never really had to worry about transaction isolation level. Well, that time has come.

    We have a very big table (4 million rows) filled with inventory records. Our salespeople query this table looking for specific parts. So, at any one time, we may have a few dozen open Result Sets each referencing hundreds of rows in this table. But all of these queries are explicitly READ ONLY.

    At the same time, we want to update this table. The way we do this is to add new rows for a particular part manufacturer, one manufacturer at a time. Sometimes this results in the insertion of 250,000 rows. Each row is marked so that we know it is inserted today.

    Next, we delete the rows for this manufacturer that we put in yesterday. After the insertion and before the deletion, we have some duplicate rows, but that doesn't bother us.

    DELETE FROM BIG_TABLE WHERE MFR = 'My Manufacturer'

    The problem we're having is that when we delete yesterday's rows, while at the same time our salespeople are querying some of those same rows, we end up exceeding our lock space. The queries each issue row locks on their rows of interest. The delete issues row locks on its massive set of rows. When the lock space is nearly used up, the database (DB2) starts to free up row locks by issuing table locks and performance degrades rapidly.

    Our solution is to lower the isolation level on these transactions. I think that in the case of the queries, they can use UNCOMMITTED_READ. After all, they are read only queries and have no need to use transactions and lock rows at all. This should eliminate all of the row locks issued by these queries. (By the way, we'd like to use transaction isolation level of NONE, but this is not available with our JDBC driver).

    My question is, do I need to also change the isolation level on my big, fat delete statement, too? If so, what will it gain me? I should have enough lock space to lock all of the rows I want to delete and, so long as my queries are not also generating row locks, I should never escalate to a table lock.

    On the other hand, what's the harm of dropping the isolation level on the delete statement? We just don't care if the salespeople see rows in their results that no longer exist (so long as we handle any consequent errors). And no other process will be updating or deleting these rows (they may be updating or deleting rows for other manufacturers, but who cares?).

    In fact, to get the benefit of the low isolation level, should I be more worried about relaxing the delete statement(s) or the queries' isolation level? In other words, to permit dirty reads, don't I have to lower the isolation level of the delete statement?
    It is the isolation level of the readers that you need to change - i.e. the readers have to specify that they will accept dirty reads. The isolation level of the deleting process is irrelevant - it WILL lock/delete those records (if it can).

    Of course, if you can migrate to Oracle you will have less issues: locks never escalate, there is no "lockspace" to run out of, and readers do not lock records at all regardless of isolation level.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •