Hi all, I have one typical problem of handling transaction. The scenario is my application will create a user profile, so at the end i am inserting/updating 6 tables in sequence. the whole insert /update to 6 tables are one transaction. I am keeping my table locked unless rest of the 5 inserts are done. company standard is page label lock , now we do not want to lock the first table as other application using the first table are timing out,.

1. We want to commit the first table and release the lock. How to handle the error situation and how to roll back if there is any error in rest of inserts


now please suggest me different options that i can implement for transaction management.

1. Bi-temporaral logic is not possible as its not the standard in the company
2. Cant go for row level locking, not the standard of the company

Change the "standard of the company" to something that makes sense.

Maybe you can use something like DB2_SKIP_INSERTED and related registry settings? Maybe you can use "uncommitted read"? What is "bi-temporaral logic", btw? What would be helpful is to know which DB2 version you are using on which platform.

we are using DB2 v8 on mainframe platform,
1. We can always change the standard of the company if we can justify the change on a system level. The standard is implemented for 7 countries and we need strong facts to support our changes.

2. Bi -temporaral logic is used to preserve data, We do not update any record directly. We do new insert marking the existing one as invalid. There would be one indicator in each table to indicate "INVALID_IND" . On a daily/weekly/monthly basis we will delete all the records with INVALID_IND = 'Y'. So any time if there is any error during the subsequent insert we can revert back to original records.

I am looking for similar mechanism where i can revert back to previous record after commit, if there is any DB2 feature supports the same mechanism

I would go to row-level locks because page-level locking also has some nasty and unexpected side effects, especially for simple table spaces. And you should reduce the isolation level as far as possible, i.e. use CS if UR is not acceptable. Aside from that, you can apply the usual performance optimizations (commit early in applications, tune the system, etc.)

Something else you could consider is to store all changes in a staging table (like a global temp table). Once you have all 6 changes, apply them together and without delay to the actual table, then commit.

why are you keeping your table locked unless rest of the 5 inserts are done ?

I think there`s just a row lock in your table after insert and before commit.

On INSERTs, DB2 has to keep the X-locks until the end of the transaction to prevent dirty reads. Due to lock hierarchies, you will also have an IX-loxk at the table level. Hierarchical locks are needed for table or system-wide operations, e.g. for a DROP TABLE command, you do not want to X-lock every single row in the table, but just the table itself. But when you do that, you need some information at the table level whether there are locks set on a level further down, i.e. on pages or rows associated with the table.

Whether you have row, page, or table level locks depends on your configuration, isolation level setting and whether lock escalation occurs. So you cannot necessarily assume to have "just a row lock".