Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Sep 2002
    Posts
    33

    Unanswered: Locking problem?

    guys,

    i was hoping you would point me to the right place or explain if you have a moment. In our production environment we are experiencing lots of Timeouts and less Deadlocks. Like today we had huge traffic and registered around 4000 people and have gotten 61 errors (15 deadlocks, 46 timeouts). now this doesnt happen when we have less traffic...

    how i can investigate the problem. i monitored SQL server whole day and saw the Lock Requests/Sec peeks from time to time, i saw it go as high as 200,000

    is it normal to have that many lock requests? i suspect its the reason for our problems....

    please let me know if you need anymore information.

    any help is greatly appreciated.

    regards
    Last edited by shizox; 03-07-03 at 09:34.

  2. #2
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    I'm definetly not the man for this as I don't know alot about locking, but there might be one thing you can do on your select-statements and it looks something like this:

    SELECT * FROM myTable WITH (NOLOCK) WHERE Field = Value

    I definetly have to read up on locking myself, but this is what I have learned so far...
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

  3. #3
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    shizox, is the "traffic" generating allot of inserts or updates? I suspect they are. You might have lost a clustered index on one or more of your tables. This often creates a hot spot on the last page of a table and inserts will end up competing heavily for that last page.

    If your updates are broad then a rowlock can quickly escalate to a page or even a table lock.

    Issuing a select without the "NOLOCK" will cause your selects to wait until locks are cleared or a query timeout is reached. I would caution you against the use of NOLOCK as this will allow dirty reads of uncommitted data. This might work for your environment but usually doesn't as you have NO guarantee of repeatability.

    Ponder this and post back.
    Paul Young
    (Knowledge is power! Get some!)

  4. #4
    Join Date
    Sep 2002
    Posts
    33
    Thanks guys for your replies...

    I did go through all the tables and made sure we have all necessary indexes, i don't know where would the clustered index come handy instead of regular one. (is clustered the one that phisically reorders the table?)


    Paul, you are suggesting using WITH(ROWLOCK) on all the updates? Why would SQL lock pages or entire tables when i do an update on sigle row? isn't supposed to be a default?

    thanks again,
    wojo.

  5. #5
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Yes a clustered index physically reorders the table. So if you have a hot spot on the last page of your table (no clutered index) you might choose a random attribute to cluster arround. If you have an address table, the first address line would do while state or city might only help a little, either one would be better than no clustered index at all. Non-clustered indexs leave the table in physically inserted order so if 100 people are trying to insert at once they are all fighting over the last page of the table. Having a moderate fill factor and a clustered index will cause record inserts into different pages thus spreading you i/o out.

    Consider an update than can clearly single out one row for update, but there are no indexes or no index that compleatly cover your where clause. At some point you will be scanning the rows in the table, every time you move from one page to the next you will lock that page and hold that lock until your update completes. Lock enough pages and SQL server will escalate your update to a table lock.

    I am not suggesting using WITH(ROWLOCK) for updates or WITH(NOLOCK) for selects. IMHO SQL server will do a good job handling locking in a well tuned environment. Sometimes these hints are useful but not often.

    Clear as mud? Again, my suggestion is to add clustered indexes if you are not using them and re-check updates to varify resonable index coverage.
    Paul Young
    (Knowledge is power! Get some!)

  6. #6
    Join Date
    Sep 2002
    Posts
    33
    Thanks again Paul,

    I have another question about clustered indexes, how do i know which column to pick for clustered index since you can only have one. should it always be a key?

    And why is clustered better than regular index, i mean it puts the table in sorted order but the regular index does the same thing... sorry but i'm kinda lost in all that.

    regards,
    wojo

  7. #7
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Hum, tough question that. If I had a table getting lots of inserts I would choose to cluster around the most random attribut in order to spread out io. I would then add additional indexes to support updates and selects. If I had a table getting lots of select I would tend to cluster around common data. As an example I have to store LOTS of market data so I cluster around stock symbol and observed phenomenon (high, low, close).

    Having a clustered index on some keys is pointless. A clustered index on a sequential key like an identity column serves little use. If you can setup a clustered index that groups your data according to your needs AND helps with most all queries you are ahead of the game!

    Clustered indexes physically sort data. a Non-Clusterd aka regular index does not, in fact the physical order of the table is not afected by non-clustered indexes.
    Paul Young
    (Knowledge is power! Get some!)

  8. #8
    Join Date
    Sep 2002
    Posts
    33
    what do you mean you would create cluster on most random attribute? i alsways thought you should create cluster index on a column you use the most in WHERE clause (like ID), hmmm you totally lost me now :/

    sorry Paul if i'm bothering you but you seem to know a lot about all this and if you don't mind please elaborate more, if not i will understand.

    ps. i have another question but im gonna be posting it separate from this one.

    thanks again.
    wojo

  9. #9
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Lets consider what I think to be your problem, that is contingency over the last page in the table during many inserts. Lets go a bit further and say that there are three tables involved in 90% of the inserts and you have auto commit turned on. Each time you insert data you will lock the last page of the table and probably hold that lock till you have inserted into all three tables.

    Now if just one person is inserting data there is no problem as the locks are release before an attempt is made to aquire new locks for the next insert. Now, two people are inserting data. If the first guy has just inserted data in the first of thee tables and the second guy issues an insert the second guy will have to wait till the first guy release the lock on the first and all other tables. Again I doubt if either would notice the wait as we are talking about sub second time here. At some point if you add enough people inserting data you will notice a slowdown in response time as more and more people get in line for that last page of the first table.

    Now through in the fact that some people might have to insert data in other tables before they insert into the above mentioned three tables and you start holding more locks and you could get in to situation where a dead lock occures. Lets say you start your three table insert, at the same time I insert into a fourth table and they start the same three table insert as you and just after me a third person starts the same three table insert. Chances are if the wait is long enough I wil lbe a deadlock victom as the guy after me holds less resources and with me out of the way he can continue.

    I hope you are with me so far.

    Now add clustered indexes, on an arbitrary attribute, to some of the tables. Instead of everyone inserting into the last page of the first table, again from the above example, the inserts are spread out of many pages. Look at the Orders table in the Northwind DB if the clustered index is on OrderID we will all be fighting for the last page on inserts. If the clustered index were on ShipAddress chances are any two inserts would be on diffrent pages. Lets say You insert a record and the ShipAddress is '123 East Main' and I am inserting a record where the address is 'One West Main' , chances are we will be inserting into diffrent pages. Using the same example above lets add a third person inserting a record with the ShipAddress of '123 Eastern Main', that person will probably have to wait till you release the lock on the page you are inserting into but at least neither of you would be waiting on me.
    Last edited by Paul Young; 03-07-03 at 12:34.
    Paul Young
    (Knowledge is power! Get some!)

  10. #10
    Join Date
    Sep 2002
    Posts
    33
    Paul, thank you so much for taking your time to write this, i finally understand what you meant


    regards,
    wojo.

  11. #11
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Great, I was running out of examples!

    Now for the real question, would adding clustered index help your situation?
    Paul Young
    (Knowledge is power! Get some!)

  12. #12
    Join Date
    Sep 2002
    Posts
    33
    i will try adding one in the way you suggested and see if it helps


    thanks

  13. #13
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Just remeber that if you put a clustered index on a table you will need to re-build/re-create all other indexes AND you will hold a table lock until finished! Talk about the mother of all locks!
    Paul Young
    (Knowledge is power! Get some!)

  14. #14
    Join Date
    Dec 2002
    Posts
    1,245
    Clustered indexes are not restricted to a single column. You can have multiple columns in a clustered index (I tried to find out exactly what the limit is, but I did not find it readily).

    I have tables with three-column clustered indexes.

    Regards,

    Hugh Scott

    Originally posted by shizox
    Thanks again Paul,

    I have another question about clustered indexes, how do i know which column to pick for clustered index since you can only have one. should it always be a key?

    And why is clustered better than regular index, i mean it puts the table in sorted order but the regular index does the same thing... sorry but i'm kinda lost in all that.

    regards,
    wojo

  15. #15
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    I believe wojo meant that one can only have a single clustered index on a table NOT that only one column can be used in a clustered index.

    The limit to the number of columns in a clustered index is the number of columns in the table you are indexing.
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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