473,320 Members | 1,982 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Deadlock

RZ
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ł
Feb 14 '06 #1
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
Feb 14 '06 #2
RZ
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.
Feb 14 '06 #3
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
Feb 14 '06 #4

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 ...

Feb 15 '06 #5
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
Feb 15 '06 #6

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 ?

Feb 15 '06 #7
"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.
Feb 16 '06 #8
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
Feb 16 '06 #9

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 ...
Feb 16 '06 #10
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
Feb 16 '06 #11
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
Feb 16 '06 #12

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
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...
1
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...
7
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...
3
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...
1
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....
6
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...
15
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)...
1
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...
2
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...
0
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
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...
0
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...
1
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)...
0
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...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
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....
0
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
0
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.