Tweet
Hi,
I have one problem which I can't resolve.
I run quite long process which is all about using different options in our system and this is all abour preparing and processing data.
At some point I enter program which start Committment control under QILE Activation Group. Program ends, commit ends and Activation group is no longer Active (but of course still visible as inactive).
Later I enter program which start Commitment Control (job scope) again under same Activation Group and when program try to update one table, it lock itself on some records while updating some records. When I checked performance monitor I've seen Hard Reset Locks and Uncommited Reads on this table which I believe lead to Dead Locks and thus program hangs for quite long time.
Thing is the update trigger is compiled with commit *none for this one table and there is no commit on this table in the program but in journal I can see this update operation was done under coomit and I can't understand why.
I was able to get rid off this problem in two cases:
1. I've recompiuled trigger with "Set Transaction Isolation level *none" setting explicitly in SQL trigger and then it no longer locks itself
2. I've started Commitment under QILE activation group only once.
While I'm happy with solution from point 1 I can't understand why this is happening. Using prtsqlinf on update trigger I can see that commit is set to *none but it looks like this is not enough and only after I add "Set Transaction Isolation level *none" program no longer locks on this table.
I have one problem which I can't resolve.
I run quite long process which is all about using different options in our system and this is all abour preparing and processing data.
At some point I enter program which start Committment control under QILE Activation Group. Program ends, commit ends and Activation group is no longer Active (but of course still visible as inactive).
Later I enter program which start Commitment Control (job scope) again under same Activation Group and when program try to update one table, it lock itself on some records while updating some records. When I checked performance monitor I've seen Hard Reset Locks and Uncommited Reads on this table which I believe lead to Dead Locks and thus program hangs for quite long time.
Thing is the update trigger is compiled with commit *none for this one table and there is no commit on this table in the program but in journal I can see this update operation was done under coomit and I can't understand why.
I was able to get rid off this problem in two cases:
1. I've recompiuled trigger with "Set Transaction Isolation level *none" setting explicitly in SQL trigger and then it no longer locks itself
2. I've started Commitment under QILE activation group only once.
While I'm happy with solution from point 1 I can't understand why this is happening. Using prtsqlinf on update trigger I can see that commit is set to *none but it looks like this is not enough and only after I add "Set Transaction Isolation level *none" program no longer locks on this table.