FK constraint when FK row is there! Always takes 10 mins to fail!

I am developing a SQLServer/JSP/Java (Jrun4) web app that intermittently has a FK constraint that always hoses up the transaction for exactly 10 minutes.

The user can save a record from a form that inserts a few rows in the db. The second insert is dependent on the FK of the first insert. All the inserts are done in an entity bean that calls stored procs.

We've had this intermittant error where the inserts take exactly 10 minutes (every time!), then it fails with a FK constraint, yet out logging shows that it's using an existing foreign key. (the stored procs returns the FK)

We have a dev server and a test server, and this has yet to occur on the dev server! I feel like there must be something set up incorrectly.

Any ideas?

thanks in advance!

-DanFlagged -

Are any of the tables really large? Enforcing a FK constraint without indexes on large tables could cause it to take a really long time. 10 minutes seems extreme, but...

And do you know what the differences are between your dev and test server? Do they have the same constraints? Do they have the same data?|||And the same indexes and statistics?|||There isn't much data: under 100 rows, 10 column.

The problem only happens intermittently. In fact it hasn't happed in the last several hundered inserts (we've been testing it)

Originally posted by strader
Flagged -

Are any of the tables really large? Enforcing a FK constraint without indexes on large tables could cause it to take a really long time. 10 minutes seems extreme, but...

And do you know what the differences are between your dev and test server? Do they have the same constraints? Do they have the same data?|||Both servers are set up the same. The more I think about it- based on the infrequency of the problem it may just be a coincidence that it only happed on the one server.

I'm investigating a possible answer to the problem- I noticed an unhandled exception in the bean and I think it's possible the 1st insert worked, then some unhandled exception occured and rolled back the transaction, then the bean continuted to attempt to insert the child row- just a theory since this is so infrequent.

Originally posted by Paul Young
And the same indexes and statistics?|||Here's another possibility - if you're running simultaneous transactions from different threads or processes against that table, and if your java code or your interface (jdbc?) is hanging, causing that transaction to sit there indefinitely, it could be causing blocking or deadlocks on that small table. If it puts an exclusive lock on the table, and you're holding that transaction open, then all the other waiting processes will fail or timeout.

It might be happening on only one server vs. the other because you're stressing one more than the other.

If it happens again, try using SET LOCK_TIMEOUT to change it to a shorter time for that connection. The more I think about it, the more likely it seems this is what's happening, since it's timing out after exactly ten minutes each time.|||I wanted to try one more thing before I tried your suggestion- and it looks like I fixed the problem, but I'm not 100% sure why the transaction was failing. Here's what the code was doing:

When the user saves form data, the entity bean inserts rows into the database, and in the same transaction (I think) proceeds to update those rows. My guess is that once in a while sqlserver locks those rows so that the records cannot be re-read. My fix was to prevent the extraneous update from occuring. It was happening because I didn't know enough about Java beans when I wrote the bean code. (I only used the store method in the bean for UPDATES and I handled inserts manually, but the store is called automatically for all bean calls so I ended up inserting, then updating).

Our beans are container managed, so that their transactions begin when the bean is called and end when the bean returns. I thought that one could manipulate an uncommited row inserted within a transaction but I guess that's not ALWAYS the case because 1 out of 100 times it times out and fails.

I'd say the problem's solved since it's been a week since my fix was in and no more problems!

thanks for the help.

first()

Hi all

I'm trying to do some calculations using java.....when I execute my query and want to ensure that I am at the first row I'm using ResultSet.first()...but that gives me an "Unsupported method"-error.

I've tried ResultSet.absolute(1) as well - with the same result unfortunately :(

Does anybody know what I'm doing wrong ??

Thanks

KimDid you make your resultset scrollable ?|||That looks more like vb than java...

http://java.sun.com/docs/books/tutorial/index.html|||Nope - its java.

First in sequence

A java group put this out. Is there a way to make it work in SQL 2000 (works
fine in SQL 2005 using a partition by statement - only looking for a 2000
method)
create table #tt(id int,name varchar(3),salary int)
insert into #tt(id , name ,salary)select 10 as id, 'aaa'as name ,
3000 as salary
insert into #tt(id , name ,salary)select 10 , 'abc' , 3890
insert into #tt(id , name ,salary)select 10 , 'bbb', 2000
insert into #tt(id , name ,salary)select 20 , 'xyz' , 10000
insert into #tt(id , name ,salary)select 20 , 'are', 4000
insert into #tt(id , name ,salary)select 50 , 'yyy' , 5000
select a.id, a.name, a.salary
from #tt a
where (a.id, a.salary) in
(select b.id, min(b.salary)
from #tt b
group by b.id
)
--
Regards,
JamieTry:
select
a.id, a.name, a.salary
from
#tt a
inner join
(
select [id], min(salary) as min_salary
from #tt
group by [id]
) as b
on a.[id] = b.[id] and a.salary = b.min_salary
go
AMB
"thejamie" wrote:
> A java group put this out. Is there a way to make it work in SQL 2000 (works
> fine in SQL 2005 using a partition by statement - only looking for a 2000
> method)
> create table #tt(id int,name varchar(3),salary int)
> insert into #tt(id , name ,salary)select 10 as id, 'aaa'as name ,
> 3000 as salary
> insert into #tt(id , name ,salary)select 10 , 'abc' , 3890
> insert into #tt(id , name ,salary)select 10 , 'bbb', 2000
> insert into #tt(id , name ,salary)select 20 , 'xyz' , 10000
> insert into #tt(id , name ,salary)select 20 , 'are', 4000
> insert into #tt(id , name ,salary)select 50 , 'yyy' , 5000
> select a.id, a.name, a.salary
> from #tt a
> where (a.id, a.salary) in
> (select b.id, min(b.salary)
> from #tt b
> group by b.id
> )
> --
> Regards,
> Jamie

