Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2006
    Location
    Columbus, OH
    Posts
    69

    Unanswered:

    Are there any good resources to explain how to prevent locking for DB2 databases? I'm already seeing signs of locking between the application, triggers, and selects. And we're only in test mode.

    Diagram


    App --> Table1a Table2a Table3a Table4a Table5a
    |
    |
    --> Table1b Table2b Table3b Table4b Table5b


    The Application INSERTS data into Table1a & 1b simultaneously, then when done with that load, flags another table, and which triggers a data move. From delete 5, 4->5, 3->4, 2->3..... then deletes the group out of both 1 tables. Have Table 1 serve as the temp table for the app to INSERT in.

    The issue i am VERY concerned with, is the app will continue to process data into the TEMP table and their will be a lock on the table so either the app wont be able to insert or the TRIGGER won't be able to delete from.

    If the app and trigger do record locking this shouldnt be a problem, bc hypothetically they wont be the same records. However the Trigger should always get priority over the app, make the app wait then insert.

    Is this going to be possible? Can you give locking priority to one over the other? Can you force control over a lock? Can you do this in a trigger? Appreciate any insight or direction to more info. No more DBA's in this group left to bounce ideas off of. Thanks,
    Charlie.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Charlie,
    Next time you have a different topic, start a new thread. You will get a better chance of getting an answer. That being said, I will try to help.

    Locks are done on a first-come first-serve basis. There is no other way to do it. But when a trigger fires, any locking it does actually belongs to the unit of work (UOW) of the application that caused the trigger to fire. They are not separate processes. In DB2 all locks are row locks unless 1) there is no more space in the lock-list at which point all the row locks for the table escalate to a table lock. or 2) There already is a table lock from either a lock-escalation or the table was lock through a SQL command (i.e. LOCK TABLE...).

    I am afraid I do not understand your issue. A single UOW cannot have locking problems with itself. Do you have multiple applications experiencing the problem? Please clarify.

    Andy

  3. #3
    Join Date
    Sep 2006
    Location
    Columbus, OH
    Posts
    69
    Basically the upload application is a perl script, firing inserts into the table. Does this represent 1 UOW? The perl script will issue an UPDATE which will cause the TRIGGER to fire. Does the next INSERT wait for the completion of the TRIGGER?

    Thanks,
    Charlie

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    A UOW is all the commands issued between a commit/rollback to the next commit/rollback. So if you are not issuing commits, you have one large UOW which tends to lead to lock escalation which is something that should be avoided. You should commit as quickly as possible. Any DML (INSERT, UPDATE, DELETE) will wait until all the triggers that are fired by it are completed before it will return control to the issuer.

    Andy

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    here you go ... I have 'moved' the new posts to this thread ..
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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