Solved

SQL Timeout and locked entry

Posted on 2008-10-02
27
974 Views
Last Modified: 2012-05-05
My vb.net app has been working for many months and all of a sudden i am getting a timeout message when trying to do an SQL update from my code. If i run from the MSSQL window i can add manually fine.

I notice that when i am adding it freezes and then if i do a sp_who2 it shows a number of SUSPENDED status for my database communications.

im doing a dbase loop and inside that loop in the following code:

  Dim myConnection As SqlConnection
                Dim myCommand As SqlCommand
                Dim dr As SqlDataReader

                myConnection = New SqlConnection("server=localhost;uid=sa;pwd=mypassword;database=databasename")
                'you need to provide password for sql server
                myConnection.Open()
                myCommand = New SqlCommand(sql, myConnection)
                dr = myCommand.ExecuteReader

                dr.Close()
                myConnection.Close()

I have tried several sql update methods, and have set both command and connection timeouts to 0 and 1000 of which does nothing (sits there waiting for infinity or times out after 1000 seconds)

Its really bugging me and have no idea what to do. I have rebooted server and restarted services tp np avail

HELP !!!!!
0
Comment
Question by:bolox2
  • 15
  • 12
27 Comments
 

Author Comment

by:bolox2
ID: 22624796
it sticks at this point:

dr = myCommand.ExecuteReader

