Results 1 to 8 of 8
  1. #1
    Join Date
    May 2004
    Posts
    125

    Post Unanswered: Revisiting the locking issues with Sql2000

    ok, about a few months ago I brought up the issues we were having with deadlocks. My company is running a .net data entry application hitting against a sql 2000 box. Forget hardware since we have more then enough hardware to handle the load.

    The issue we are seeing is we get a process that blocks a table, and the bulding up of block process that cause timeouts/deadlocks etc. A couple of things we see when this happens is that .net on the web server seems to open up more and more connections(it seems to double or triple the connections as the blocking begins), we start to see deadlocks and we see alot of timeouts in the app due to the blocking.

    The blocking aways happen on an insert of an order or a customer. One thing I keep wondering about is how we handle the unique id for our tables. Our DBA had us put in the following:

    Begin Trans
    select max(id) + 1
    insert....
    commit trans

    I've heard from a few here and in other forums this is the wrong way to go. I'm still get a gut feeling that this could start causing issues like what we are seeing. My problem is I'm not knowledgable enough to make a strong enough case with it.

    What I need help is getting enough information to either point to how we manage ID's or at the very least rule them out. I've done searchs in google and some other search engines but there doesnt seem to be any good articles on how best to handle unique ids and show a good case for it. I do know I can use sql identity, or even guid with the newid() function but I need solid proof before my boss will move to one of these methods. Any links you guys can give or even your own write up will really help my case and hopefully help my headache.

    Thanks Ahead of time

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I hope at least that you have an index on the id column. I do know that the spike in connections is caused by the blocking, so you are right in thinking that solving the blocking problem will solve the connection problem.

    How long does the insert take? If it is a row or two to a couple of nice normalized tables, they shouldn't cause so much trouble. If you are inserting into 100 column tables, then you have some redesigning ahead of you.

    I am going to guess that the DBA wants you to save off the max(id) + 1 as your ID. This is a fairly common need, but can be accomplished better through stored procedures. Are you supporting SQL 2000 as well as another RDBMS? If so, then you might not be able to get around this, as you have to play to the lowest common denominator. I am not sure if Oracle, DB2 or any of the others have a new_id () - like function.

  3. #3
    Join Date
    May 2004
    Posts
    125
    Yeah indexing is in place and from what I can tell, done correctly. We update statistics every night and reindex maybe once a week or so now. The inserts is one at a time but we have about 100-150 users at any given time inserting orders not to mention data we pull in from the websites(10 different sites pulling in around 5000-6000 orders a day). The ugly part of the websites is that it is a cursor that does maybe 50 to 60 orders one at a time(we run the webimports once an hr for each site). We just can't seem to narrow it down and its driving us crazy.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Yeah, IDENTITY would have made your life much easier, and you probably would not have had those blocking issues. You can and probably should add a table hint to the table where MAX id is stored, but...

    The problem with this volume and concurrency is that using:

    BEGIN TRAN
    select @id=max(id)+1 from tbl (HOLDLOCK)
    INSERT...
    ...
    COMMIT TRAN

    would ensure consistency of data, but the duration of each transaction would also ensure that you'll continue having timeouts.

    ...And before certain "someone" jumps in to promote GUID, I'd say I'm all for it, but try to convince your developers and especially users that typing 5AA729BB-466C-40E8-A487-1C375CFB965B is easier than any sequencial number...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Cursor that is run once per hour? Hmm. This could be a part of the problem. First thing is going to be finding out who is blocking whom. Try this out, and modify it to find out who is doing what:

    Code:
    select spid, cpu, physical_io, memusage, blocked, waittype, lastwaittype, waitresource
    from master..sysprocesses
    where blocked > 0
    union 
    select spid, cpu, physical_io, memusage, blocked, waittype, lastwaittype, waitresource
    from master..sysprocesses
    where spid in (select blocked from master..sysprocesses)
    You will probably want to add either program_name, hostname, or loginame to the above queries, depending on how you can tell the various processes apart.

    If the ID column is indexed, then the select max(id) + 1 should take out just a rowlock on that one row. The cursor, on the other hand, could be doing all sorts of things to the back of your database. Let us know who is doing all the blocking. Then we can figure out what to do about it.

  6. #6
    Join Date
    May 2004
    Posts
    125
    Actually who is blocking who I can answer. Its actually across the board. If it was one particular process that would cause it we would have hunted that down. Sometimes it locks when the web imports comes in. When that happens, sometimes its the cursor, sometimes its somone out on the floor taking the order over the phone. Sometimes it happens when the web import is not occuring. This is the frustrating part...there really isn't a partern to it...

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Since no one process is to blame, have you narrowed it down to this one table with the ID column?

    How long does it take to do the insert? If the insert goes over about 2 seconds (6000 inserts/8 hour shift comes out to one insert per 5 seconds, so taking gusts into account..), then you should be OK. Otherwise, blocking on the select is almost guaranteed.

    As a wild-a** idea, you could build off both ends of the table, and have some processes add orders with select min(id) - 1 for their IDs. That may only get you so far, though.

    I think the consensus is going to be try to eliminate the select part altogether, and get to an identity or new_id() way of doing things. It sounds like you do not have to worry about cross-platform or migration issues.

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    You may be able to put a band-aid of sorts on this system. After thinking it over, this may be the second system I have seen in my career that could benefit from the (nolock) hint. If you use the (nolock) hint on the select max(id) + 1 query, then it will not be blocked by the insert queries. HOWEVER, you will run a slightly higher risk of inserting (or trying to insert) duplicate IDs into the table. I think this is already a minor problem in the design, because two connections could select the same max(id) if they are run close enough together. The increase in risk is that this time period will now expand by a few milliseconds, to wit, the amount of time between when the insert query sets up all of its locks, and when the insert actually happens. I doubt it is a long period of time (or at least I hope it is not), so you may get away with it. Just remember, (nolock) is not to be used lightly. If you do a search on (nolock) in this forum, you will probably find a number of posts saying do not use it. As was pointed out before, using either an identity column, or using new_id() would be a much better fix for this situation. This would only ever be a band-aid buying you a little time to implement a better solution. Good luck.

Posting Permissions

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