Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2011
    Posts
    5

    Unanswered: weird insert issue, might be index related

    Hello,
    I apologize if this has been posted before, I'm not even sure what to search for to try and find out what's going on.

    Basically, I have an SQL 2005 express database. This database is for my order processing software, called stoneedge. Stoneedge is a ms access based program that uses linked tables to connect to the sql database. One of the tables is for managing products, its called "inventory". This table has a "localsku" column, which is the primary key. the data type is varchar(200).

    For years the table worked fine, i could add as many skus as i wanted. then one day it started giving me issues with skus that start with the number 1. If a sku starts with a 1 then i cannot add it unless i kick everyone else out of our order managing software. I cannot even add the item directly into the database with an insert statement. Recently it started to get worse. now i cannot add any skus that start with an 8 or a 1, but if the sku starts with any other number or letter then it works fine.

    does anyone have any idea about what could be going on? the stoneedge tech support was unable to figure out the issue. They said it was related to the table indexing, hence the thread title. if you need more info from me then let me know.

    thanks

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    When you try to do the insert (directly in SQL Server), do you get an error, or does it hang for a period of time? If it hangs, then open a new connection to SQL Server, and run
    Code:
    sp_who2
    This will show you basic activity information. More importantly, it will show you blocking chains. Look for something that has a number in the BlkBy (Blocked By) column. This tells you what SPID is holding things up, and you can try to work out what they are doing to hold locks on the index or table.

  3. #3
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    I find it a bit strange that these issues are related to specific SKU numbers, so I wonder if this could be an issue related to the fact that you're running SQL Server express. What kind of error message do you get. Could it be that you've reached the 4GB database size limit of SQL Server express?

    If so, you may get error messages since SQL Server cannot split index pages as needed for insert of some SKU's, while others work just fine since there is room left in the index pages for that SKU.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  4. #4
    Join Date
    Jul 2011
    Posts
    5
    Hello,
    Thanks for the replies.

    @MrCrowley: It just hangs when i try the manual insert via sql. I will try out your suggestion very soon and hopefully it leads to a solution. thanks very much.

    @roac: i havent reached the 4gb limit, and it doesnt give any errors. plus, even right after 1 sku fails i can turn around and add another sku without an issue. Its pretty crazy.

    thanks

  5. #5
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Can you query sys.indexes for your object?
    Code:
    select * from sys.indexes where object_id = object_id('schema.objectname')
    Of particular interest to me are the two columns allow_page_locks and allow_row_locks. I've seen deadlocking schenarios when these two are 0 (false) for an object. If so, you could try to resolve the issue by enabling page and row locks for this index:
    Code:
    alter index indexname on schema.objectname
      set (allow_row_locks = on, allow_page_locks = on)
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  6. #6
    Join Date
    Jul 2011
    Posts
    5
    Hi Roac,
    I just queried the index and was able to get results. Both of those locking fields are already set to 1. I'd like to note that this index is clustered, but the rest are not clustered.

    I tried out MrCrowley's suggestion and learned that the block is due to another users select statement, from the same order managing software. Im wondering if turning those locking fields off would fix it, but at the same time i dont want to break anything.

    thanks
    Last edited by bkscott; 07-06-11 at 15:57. Reason: added a note

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Are there any triggers on your table?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Jul 2011
    Posts
    5
    Quote Originally Posted by blindman View Post
    Are there any triggers on your table?
    there are 2 triggers, both are for updates so i didnt think an insert would trigger them. am i correct about that?

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    That's odd. Usually a select statement gets in and gets out fairly quickly. I was expecting a hanging transaction from some other connection. Although, the application could be opening a recordset for update purposes.

  10. #10
    Join Date
    Jul 2011
    Posts
    5
    Quote Originally Posted by MCrowley View Post
    That's odd. Usually a select statement gets in and gets out fairly quickly. I was expecting a hanging transaction from some other connection. Although, the application could be opening a recordset for update purposes.
    im starting to believe that is what's going on, except i dont understand why only skus that start with certain characters are effected. ive seen a lot of strange issues over the years but this one has to be one of the weirdest that i've come across.

  11. #11
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    The root of the problem is that when locks are taken out at a page level, all items on the page are locked. This includes SKUs that are alphabetically close to the SKU (or set of SKUs) being locked.

Posting Permissions

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