SQL Server 2005..
I have a database procedure that has always worked quickly, recently it is hanging. I narrowed it down to one query with multiple joins, then dissected it to a single table and a simple where clause.
When entered directly into SQL Management Studio, this query hangs...
select * from myTable where Fkey = 123
Yet this query returns immediately..
select * from myTable
The column "Fkey" is a foreign key to a parent table. There is a foreign key constraint defined on it.
The database is in use, after a reboot, I can fetch from the table OK for a few minutes, then it just hangs. My last run (which I let go on and on) took 11 minutes to complete. There are only 100 records in this table !
After 11 minutes, this message was returned..
[Macromedia][SQLServer JDBC Driver][SQLServer]Transaction (Process ID 60) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
So if its a deadlock and all I'm doing is a select.. how do I figure out why its deadlocking and how do I keep it from happening?