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.