Link to home
Start Free TrialLog in
Avatar of bolox2
bolox2Flag for United Kingdom of Great Britain and Northern Ireland

asked on

SQL Timeout and locked entry

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 !!!!!
Avatar of bolox2
bolox2
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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. :(
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.
Avatar of bolox2

ASKER

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 ???
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?
Avatar of bolox2

ASKER

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 :(
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.
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()
Avatar of bolox2

ASKER

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)

:'(

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.
And, did you take my advice and change your ExecuteReader to the ExecuteNonQuery?
Avatar of bolox2

ASKER

as i said above....

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

ASKER

yes changed to exnonqry and still same :(

Im going to reqrite the prog tomorrow at this rate. :'(
Avatar of bolox2

ASKER

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    
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

Avatar of bolox2

ASKER

i ran the above in SQL and just replys with "command completed successfully" ???
What did i do wrong?
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.
Avatar of bolox2

ASKER

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 :):)
 
Avatar of bolox2

ASKER

AAArrrggghhhh i need this working soon i have a HUGE backlog of these to update :(
ASKER CERTIFIED SOLUTION
Avatar of mdougan
mdougan
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bolox2

ASKER

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)
Avatar of bolox2

ASKER

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
Avatar of bolox2

ASKER

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

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.
Avatar of bolox2

ASKER

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?
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?
Avatar of bolox2

ASKER

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
LOL

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

Cheers!
Mike