Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Oct 2003
    Posts
    268

    Unanswered: SQL Server locks?

    I have a simple web application using ASP.NET/C#/SQL Server 2000/Windows 2000

    I'm having problems with the server "hanging" for ~15 minutes. Disk/CPU are idle so this isn't a resource issue. In Enterprise Manager, under "Current Activity" I see several processes waiting on:

    LCK_M_X
    LCK_M_S
    LCK_M_S
    NETWORKIO

    How do I investigate this? My application is so simple, I have trouble imagining how a lock-related problem could ocurr? There is absolutely no concurrent processing done on this server. There is only one user running this app and there are no separate threads spawned.

    Although this problem is sporadic, this happens the most while doing the following:

    - Open connection1 (for SELECT)
    - Open connection2 (for UPDATE)
    - Open SqlDataReader through connection1 with SELECT statement
    - Iterate through
    - For certain records issue UPDATE statement via connection2.

    Here are some sample code snippets. Default ADO.NET options and everything should be cleaned up through C# "using" blocks without relying on garbage collection.

    Code:
    using (SqlConnection selectConnection = LeadsDatabase.OpenConnection()) {
    using (SqlConnection updateConnection = LeadsDatabase.OpenConnection()) {
    using (SqlCommand dbCommand = new SqlCommand(sql, selectConnection)) {
    using (SqlDataReader reader = dbCommand.ExecuteReader()) {
    while (reader.Read()) {
    using (SqlCommand updateCommand = new SqlCommand(sql, updateConnection)) {
    updateCommand.ExecuteNonQuery();

  2. #2
    Join Date
    Jan 2005
    Posts
    6
    What are the queries? Do both the update and select reference the same table(s)?

  3. #3
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    Sounds like you have a blocking problem:

    Updater is blocked by Reader
    Reader is blocked by (waiting on) NetworkIO.

    Since Reader is not reading, the NetworkIO lock will never be released. You are not deadlocked because the Reader is not waiting on the Updater - it is waiting on itself (the client).

    SQL Server has to buffer results at the client and at the server. If the client buffer isn't clearing out fast enough to allow the server buffer to clear, you leave NETWORKIO locks.

    The solution is to not leave any results unread before attempting an update. However, since you are relying on .NET abstracting that for(?) you, you don't have any control over that in the interface.

    You should be able to solve this by using WITH NOLOCK in your Select statement.

    If 'several' > 2, I'm confused how you have 'several' processes with only one user... multiple instances of the same application?

  4. #4
    Join Date
    Oct 2003
    Posts
    268
    gonzo37, the queries are somewhat long and the details are mostly irrelevant. Yes they are both accessing the same tables.

    MaxA, brilliant! I think that is exactly what is happening. I will try out your NOLOCK suggestion.

    I believe there are only two relevant processes:

    - the SELECT waiting on NETWORKIO (aka the client app) which is waiting on the UPDATE.
    - the UPDATE waiting on a lock held by the SELECT.

    There are a few more processes since the single user (which isn't me) is closing his browser and retrying in hopes that it will work better. But those processes really aren't significant in this.

    Thanks!

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Forget NOLOCK and fix your process.

    NOLOCK will cause you all kinds of grief.

    Keep your transacions as short as possible.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    Hmmmmm - NOLOCK will cause all kinds of grief for a single-user system? Would you also recommend HOLDLOCK and/or SET TRANSACTION ISOLATION LEVEL SERIALIZABLE?

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Yeah, MaxA, that's good one there, I am with you on it! This blind (no pun intended?) dismissal of lowering TIL is just like listening to old folks talking about dangers of driving: "Sure you can drive, but you'll get into a car wreck. Don't come to us crying, because we told you so!"

    But I would recommend to move out of the realm of the front-end and have it as a scheduled task On-Deman, or a stored procedure. This way you won't have this situation (guaranteed!).
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Dec 2004
    Posts
    47
    I use the below query to monitor if any lock is getting created.

    SELECT spid, cmd, status, loginame, open_tran,
    datediff(s, last_batch, getdate ()) AS [WaitTime(s)]
    FROM master..sysprocesses p
    WHERE open_tran > 0
    AND spid > 50
    AND datediff (s, last_batch, getdate ()) > 30
    ANd EXISTS (SELECT * FROM master..syslockinfo l
    WHERE req_spid = p.spid AND rsc_type <> 2)

    e.g:

    While executing the it gave the below result,

    spid cmd status loginame open_tran WaitTime(s)
    ------ ---------------- ------------------------------ -------------------------------------------------------------------------------------------- --------- -----------
    61 SELECT INTO runnable 2 3612



    (1 row(s) affected)

    Iam not sure if this is provide any help in this scenario but still to monitor where its getting locked we can use this after running the appln....

  9. #9
    Join Date
    Oct 2003
    Posts
    268
    Quote Originally Posted by Brett Kaiser
    Forget NOLOCK and fix your process.

    NOLOCK will cause you all kinds of grief.

    Keep your transacions as short as possible.
    Adding the NOLOCK hint to my SELECT caused my "hanging" problem to go away. I'm very happy!

    But I would like to know: What is the preferred method or cleaner route to doing this?

    My code needs to do the following. BTW, I must stress that this is VERY simple from a db perspective:

    Do a SELECT query.
    For every row, do some tests at the client application level
    For certain rows, issue an UPDATE against that row.

    (Before someone suggests it, no, I can't make this a completely set based operation or completely T-SQL, and it does need to be procedural.)

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by rdjabarov
    "Sure you can drive, but you'll get into a car wreck. Don't come to us crying, because we told you so!"

    But I would recommend to move out of the realm of the front-end and have it as a scheduled task On-Deman, or a stored procedure. This way you won't have this situation (guaranteed!).
    That's funny...can you talk out of both sides at the same time.

    I mean you know better...


    They've locked all of the data with SELECT and the RS, and if they don't manage to shoot themselves in the foot, the SELECT Display will be inaccurate anyway because of the update.

    Now let's say the want to use that datya which is old, to perform another update.

    BOOM

    You just blew away your original update.

    But hey, it's only a single user application (what is that btw?)

    Why would you use sql server for a "single user" application?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    But I would like to know: What is the preferred method or cleaner route to doing this?

    Depends on your business rules and how your user interacts with the interface. One approach is to only return a page of results at a time to ensure you don't leave any NETWORKIO locks behind. This is nontrivial and sometimes controversial (let's see what happens on this thread).

    Here's how it works:
    Your reader sends a pagesize and pagenumber parameter to your stored procedure (and often other parameters that affect the WHERE clause and the ORDER BY clause) and the stored procedure returns pagenumber (or less, if the user is on the last page) rows to the client.

    One application for which I use this method has 1.5 million rows, and a typical query (w/o paging) may return between 10 and 84,000 rows (you don't want to stick those in a datareader). This is a state-less web application with a web farm of 20 servers; thus, subsequent requests are likely to be handled by a different web server (what a waste of memory when the average pagesize is 50 or less, the average result set is 500 rows and we're handling 45 requests per second during peak usage).

    Here's pseudo code (borrowed from another discussion group by a coworker) that returns the second page, with 10 rows per page:

    -- First, return the count so you can display Page y of n
    Select Count(1) As 'rowcount'
    From mytable
    Where Column3 = N'Something'

    -- Then, return the rows
    Select derived1.*
    From
    (Select Top 20 Column1_PK, Column2, Column3
    From mytable
    Where Column3 = N'Something'
    Order By Column2 Desc, Column1_PK) derived1
    Left Join
    (Select Top 10 Column1_PK
    From mytable
    Where Column3 = N'Something'
    Order By Column2 Desc, Column1_PK) derived2
    On derived1.Column1_PK = derived2.Column1_PK
    Where derived2.Column1_PK Is Null
    Order By Column2 Desc, Column1_PK

    Note: For this to be deterministic, your Primary Key (Column1_PK in this example) must always be included in the Order By statements.

    This is the third version of paging I've tried (the others used temporary tables) and it has been the most efficient (our average execution time is < 500 ms). Our stored procedure is actually much more complex as we host web sites and provide a tool that allows our customers to design searches for their visitors. And, of course, we provide the search engine.

    Finally, if one of the roles you fulfill at your organization is DBA, it is your responsibility to understand the business and when you must be able to perform repeatable reads (w/o phantom inserts; like a banking application) and when returning potentially 'dirty data' is okay (80% or more of the time in my experience; for example, a job search).


    We are all captives of the pictures in our heads; our belief that the world we have experienced is the world that really exists. Walter Lippman

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by RogerWilco
    Adding the NOLOCK hint to my SELECT caused my "hanging" problem to go away. I'm very happy!..
    Hey, check out your own arguments in this thread. "What if the future release does not support table lock hints???" Well, there was a reason why I didn't buy this argument back then, because it does.

    Brett, your "scenario" will work ONLY in either fully asyncronous environment with several connections, or in multi-threaded situation. Take your pick, and then form your argument accordingly

    MaxA, 1.5MB is not gonna impress Kurt, he's working with 10-times-larger numbers.

    Kurt, I guess I am the one who makes this recommendation. At this point I don't see any compelling reason to leave this logic on the front-end. It may not be set-based, but it does belong on the database side.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  13. #13
    Join Date
    Oct 2003
    Posts
    268
    Thanks guys. Robert, you are right, I would always not like to rely on the behavior of a hint when possible.

    FYI, what I'm doing is iterating through records which include name, address, phone, etc and performing lots of validation logic on them. I'm iterating through a recordset and doing UPDATES on bad rows. The logic we have is pretty elaborate and can't be done in T=SQL. Also, in the future we'd like to hook into third party COM components that do Address validation and correction and zip code processing and phone number matching. All this logic needs to be done at the application level and can't be done in a stored proc.

    The two suggestions I'm hearing are:
    - Move the UPDATE logic out of application code and into the database as a stored proc or something similar. As I just said, this really isn't practical.
    - Read the data in pages to bypass the locking issues.

    How about using a different ADO.NET construct that allows writing back to the record data?

    I don't see how dirty reads are possible. The UPDATES only happen after the data has been read.

    Brett, I said that this process is "single user" since for the sake of the problem at hand there really is only one relevant client thread/process. There is much more complexity to the overall system which warrants using SQL Server as opposed to Access or flat files but those details really aren't relevant here.

    Thanks again guys for all the help and feedback!

  14. #14
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You can bring in COM functionality into T-SQL providing that it's out-of-process component, or if it's a fully tested in-process one. Look up sp_OAxxx in BOL.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by rdjabarov
    Brett, your "scenario" will work ONLY in either fully asyncronous environment with several connections, or in multi-threaded situation.

    I'm sorry...is there any other kind?

    why would anyone want to build a bottleneck as part of system design?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •