 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / DB2 Topics / February 2006Deadlock
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ł > Hi. > I have a few instances of same java application working concurrent on DB2 [quoted text clipped - 16 lines] > > 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 > 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. >> 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  SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab >>> Try setting this instance parameter (probably requires instance stop and >>> start). I think you need FP9 or higher, but not sure. [quoted text clipped - 5 lines] > 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 ... >>>> Try setting this instance parameter (probably requires instance stop >>>> and start). I think you need FP9 or higher, but not sure. [quoted text clipped - 14 lines] > 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. SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab > 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 ? >> That's odd. Should be 1 = ON = YES. > [quoted text clipped - 3 lines] > 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. > >> That's odd. Should be 1 = ON = YES. > > [quoted text clipped - 6 lines] > 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 ... >> That's odd. Should be 1 = ON = YES. > > What does exactly mean "That's odd" ? > Sorry, but my english is not perfect ... Neither is mine :-) > You mean I said something that can not be true ? > So we are still doing some mistake ? Either DB2 made a mistake by not accepting YES or you made one. DB2 is supposed to support 1, ON and YES as well as 0, OFF and NO. If you can reproduce the bad behaviour with a simple example it may be worth calling support so other customers don't run into teh problem you may have discovered. Cheers Serge SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab > Either DB2 made a mistake by not accepting YES or you made one. > DB2 is supposed to support 1, ON and YES as well as 0, OFF and NO. > If you can reproduce the bad behaviour with a simple example it may be > worth calling support so other customers don't run into teh problem you > may have discovered. The code should be doing the right thing (in V8.2). So a reproducable scenario would be really appreciated. SignatureKnut Stolze DB2 Information Integration Development IBM Germany > 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. SignatureKnut Stolze DB2 Information Integration Development IBM Germany |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.