First in sequence

A java group put this out. Is there a way to make it work in SQL 2000 (works
fine in SQL 2005 using a partition by statement - only looking for a 2000
method)
create table #tt(id int,name varchar(3),salary int)
insert into #tt(id , name ,salary)select 10 as id, 'aaa'as name ,
3000 as salary
insert into #tt(id , name ,salary)select 10 , 'abc' , 3890
insert into #tt(id , name ,salary)select 10 , 'bbb', 2000
insert into #tt(id , name ,salary)select 20 , 'xyz' , 10000
insert into #tt(id , name ,salary)select 20 , 'are', 4000
insert into #tt(id , name ,salary)select 50 , 'yyy' , 5000
select a.id, a.name, a.salary
from #tt a
where (a.id, a.salary) in
(select b.id, min(b.salary)
from #tt b
group by b.id
)
Regards,
Jamie
Try:
select
a.id, a.name, a.salary
from
#tt a
inner join
(
select [id], min(salary) as min_salary
from #tt
group by [id]
) as b
on a.[id] = b.[id] and a.salary = b.min_salary
go
AMB
"thejamie" wrote:

> A java group put this out. Is there a way to make it work in SQL 2000 (works
> fine in SQL 2005 using a partition by statement - only looking for a 2000
> method)
> create table #tt(id int,name varchar(3),salary int)
> insert into #tt(id , name ,salary)select 10 as id, 'aaa'as name ,
> 3000 as salary
> insert into #tt(id , name ,salary)select 10 , 'abc' , 3890
> insert into #tt(id , name ,salary)select 10 , 'bbb', 2000
> insert into #tt(id , name ,salary)select 20 , 'xyz' , 10000
> insert into #tt(id , name ,salary)select 20 , 'are', 4000
> insert into #tt(id , name ,salary)select 50 , 'yyy' , 5000
> select a.id, a.name, a.salary
> from #tt a
> where (a.id, a.salary) in
> (select b.id, min(b.salary)
> from #tt b
> group by b.id
> )
> --
> Regards,
> Jamie

First in sequence

A Java group put this out. Is there a way to make it work in SQL 2000 (work
s
fine in SQL 2005 using a partition by statement - only looking for a 2000
method)
create table #tt(id int,name varchar(3),salary int)
insert into #tt(id , name ,salary)select 10 as id, 'aaa'as name ,
3000 as salary
insert into #tt(id , name ,salary)select 10 , 'abc' , 3890
insert into #tt(id , name ,salary)select 10 , 'bbb', 2000
insert into #tt(id , name ,salary)select 20 , 'xyz' , 10000
insert into #tt(id , name ,salary)select 20 , 'are', 4000
insert into #tt(id , name ,salary)select 50 , 'yyy' , 5000
select a.id, a.name, a.salary
from #tt a
where (a.id, a.salary) in
(select b.id, min(b.salary)
from #tt b
group by b.id
)
Regards,
JamieTry:
select
a.id, a.name, a.salary
from
#tt a
inner join
(
select [id], min(salary) as min_salary
from #tt
group by [id]
) as b
on a.[id] = b.[id] and a.salary = b.min_salary
go
AMB
"thejamie" wrote:

> A Java group put this out. Is there a way to make it work in SQL 2000 (wo
rks
> fine in SQL 2005 using a partition by statement - only looking for a 2000
> method)
> create table #tt(id int,name varchar(3),salary int)
> insert into #tt(id , name ,salary)select 10 as id, 'aaa'as name ,
> 3000 as salary
> insert into #tt(id , name ,salary)select 10 , 'abc' , 3890
> insert into #tt(id , name ,salary)select 10 , 'bbb', 2000
> insert into #tt(id , name ,salary)select 20 , 'xyz' , 10000
> insert into #tt(id , name ,salary)select 20 , 'are', 4000
> insert into #tt(id , name ,salary)select 50 , 'yyy' , 5000
> select a.id, a.name, a.salary
> from #tt a
> where (a.id, a.salary) in
> (select b.id, min(b.salary)
> from #tt b
> group by b.id
> )
> --
> Regards,
> Jamie

Firing a java application from stored procedure

Hey all,
I've got a question and after doing some research I've found only a vague reference but no clear answer.

I have a java app that will be passing parameters to my stored procedure. I'll grab the requested info from the tables but instead of sending it back to the java app that sent the request, I need to send it to a "different" java app (the second java app will not be running at the time).

Can someone point me to a good source for executing java applications from a stored procedure?

Thanks in advance ...
tamSee this http://www.onjava.com/pub/a/onjava/2003/08/13/stored_procedures.html link is any help.

Another link http://www.sswug.org/searchresults.asp%3Fkeywordstofind%3Djava,%2520sto red%2520procedures for information.