The SQL it is using is fine (i can use the generated SQL fine in the MSSQL Query Window, And the SQL is very short. :(
0
 
LVL 18

Expert Comment

by:mdougan
ID: 22625001
I doubt this is the cause of your hanging, but is there any particular reason why you are using ExecuteReader to perform an Update?  Normally, you would do this:

myCommand.ExecuteNonQuery()

You'd have to post the content of your SQL, it is possible that the update is interfering with a sub-select that is part of the statement.  Are you sending up straight SQL or are you calling a stored procedure on the database?

I forget what sp_who2 shows, but what you want to display are blocking locks, and see if any of your process id's are blocked.  When you can figure out what's blocking them, we can maybe determine why.

In general, when you have other code that selects against large tables in your database you should include the WITH (NO LOCK)  clause...

ie
SELECT * FROM MYBIGTABLE WITH (NO LOCK)
JOIN MYOTHERBIGTABLE WITH (NO LOCK)
ON MYBIGTABLE.X = MYOTHERBIGTABLE.X

That helps keep your selects from taking blocking locks against updates.
0
 

Author Comment

by:bolox2
ID: 22625041
Thank you for your prompt response,

Here is my SQL:


                Dim sql As String
                sql = ("UPDATE Eclips Set Status = 'DONE'" _
                & ", NLAArticleID = '" & NLAArticleID & "'" _
                & ", Day = '" & Day & "'" _
                & ", Page_Section = '" & Page_Section & "'" _
                & ", Article_Percentage = '" & Article_Percentage & "'" _
                & ", Publication_Name = '" & Publication_Name & "'" _
                & ", Publication_Acronym = '" & Publication_Acronym & "'" _
                & ", Edition = '" & Edition & "'" _
                & ", Publication_Date = '" & Publication_Date & "'" _
                & ", WordCount = '" & WordCount & "'" _
                & ", Body = '" & Replace(Body, "'", "''") & "'" _
                & ", PageNumbers = '" & Page_Numbers & "'" _
                & ", Headline = '" & Replace(Headline, "'", "''") & "'" _
                & ", SubHeadline = '" & Replace(SubHeadline, "'", "''") & "'" _
                & ", PhotoCaption = '" & Replace(Photo_Caption, "'", "''") & "'" _
                & ", Publication_SubSource = '" & Replace(Publication_SubSource, "'", "''") & "'" _
                & ", Publication_Region = '" & Replace(Publication_Region, "'", "''") & "'" _
                & " WHERE OriginalFilename = '" & Replace(Filename, "c:\XML\", "") & "'")



What is happening is that an XML file is being opened and segments are being stripped out of each node to create the container for the above.

I read a couple of places about the (NO BLOCK) method, how would that fit in my SQL ???
0
 
LVL 18

Expert Comment

by:mdougan
ID: 22625533
The   WITH (NO LOCK)   statement only applies to SELECT statements.  See, select statements don't want the data to change until it has finished selecting all of the data and returns it to whomever has requested it.  So, let's say that someone else issues a big select statement to retrieve 1 million rows.  The select statement will put update locks on rows as it selects them, so, let's say it has selected the first thousand rows and is continuing to select all million rows, but, you go in and try to update row 500.  You are going to get a blocking lock until their select statement finishes.  Selecting 1 million rows takes a long time, so, you might timeout.

If the select statement contained the WITH (NO LOCK)  statement as I gave an example above, then it wouldn't take out update locks as it selects the record, so, you might go in and change row 500.  The only danger is that whoever selected that million rows is going to be looking at data for row 500 that is out of date.  But, generally, that's OK.  That's always an issue in dynamic environments where the data changes frequently.

What you want to do is display the Query that is running in the process id that contains a blocking lock that is blocking your update.  I think we took sp_who2 and added some stuff to give us more information, but sp_who2 might have enough to do this.  Once you can identify the query, you can consider updating that query so that it contains the WITH (NO LOCK)  statement.  That might help your problem.

I don't see anything wrong with your SQL that would cause the delay.  How big is the Eclips table and does it have any update triggers on it?  Does it have very many indexes?  Any full-text indexes?
0
 

Author Comment

by:bolox2
ID: 22632567
there are about 250,000 records in it at the mo (groving 5000 per day)

There are no triggers at all. Do you think i should archive the data maybe ? I would like the data to be somewhere searchable tho?

How would you add the "LOCK" optiuon to my SQL where would it go in the syntax ?

Sorry my name is Bolox for a reason :(
0
 
LVL 18

Expert Comment

by:mdougan
ID: 22633145
No, 250,000 records is not that many, though, growing 5000 a day is pretty fast.  You shouldn't have any problems selecting records until you hit tens of millions of records.  No need to archive at this point.

As I said, the WITH (NO LOCK) is only for SELECT statements, not for UPDATE statements, so, there is no place in this query where you can add that statement.  However, I was suggesting that there may be other queries that are running against this table, perhaps someone is searching this table?  And you could add the WITH (NO LOCK) to that query, as that query is doing a SELECT.  I suspect that one or more SELECT queries are blocking your update.  So, there really isn't anything wrong with your update.
0
 
LVL 18

Expert Comment

by:mdougan
ID: 22633165
You also never explained why you are trying to return a DataReader

dr = myCommand.ExecuteReader

You should remove any references to the object  dr and instead execute this statement

myCommand.ExecuteNonQuery()
0
 

Author Comment

by:bolox2
ID: 22758264
AArrgghhhhhh, this started to work agaion and now its doing it again !!!

ANy other ideas?


The SQL Server Profiler takes the SQL and adds to SQL:BatchStarted, but does not complete.

Strangely, if i close my program down it DOESupdate that one record it was working on (70% of the time that is)

:'(

0
 
LVL 18

Expert Comment

by:mdougan
ID: 22758999
The next time that your update hangs, run the system stored procedure called  sp_who2  (or something like that).  I believe it displays which process id's are blocked by which other process ids.  Identify what is running in the process that is blocking your update process id.  Once you know what query or process is causing the block, then we can figure out how to fix it.
0
 
LVL 18

Expert Comment

by:mdougan
ID: 22759011
And, did you take my advice and change your ExecuteReader to the ExecuteNonQuery?
0
 

Author Comment

by:bolox2
ID: 22759024
as i said above....

What do you need to see? I did the sp_who in the start with no joy
0
 

Author Comment

by:bolox2
ID: 22759038
yes changed to exnonqry and still same :(

Im going to reqrite the prog tomorrow at this rate. :'(
0
 

Author Comment

by:bolox2
ID: 22759079
This is  sp_who2 with nowt running:
1     BACKGROUND                     sa   .   . NULL RESOURCE MONITOR 0 0 10/20 14:13:39                                                1     0    
2     BACKGROUND                     sa   .   . NULL LAZY WRITER      16 0 10/20 14:13:39                                                2     0    
3     SUSPENDED                      sa   .   . NULL LOG WRITER       156 0 10/20 14:13:39                                                3     0    
4     BACKGROUND                     sa   .   . NULL LOCK MONITOR     0 0 10/20 14:13:39                                                4     0    
5     BACKGROUND                     sa   .   . master SIGNAL HANDLER   0 0 10/20 14:13:39                                                5     0    
6     sleeping                       sa   .   . master TASK MANAGER     0 0 10/20 14:13:39                                                6     0    
7     BACKGROUND                     sa   .   . master TRACE QUEUE TASK 0 0 10/20 14:13:39                                                7     0    
8     sleeping                       sa   .   . NULL UNKNOWN TOKEN    0 0 10/20 14:13:39                                                8     0    
9     BACKGROUND                     sa   .   . master BRKR TASK        0 0 10/20 14:13:39                                                9     0    
10    SUSPENDED                      sa   .   . master CHECKPOINT       0 18 10/20 14:13:39                                                10    0    
11    BACKGROUND                     sa   .   . master TASK MANAGER     0 0 10/20 14:13:39                                                11    0    
12    BACKGROUND                     sa   .   . master BRKR EVENT HNDLR 0 51 10/20 14:13:39                                                12    0    
13    sleeping                       sa   .   . master TASK MANAGER     0 0 10/20 14:13:39                                                13    0    
14    BACKGROUND                     sa   .   . master BRKR TASK        0 0 10/20 14:13:39                                                14    0    
15    sleeping                       sa   .   . master TASK MANAGER     0 0 10/20 14:13:39                                                15    0    
16    sleeping                       sa   .   . master TASK MANAGER     0 0 10/20 14:13:39                                                16    0    
17    sleeping                       sa   .   . master TASK MANAGER     0 0 10/20 14:13:39                                                17    0    
18    sleeping                       sa   .   . master TASK MANAGER     0 0 10/20 14:13:39                                                18    0    
19    sleeping                       sa   .   . master TASK MANAGER     0 0 10/20 14:13:39                                                19    0    
20    sleeping                       sa   .   . master TASK MANAGER     0 0 10/20 14:13:39                                                20    0    
51    sleeping                       DMSFTP\Administrator DMSFTP   . master AWAITING COMMAND 155 253 10/20 15:05:19 Microsoft SQL Server Management Studio         51    0    
52    sleeping                       DMSFTP\Administrator DMSFTP   . ECLIPS AWAITING COMMAND 282 41 10/20 16:16:02 Microsoft SQL Server Management Studio         52    0    
53    RUNNABLE                       DMSFTP\Administrator DMSFTP   . ECLIPS SELECT INTO      78 12 10/20 16:42:14 Microsoft SQL Server Management Studio - Query 53    0    
54    sleeping                       DMSFTP\Administrator DMSFTP   . msdb AWAITING COMMAND 62 59 10/20 14:13:57 SQLAgent - Generic Refresher                   54    0    
58    sleeping                       DMSFTP\Administrator DMSFTP   . ECLIPS AWAITING COMMAND 570796 67 10/20 16:06:00 Microsoft SQL Server Management Studio - Query 58    0    
 
And here is the remainder  adfter the lock:
57    SUSPENDED                      sa DMSFTP           . ECLIPS SELECT           0 0 10/20 16:43:37 .Net SqlClient Data Provider                   57    0    
57    SUSPENDED                       DMSFTP           . ECLIPS SELECT           31 0 10/20 16:43:37 .Net SqlClient Data Provider                   57    0    
57    SUSPENDED                       DMSFTP           . ECLIPS SELECT           31 0 10/20 16:43:37 .Net SqlClient Data Provider                   57    0    
57    SUSPENDED                       DMSFTP           . ECLIPS SELECT           31 0 10/20 16:43:37 .Net SqlClient Data Provider                   57    0    
57    SUSPENDED                       DMSFTP           . ECLIPS SELECT           31 0 10/20 16:43:37 .Net SqlClient Data Provider                   57    0    
57    SUSPENDED                       DMSFTP           . ECLIPS SELECT           31 0 10/20 16:43:37 .Net SqlClient Data Provider                   57    0    
57    SUSPENDED                       DMSFTP           . ECLIPS SELECT           31 0 10/20 16:43:37 .Net SqlClient Data Provider                   57    0    
57    SUSPENDED                       DMSFTP           . ECLIPS SELECT           31 0 10/20 16:43:37 .Net SqlClient Data Provider                   57    0    
57    SUSPENDED                       DMSFTP           . ECLIPS SELECT           31 0 10/20 16:43:37 .Net SqlClient Data Provider                   57    0    
58    sleeping                       DMSFTP\Administrator DMSFTP           . ECLIPS AWAITING COMMAND 570796 67 10/20 16:06:00 Microsoft SQL Server Management Studio - Query 58    0    
59    SUSPENDED                      sa DMSFTP         57    ECLIPS UPDATE           0 0 10/20 16:43:37 .Net SqlClient Data Provider                   59    0    
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 18

Expert Comment

by:mdougan
ID: 22761899
hard to know what columns you're displaying without the column headers!  We modified our proc, which maybe is more useful.  Try compiling this and running it,then display the results with column headers if possible.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[util_who]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[util_who]

GO
 

SET QUOTED_IDENTIFIER ON 

GO

SET ANSI_NULLS ON 

GO
 
 

CREATE  proc dbo.util_who

as begin
 

select spid

	,Status        =

                  CASE lower(status)

                     When 'sleeping' Then lower(status)

                     Else                 upper(status)

                  END

--        ,nt_username=rtrim(nt_username)

        ,loginame=rtrim(loginame)

	,hostname1=rtrim(s.hostname)

	,blk=convert(char(5),blocked)

	,dbname = case

					when dbid = 0 then null

					when dbid <> 0 then db_name(dbid)

				end

	,cmd

	,program_name

	,cpu

	,physical_io

	,memusage

        ,open_tran

	,waittime

	,waittype

	,blocked

	,lastwaittype

	   

from  master.dbo.sysprocesses s

where spid >= 0 and spid <= 32767

order by cpu desc, spid
 

end

GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO

Open in new window

0
 

Author Comment

by:bolox2
ID: 22794612
i ran the above in SQL and just replys with "command completed successfully" ???
What did i do wrong?
0
 
LVL 18

Expert Comment

by:mdougan
ID: 22795513
Well, after you run the above, that creates a new stored procedure called  util_who.  Then, instead of running sp_who2,  try running util_who and see what output it gives you, when you're experiencing this hanging.
0
 

Author Comment

by:bolox2
ID: 22797890
in a nushell it is refering to :
LCK_M_IX (on the update im doing)
i had a brief look online about this but. :'( can u put it in english :)
After all Bolox is my name for a bloody good reason :)
Will up the points if you can help me :):)
 
0
 

Author Comment

by:bolox2
ID: 22797926
AAArrrggghhhh i need this working soon i have a HUGE backlog of these to update :(
0
 
LVL 18

Accepted Solution

by:
mdougan earned 500 total points
ID: 22798214
That lock is called Lock with Intent for an Exclusive lock.  I'm not sure if that is referring to the lock that you are trying to put on the table, because you're trying to update the table, or whether that is the lock that is blocking your process.  Here is a link to a support page that talks about how to diagnose blocking problems.

http://support.microsoft.com/kb/224453/

Skip down to where it says "1. Identify the SPID at the head of the blocking chain. "

They have you look in the tree, in Enterprise Manager, under servers, then locks, and you can find out which Process IDs have blocking locks.  These are the processes that are hanging you up.  Once you identify which processes are blocking, you can take a peek at what the query is, that is doing the blocking, by running this command:

DBCC INPUTBUFFER (<spid>)

where you replace the <spid> with the process id of the "blocking" process.  That will tell you what query is causing the problem.  Blocks are a bit like a long car crash.  One process will lock something that another process needs, so, the second process waits.  However, the second process has already locked another resource that a third process needs, so the third process has to wait on the second process which is waiting on the first process.  Nobody can move until the first process is cleared.

So, you have to identify what each query is, behind each of these processes that are blocking other processes.  Then, you can maybe find a way to keep those processes from locking resources.
0
 

Author Closing Comment

by:bolox2
ID: 31502404
Thank you for your assistance, The real annoyance is that it has suddenly (again) decided to start working :(



So i guess i will have to try this when it fails again (which will prob be in a couple of weeks)



Thank you for your assistance, it is most appreciiated

Bolox (aka Gavin)
0
 

Author Comment

by:bolox2
ID: 22875105
Ok problem is back again..................
I ran the "DBCC INPUTBUFFER (<spid>)" and the SQL command that returns is fine, (once the app is ended that is running it i put the SQL into a QRY Window and ran in no time at all.
Where should i be looking to correct this??
 
Sorry to keep dragging this out, its driving me crazy and has put me VERY behind on my project
0
 

Author Comment

by:bolox2
ID: 22876968
is is the dabaase ent from my code: if this helps anyone: I open up an XML file get the relevant data, close the file, then execute the SQL below.. It works so well, then it just dies with this stupid timeout !!!
 
AArrgghhhhhh
 


 

                Dim sql As String

                sql = ("UPDATE Eclips Set Status = 'DONE'" _

                & ", NLAArticleID = '" & NLAArticleID & "'" _

                & ", Day = '" & Day & "'" _

                & ", Page_Section = '" & Page_Section & "'" _

                & ", Article_Percentage = '" & Article_Percentage & "'" _

                & ", Publication_Name = '" & Publication_Name & "'" _

                & ", Publication_Acronym = '" & Publication_Acronym & "'" _

                & ", Edition = '" & Edition & "'" _

                & ", Publication_Date = '" & Publication_Date & "'" _

                & ", WordCount = '" & WordCount & "'" _

                & ", Body = '" & Replace(Body, "'", "''") & "'" _

                & ", PageNumbers = '" & Page_Numbers & "'" _

                & ", Headline = '" & Replace(Headline, "'", "''") & "'" _

                & ", SubHeadline = '" & Replace(SubHeadline, "'", "''") & "'" _

                & ", PhotoCaption = '" & Replace(Photo_Caption, "'", "''") & "'" _

                & ", Publication_SubSource = '" & Replace(Publication_SubSource, "'", "''") & "'" _

                & ", Publication_Region = '" & Replace(Publication_Region, "'", "''") & "'" _

                & " WHERE OriginalFilename = '" & Replace(Filename, "c:\XML\", "") & "'")
 
 

                MyNode = Nothing
 
 

                MyDoc = Nothing
 
 

                Dim myConnection As SqlConnection

                Dim myCommand As SqlCommand
 

                myConnection = New SqlConnection("Data Source=192.168.0.76;Initial Catalog=BATABASE;Persist Security Info=True;User ID=sa;Password=Password$tein;Connection Timeout=0")

                myConnection.Open()

                myCommand = New SqlCommand(sql, myConnection)

                myCommand.ExecuteNonQuery()

                myConnection.Close()

                'MsgBox(sql)

Open in new window

0
 
LVL 18

Expert Comment

by:mdougan
ID: 22878343
OK, so, just to be sure we're on the same page, when your update boggs down, you search for SPID's that have Exclusive (Blocking) Locks.  Then, you run the DBCC on those SPIDs?  You have to run the DBCC while that query has the Blocking Lock, otherwise the process id can be reused by another query.  Can you post one or two of the queries that hold the blocking locks?

Your update query is fine.  You might check to see if OriginalFileName is an indexed column, just to ensure that your update isn't slowing down because it is having to do a table scan to find the record to update.
0
 

Author Comment

by:bolox2
ID: 22878521
Hmmmm, indexing.....................
 
Making sense a little now.........., i see how to Create an Index using:
CREATE INDEX IDX_CUSTOMER_LAST_NAME
on CUSTOMER (Last_Name)  for exmaple, but
How would i use this?
0
 
LVL 18

Expert Comment

by:mdougan
ID: 22886143
Well, I was suggesting that it might be a good idea to have an index on the OriginalFileName field in the table you are trying to update.  This is because you are searching on that field in your WHERE clause of the UPDATE statement.

Once you create an index, with a statement like the one you have above, the optimizer will automatically use the index when running a query, you don't have to do anything else.  The difference can be very significant in performance.  Without an index, the database has to search through every data page of your table to find the record that you want to update.  If you have a lot of fields in your table, that can be a lot of data pages.  When you create an index, there is a separate smaller space for the index, and the database only has to scan through this smaller space, to find the index of the record to update, then it can go directly to that data page to do the update.

Adding indexes on fields that are commonly used in your SELECT statements, as well as your UPDATE statements is stongly suggested.  This will also help reduce the number of blocking locks taken on the table.  What fields most commonly occur in your WHERE clauses?
0
 

Author Comment

by:bolox2
ID: 22886193
OH MY FUKIN GOD !!!!! I THINK I LOVE YOU !!!!
ExpertsExchange how can i up my points so that this member gets even more points than he deserves !!!!!
It tuend out i need the NOLOCK (on the Select around this UPDATE) and also the indexing :)
Im so god damn happy !!!
.
.
I think i just pissed myself in happyness :D
0
 
LVL 18

Expert Comment

by:mdougan
ID: 22886874
LOL

No extra points needed.  Glad you finally got a solution!

Cheers!
Mike
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now