Hi.
I have a few instances of same java application working concurrent on
DB2 v8.2. All of them:
- has a transaction isolation level set to TRANSACTION_READ_UNCOMMITTED,
- try to insert into same table T1,
- resolves triggers:
- BEFORE INSERT ON T1,
- AFTER INSERT ON T1
which selects from table T2.

In all, except one, occurred an exception with: SQLCODE: 911, SQLSTATE:
40001, SQLERRMC: 2. When I give up with select statement in triggers,
applications seems to work fine.

Can someone explain why the deadlock occurs with transaction isolation
level set to TRANSACTION_READ_UNCOMMITTED and how to solve the problem.

Regards,

Rafa³

"RZ" <zyry@wp.pl> wrote in message news:dssl4v$4rr$1@news.onet.pl...
> Hi.
> I have a few instances of same java application working concurrent on DB2
> v8.2. All of them:
> - has a transaction isolation level set to TRANSACTION_READ_UNCOMMITTED, -
> try to insert into same table T1,
> - resolves triggers:
> - BEFORE INSERT ON T1,
> - AFTER INSERT ON T1
> which selects from table T2.
>
> In all, except one, occurred an exception with: SQLCODE: 911, SQLSTATE:
> 40001, SQLERRMC: 2. When I give up with select statement in triggers,
> applications seems to work fine.
>
> Can someone explain why the deadlock occurs with transaction isolation
> level set to TRANSACTION_READ_UNCOMMITTED and how to solve the problem.
>
> Regards,
>
> Rafa³

The transaction isolation level in DB2 only affects share (select) locks. It
does not affect locks that are taken as a result of insert, update, delete,
or select for update.

Try setting this instance parameter (probably requires instance stop and
start). I think you need FP9 or higher, but not sure.

db2set DB2_SKIPINSERTED=ON

Mark A wrote:
> Try setting this instance parameter (probably requires instance stop and
> start). I think you need FP9 or higher, but not sure.
>
> db2set DB2_SKIPINSERTED=ON

I've got fixpak 3 instaled, DB2_SKIPINSERTED set to ON - it doesn't help.

RZ wrote:
> Mark A wrote:
>
>> Try setting this instance parameter (probably requires instance stop
>> and start). I think you need FP9 or higher, but not sure.
>>
>> db2set DB2_SKIPINSERTED=ON
>
>
> I've got fixpak 3 instaled, DB2_SKIPINSERTED set to ON - it doesn't help.
Post your triggers. You probably need to straighten out the ordering of
execution. BTW, there are some operations where DB2 has to upgrade the
isolation level. E.g. for RI. Got any of those?

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

U¿ytkownik "Serge Rielau" <srielau@ca.ibm.com> napisa³ w wiadomo¶ci
news:45e8atF6akl0U1@individual.net...
> RZ wrote:
>> Mark A wrote:
>>
>>> Try setting this instance parameter (probably requires instance stop and
>>> start). I think you need FP9 or higher, but not sure.
>>>
>>> db2set DB2_SKIPINSERTED=ON
>>
>>
>> I've got fixpak 3 instaled, DB2_SKIPINSERTED set to ON - it doesn't help.
> Post your triggers. You probably need to straighten out the ordering of
> execution. BTW, there are some operations where DB2 has to upgrade the
> isolation level. E.g. for RI. Got any of those?

Thanks for responses !

The problem was, we had the flag DB2_SKIPINSERTED set to YES, not to ON.
Fortunately we've notised that mistake ...
But till then we had a week of nightmare ...
This is one of that cases, that makes programmers go mad ...

Filip Sielimowicz wrote:
>
> U¿ytkownik "Serge Rielau" <srielau@ca.ibm.com> napisa³ w wiadomo¶ci
> news:45e8atF6akl0U1@individual.net...
>
>> RZ wrote:
>>
>>> Mark A wrote:
>>>
>>>> Try setting this instance parameter (probably requires instance stop
>>>> and start). I think you need FP9 or higher, but not sure.
>>>>
>>>> db2set DB2_SKIPINSERTED=ON
>>>
>>>
>>>
>>> I've got fixpak 3 instaled, DB2_SKIPINSERTED set to ON - it doesn't
>>> help.
>>
>> Post your triggers. You probably need to straighten out the ordering
>> of execution. BTW, there are some operations where DB2 has to upgrade
>> the isolation level. E.g. for RI. Got any of those?
>
>
> Thanks for responses !
>
> The problem was, we had the flag DB2_SKIPINSERTED set to YES, not to ON.
> Fortunately we've notised that mistake ...
> But till then we had a week of nightmare ...
> This is one of that cases, that makes programmers go mad ...
That's odd. Should be 1 = ON = YES.


--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

U¿ytkownik "Serge Rielau" <srielau@ca.ibm.com> napisa³ w wiadomo¶ci
news:45hmbmF6u27rU1@individual.net...

> That's odd. Should be 1 = ON = YES.

What does exactly mean "That's odd" ?
Sorry, but my english is not perfect ...

You mean I said something that can not be true ?
So we are still doing some mistake ?

"Filip Sielimowicz" <sielim@wp.pl> wrote in message
news:dt0bf6$rdv$1@inews.gazeta.pl...
>
> U¿ytkownik "Serge Rielau" <srielau@ca.ibm.com> napisa³ w wiadomo¶ci
> news:45hmbmF6u27rU1@individual.net...
>
>> That's odd. Should be 1 = ON = YES.
>
> What does exactly mean "That's odd" ?
> Sorry, but my english is not perfect ...
>
> You mean I said something that can not be true ?
> So we are still doing some mistake ?

You did what I told you and it works, right? What mistake could there be?
Quit worrying.

Filip Sielimowicz wrote:

> The problem was, we had the flag DB2_SKIPINSERTED set to YES, not to ON.
> Fortunately we've notised that mistake ...
> But till then we had a week of nightmare ...
> This is one of that cases, that makes programmers go mad ...

You probably had some other issue. DB2_SKIPINSERTED can be set to 1, ON, Y,
YES, T, or TRUE to be activated.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

U¿ytkownik "Mark A" <nobody@nowhere.com> napisa³ w wiadomo¶ci
news:geidnVjMPui0UG7enZ2dnUVZ_s2dnZ2d@comcast.com. ..
> "Filip Sielimowicz" <sielim@wp.pl> wrote in message
> news:dt0bf6$rdv$1@inews.gazeta.pl...
> >
> > U¿ytkownik "Serge Rielau" <srielau@ca.ibm.com> napisa³ w wiadomo¶ci
> > news:45hmbmF6u27rU1@individual.net...
> >
> >> That's odd. Should be 1 = ON = YES.
> >
> > What does exactly mean "That's odd" ?
> > Sorry, but my english is not perfect ...
> >
> > You mean I said something that can not be true ?
> > So we are still doing some mistake ?
>
> You did what I told you and it works, right? What mistake could there be?
> Quit worrying.

It's beacuse RZ is doing tests, but the knife is on my throat ...