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ł 11 3980
"RZ" <zy**@wp.pl> wrote in message news:ds**********@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" <sr*****@ca.ibm.com> napisał w wiadomości
news:45************@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" <sr*****@ca.ibm.com> napisał w wiadomości news:45************@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" <sr*****@ca.ibm.com> napisał w wiadomości
news:45************@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" <si****@wp.pl> wrote in message
news:dt**********@inews.gazeta.pl... Użytkownik "Serge Rielau" <sr*****@ca.ibm.com> napisał w wiadomości news:45************@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" <no****@nowhere.com> napisał w wiadomości
news:ge******************************@comcast.com. .. "Filip Sielimowicz" <si****@wp.pl> wrote in message news:dt**********@inews.gazeta.pl... Użytkownik "Serge Rielau" <sr*****@ca.ibm.com> napisał w wiadomości news:45************@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 ...
Filip Sielimowicz wrote: Użytkownik "Serge Rielau" <sr*****@ca.ibm.com> napisał w wiadomości news:45************@individual.net...
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
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Serge Rielau wrote: 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.
--
Knut Stolze
DB2 Information Integration Development
IBM Germany This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Robert Brown |
last post by:
I have a deadlock that's happening on one oracle instance but cannot
be reproduced on any other. It is always caused by the same SQL
statement colliding with itself and only happens under very high...
|
by: dawatson833 |
last post by:
I want to set an alert for a specific table whenever an event has
caused a deadlock to occur on the table.
I understand how to set up an alert. But I don't know which error
number to use for...
|
by: Andrew Mayo |
last post by:
Here's a really weird one for any SQL Server gurus out there...
We have observed (SQL Server 2000) scenarios where a stored procedure
which
(a) begins a transaction
(b) inserts some rows into...
|
by: Nigel Robbins |
last post by:
Hi There,
I'm getting a deadlock when I have two clients running the following statement.
DELETE FROM intermediate.file_os_details WHERE file_uid = ? AND obj_uid
There is a compound index on...
|
by: Rohit Raghuwanshi |
last post by:
Hello all,
we are running a delphi application with DB2 V8.01 which is causing
deadlocks when rows are being inserted into a table. Attaching the
Event Monitor Log (DEADLOCKS WITH DETAILS) here....
|
by: Todd McNeill |
last post by:
Hi-
We ran into some very strange deadlocks this AM, and I was hoping to
get some insight. We were running a REORGCHK on a database, and
started getting deadlocks. What is curious is that...
|
by: Zeng |
last post by:
Hi,
The bigger my C# web-application gets, the more places I need to put in the
tedious retrying block of code to make sure operations that can run into
database deadlocks are re-run (retried)...
|
by: Grant McLean |
last post by:
Hi
First a simple question ...
I have a table "access_log" that has foreign keys "app_id" and
"app_user_id" that reference the "application_type" and "app_user"
tables. When I insert into...
|
by: Sumanth |
last post by:
Hi ,
I am trying to acquire a lock on a table A in exclusive mode, and this
statement gives an error indicating a deadlock or timeout
has been detected. The lock timeout value is set to 0 which...
|
by: cwho.work |
last post by:
Hi!
We are using apache ibatis with our MySQL 5.0 database (using innodb
tables), in our web application running on Tomcat 5. Recently we
started getting a number of errors relating to...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shćllîpôpď 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |