Solved

Simple Query Hangs

Posted on 2007-11-14
12
1,877 Views
Last Modified: 2010-04-21
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?  

Thanks!
0
Comment
Question by:gdemaria
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 31

Expert Comment

by:James Murrell
ID: 20284327
0
 
LVL 39

Author Comment

by:gdemaria
ID: 20284470
Thanks cs.  But I have tried "with (NOLOCK)" and it doesn't help.

Any idea how I can track down the code that is involved in the lock?
0
 
LVL 31

Accepted Solution

by:
James Murrell earned 250 total points
ID: 20284508
mmmmm found this

Listing 1: Procedure That Finds the Head of a Chain of Blockers
USE master
SET QUOTED_IDENTIFIER OFF
GO

IF EXISTS (SELECT * FROM sysobjects WHERE name='sp_leadblocker' AND type='P')
   DROP PROC sp_leadblocker
GO

CREATE PROCEDURE sp_leadblocker
AS
IF EXISTS
    (SELECT * FROM master.dbo.sysprocesses
    WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses))
    SELECT
        spid, status, loginame=substring(loginame, 1, 12),
        hostname=substring(hostname, 1, 12),
            blk=CONVERT(char(3), blocked),
            open_tran,
        dbname=substring(db_name(dbid),1,10),cmd,
            waittype, waittime, last_batch
        FROM master.dbo.sysprocesses
        WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
            AND blocked=0
ELSE
SELECT "No blocking processes found!"
GO
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 13

Expert Comment

by:Wizilling
ID: 20284584
start a sql profiler and you will be able to capture rpc starting and sql starting and rpc completed and sql st completed and deadlock and lock... (analyse the trace and u can find out which transaction were blocking and deadlocked)

Also doing a sp_who2 or trying the procedure above will help too.

also have u a index on the Fkey column of your table. Have they been rebuilt lately. This will speed up your query.
0
 
LVL 39

Author Comment

by:gdemaria
ID: 20284790
surprisingly, the script doesn't indicate any deadlocks.

I know I had deadlocks sometimes when run, other times the query just goes on for 4+ minutes.  I used to run this query along with dozens of others in db procedures in less then 4 seconds, now this one query takes 4 minutes with very small tables (less than a couple hundred records in most).    So its hard to see if its temporary deadlock or what..  

I will try to get sql profiler installed, thanks for that wiz..
0
 
LVL 13

Assisted Solution

by:Wizilling
Wizilling earned 250 total points
ID: 20285002
it would be worthwhile have the following statement in your sql statement /procedure) to avoid any locks. (if you are only interested in uncommitted data)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
0
 
LVL 39

Author Comment

by:gdemaria
ID: 20285051
Wiz, could you explain what that would do ?

Would I place it at the top of each procedure that I run?

thanks!
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 20285138
Run a check on the table, just to be sure there isn't some corruption that is causing this problem.

USE yourDbName
DBCC CHECKTABLE ('tableName')

Also, a full scan won't use an index, the = single value should.  So rebuild the indexes on the table.

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON tableName ( column1, ...)
WITH DROP_EXISTING

NOTE: Dropping and recreating the index(es) will make the table unavailable during that time.
0
 
LVL 13

Expert Comment

by:Wizilling
ID: 20285262
From BooksOnLine
READ UNCOMMITTED.

Specifies that statements can read rows that have been modified by other transactions but not yet committed.

Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions. When this option is set, it is possible to read uncommitted modifications, which are called dirty reads. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the isolation levels.

0
 
LVL 39

Author Closing Comment

by:gdemaria
ID: 31409233
Thanks for your help.  We had to put the project on hold because of this issue.  Sorry to have disappeared.  Your help was greatly appreciated!  Although the issue still exists and is still a mystery.  I will post new questions though..
0
 

Expert Comment

by:aleto1
ID: 24941550
hi, author, did you solve the problem ?, I have the same problem with a sql7  database, allways worked but since a few days ago, the same select, simply does not work,
0
 
LVL 39

Author Comment

by:gdemaria
ID: 24943842

My problem may be different from yours, this resolution was rather unique..

Our dual nic cards where not correctly set to auto detect causing packets to be dropped and the queries to get deadlocked or hung.   Other symptoms of the network card issue were slow copy times between folders on networked computers.

0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
The AZure backup problem 11 51
find SQL job run average duration 24 53
Import export tables 5 21
8 hour Continual coverage based on Time in and Time Out 9 17
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question