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.

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

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

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

here you go ... I have 'moved' the new posts to this thread ..