Weird deadlock issue
in [DB2]
|
From: Greig on 2 Aug 2010 18:09 I have this problem where I have an SSRS report which connects to a DB2 database. It issues a few queries and then a deadlock occurs. The weird thing is that none of the queries being issued are update/ insert/delete queries. They are strictly select queries. Looking at the output from my deadlock event monitor, it seems like the 2 queries being issued at the time of the deadlock are selects from 2 different SQL PL UDFs. Kinda like this: select count(*) from table(udf1(a,b,c)) select count(*) from table(udf2(a,b)) The locks being waited on are for sysibm.sysroutines. So, what the heck is DB2 doing that's deadlocking out trying to execute the UDFs? One other notable thing...This was all working fine until I patched my server from DB2 9.1.7 to DB2 9.1.9. Not sure if this has anything to do with it or not, but it seems coincidental. My server is on Linux and the SSRS is running on a windows client version 9.1.5. Oh, and I did do a db2rbind on this database after I patched. Thanks for any help. Greig
From: Serge Rielau on 2 Aug 2010 19:43 On 8/3/2010 6:09 AM, Greig wrote: > I have this problem where I have an SSRS report which connects to a > DB2 database. It issues a few queries and then a deadlock occurs. > The weird thing is that none of the queries being issued are update/ > insert/delete queries. They are strictly select queries. > > Looking at the output from my deadlock event monitor, it seems like > the 2 queries being issued at the time of the deadlock are selects > from 2 different SQL PL UDFs. Kinda like this: > > select count(*) from table(udf1(a,b,c)) > select count(*) from table(udf2(a,b)) > > The locks being waited on are for sysibm.sysroutines. So, what the > heck is DB2 doing that's deadlocking out trying to execute the UDFs? > > One other notable thing...This was all working fine until I patched my > server from DB2 9.1.7 to DB2 9.1.9. Not sure if this has anything to > do with it or not, but it seems coincidental. My server is on Linux > and the SSRS is running on a windows client version 9.1.5. Oh, and I > did do a db2rbind on this database after I patched. Could it be the UDFs are in an invalid state when they are executed. If both try to revalidate at the same time then a conflict on the catalog sis conceivable. If that's not the case, open a PMR... -- Serge Rielau SQL Architect DB2 for LUW IBM Toronto Lab
From: Helmut Tessarek on 2 Aug 2010 20:20 > One other notable thing...This was all working fine until I patched my > server from DB2 9.1.7 to DB2 9.1.9. Not sure if this has anything to > do with it or not, but it seems coincidental. My server is on Linux > and the SSRS is running on a windows client version 9.1.5. Oh, and I > did do a db2rbind on this database after I patched. > You said that you rebound the packages, but did you bind db2cli.lst, db2ubind.lst and db2schema.bnd after installing the FP? Did you change any db2 registry variables? -- Helmut K. C. Tessarek DB2 Performance and Development /* Thou shalt not follow the NULL pointer for chaos and madness await thee at its end. */
From: Greig on 2 Aug 2010 22:40 sysibm.sysroutines says they're valid. I did open an ITR. I e-mailed you the number if you're interested. On Aug 2, 6:09 pm, Greig <frital...(a)gmail.com> wrote: > I have this problem where I have an SSRS report which connects to a > DB2 database. It issues a few queries and then a deadlock occurs. > The weird thing is that none of the queries being issued are update/ > insert/delete queries. They are strictly select queries. > > Looking at the output from my deadlock event monitor, it seems like > the 2 queries being issued at the time of the deadlock are selects > from 2 different SQL PL UDFs. Kinda like this: > > select count(*) from table(udf1(a,b,c)) > select count(*) from table(udf2(a,b)) > > The locks being waited on are for sysibm.sysroutines. So, what the > heck is DB2 doing that's deadlocking out trying to execute the UDFs? > > One other notable thing...This was all working fine until I patched my > server from DB2 9.1.7 to DB2 9.1.9. Not sure if this has anything to > do with it or not, but it seems coincidental. My server is on Linux > and the SSRS is running on a windows client version 9.1.5. Oh, and I > did do a db2rbind on this database after I patched. > > Thanks for any help. > Greig
From: Greig on 2 Aug 2010 22:41
Yeah, I rebound that other stuff too. On Aug 2, 8:20 pm, Helmut Tessarek <tessa...(a)evermeet.cx> wrote: > > One other notable thing...This was all working fine until I patched my > > server from DB2 9.1.7 to DB2 9.1.9. Not sure if this has anything to > > do with it or not, but it seems coincidental. My server is on Linux > > and the SSRS is running on a windows client version 9.1.5. Oh, and I > > did do a db2rbind on this database after I patched. > > You said that you rebound the packages, but did you bind db2cli.lst, > db2ubind.lst and db2schema.bnd after installing the FP? > > Did you change any db2 registry variables? > > -- > Helmut K. C. Tessarek > DB2 Performance and Development > > /* > Thou shalt not follow the NULL pointer for chaos and madness > await thee at its end. > */ |