Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2006
    Posts
    1

    Unanswered: MQT deadlock problem

    we are using an MQT for the first time . when we started testing application even with volume (100 rows per table), the deadlocks are all over the place . If we drop the MQT , deadlocks are gone. Here are the details of the MQT

    TableA( Column_A_1, Colum_A_2, Column_A_3, Column_A_4)

    TableB(Column_B_1, Column_B_2)

    and the MQT is defined as

    Select sum(A.Column_A_3), count(*) Column_A_2, Column_B_2 from
    TableA A, TableB B
    where A.Column_A_1 = B.Column_B_1
    refresh imediate, query optimization enabled

    TableA & TableB gets updated/inserted virtually every millisecond and these are high volume tables. And we are using this MQT in an OLTP application

    Did any one face the similar problem? Any help/clues will be appreciated

    -Rama

  2. #2
    Join Date
    Oct 2005
    Posts
    109
    Hmm, deadlocks are not directly connected to MQTs as such.
    And I thinks that is still to few information.

    So you have multiple applications doing insert/update on table A and table B and selects on the MQT?

    Since it is defined as refresh immediate, any update on table A or B will also cause an update including lock on the MQT.

    Well, you have to be more careful then: commit as often as possible.

    If not needed, don't use the refresh immediate but refresh yourself on e regular basis - this avoids locking issues as well.

    How is the ratio of update/inserts on table A and B versus selects on the MQT? do you gain much using the MQT at all ?

    In OLTP I have used MQTs only few times as a tool to avoid long calculation times e.g. with XML extender exporting documents: I precalculated the strings, so the export run much faster (In V7 that used to be a problem). Here I did not need the refresh immediate.

    And then of course: the usual stuff applies:
    - check your isolation levels
    - check whether decent indices are defined also for the MQT selects
    - specify select statements always with 'for read only'
    - check on db2set profile variables DB2_EVALUNCOMMITED, DB2_SKIPDELETED, DB2_SKIPINSERTED etc, whether those are interesting for you.
    Juliane

  3. #3
    Join Date
    Dec 2005
    Posts
    18
    Or use View instead of MQT (unless there is a specific reason to use MQT)

    Quote Originally Posted by juliane26
    Hmm, deadlocks are not directly connected to MQTs as such.
    And I thinks that is still to few information.

    So you have multiple applications doing insert/update on table A and table B and selects on the MQT?

    Since it is defined as refresh immediate, any update on table A or B will also cause an update including lock on the MQT.

    Well, you have to be more careful then: commit as often as possible.

    If not needed, don't use the refresh immediate but refresh yourself on e regular basis - this avoids locking issues as well.

    How is the ratio of update/inserts on table A and B versus selects on the MQT? do you gain much using the MQT at all ?

    In OLTP I have used MQTs only few times as a tool to avoid long calculation times e.g. with XML extender exporting documents: I precalculated the strings, so the export run much faster (In V7 that used to be a problem). Here I did not need the refresh immediate.

    And then of course: the usual stuff applies:
    - check your isolation levels
    - check whether decent indices are defined also for the MQT selects
    - specify select statements always with 'for read only'
    - check on db2set profile variables DB2_EVALUNCOMMITED, DB2_SKIPDELETED, DB2_SKIPINSERTED etc, whether those are interesting for you.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by pvrk
    Select sum(A.Column_A_3), count(*) Column_A_2, Column_B_2 from
    TableA A, TableB B
    where A.Column_A_1 = B.Column_B_1
    refresh imediate, query optimization enabled

    TableA & TableB gets updated/inserted virtually every millisecond and these are high volume tables. And we are using this MQT in an OLTP application
    I don't think it is wise to use a REFRESH IMMEDIATE MQT in your situation. While the refresh process calculates the summary it has to ensure both tables are consistent so it will most likely lock them to prevent updates.

Posting Permissions

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