Update statement times out on a small table

  • goussarova

    SSC Veteran

    Points: 256

    Hello all,

    I have a portal web site that shows the state of about 20 client computers in real time.

    I use SQL Server Express 10.50.4000.0 that runs on Microsoft Windows NT 5.2 (3790) server as a back end. Client computers (win 7) are connected to the network through wireless connections.

    I have a table on the server with one row per client computer. Each client computer updates its own row every second with the latest data. There should be no locking issues because each machine updates its own row.

    CREATE TABLE [dbo].[Table_Name](

    [col1] [char](12) NOT NULL,

    [col2] [varchar](20) NULL,

    [col3] [int] NULL,

    [col4] [varchar](20) NULL,

    [col5] [int] NULL,

    [col6] [int] NULL,

    [col7] [int] NULL,

    [col8] [char](1) NULL,

    [col9] [int] NULL,

    [col10] [int] NULL,

    [col11] [int] NULL,

    [col12] [int] NULL,

    [col13] [int] NULL,

    [col14] [int] NULL,

    [col15] [datetime] NULL,

    [col16] [int] NULL,

    [col17] [int] NULL,

    [col18] [int] NULL,

    [col19] [int] NULL,

    [col20] [int] NULL,

    PRIMARY KEY CLUSTERED

    (

    [col1] ASC

    )

    The portal web site runs a select of all the rows every 2 seconds. The machines and the site run 24/7.

    Everything works fine, except I am getting timeout errors: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Sometimes I get an additional A severe error occurred on the current command. The results, if any, should be discarded.

    I am getting about 10-15 errors every day, at random times.

    My assumption is that because the table is so small, it fits into one page, and when sql server places the lock for an update, it locks the page, and that page being the only page in the table, it locks the whole table.

    Since this application is just a portal, losing a random update is not a big deal - it only means that one of the little squares on the portal web site will 'skip a beat'.

    But I would really like to get rid of the timeout errors.

    I know that i can an update with NOLOCK, but that I would like to know ahead of time if this is what I need to do to get rid of timeouts. On every sql forum everyone advises against the NOLOCK, so I would like to hear from an expert that this is what I need.

    Could anyone please help me out.

    Thank you,

    Helen.

  • Gail Shaw

    SSC Guru

    Points: 1004424

    goussarova (12/26/2014)


    I know that i can an update with NOLOCK

    No you can't.

    Oh, you can put the hint there, but SQL ignores it. Updates are always, without exception, done with exclusive locks.

    Have you considered using read committed snapshot or snapshot isolation?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Jeff Moden

    SSC Guru

    Points: 993899

    Each client computer updates its own row every second with the latest data.

    1. Can we see the UPDATE code, please?

    2. Are there any other indexes on the table?

    3. What are the types of values in Col1?

    4. Have you checked the SQL Server logs for deadlocks?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • goussarova

    SSC Veteran

    Points: 256

    Thank you for replying to my question.

    1. Update is as straight forward as it gets:

    UPDATE dbo.Table_Name

    SET

    [Col2] = @Col2,

    [Col3] = @Col3,

    [Col4] = @Col4,

    [Col5] = @Col5,

    [Col6] = @Col6,

    [Col7] = @Col7,

    [Col8] = @Col8,

    [Col9] = @Col9,

    [Col10] = @Col10,

    [Col11] = @Col11,

    [Col12] = @Col12,

    [Col13] = @Col13,

    [Col14] = @Col14,

    [Col15] = @Col15,

    [Col16] = @Col16

    WHERE

    [Col1] = @Col1

    2. No, there are no other indexes. Table has only 20 records.

    3. Col1 has Char(12) values, like 'ABC 01 ', 'ABC 02 '

    4. Unfortunately I do not have enough rights to the database to check the log.

    As Gail Shaw mentioned, since the sql will always place an exclusive lock, and since every machine updates its own record, may be I should use row locking instead of page locking? If this is so, could you please tell me how?

  • Jeff Moden

    SSC Guru

    Points: 993899

    Yes, you could try the ROWLOCK hint.

    UPDATE dbo.Table_Name

    WITH (ROWLOCK)

    SET

    [Col2] = @Col2,

    [Col3] = @Col3,

    [Col4] = @Col4,

    [Col5] = @Col5,

    [Col6] = @Col6,

    [Col7] = @Col7,

    [Col8] = @Col8,

    [Col9] = @Col9,

    [Col10] = @Col10,

    [Col11] = @Col11,

    [Col12] = @Col12,

    [Col13] = @Col13,

    [Col14] = @Col14,

    [Col15] = @Col15,

    [Col16] = @Col16

    WHERE

    [Col1] = @Col1

    ;

    The UPDATE isn't inside a longer winded transaction, is it? And did you check to see if there are any triggers on the table?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Gail Shaw

    SSC Guru

    Points: 1004424

    goussarova (12/29/2014)


    As Gail Shaw mentioned, since the sql will always place an exclusive lock, and since every machine updates its own record, may be I should use row locking instead of page locking? If this is so, could you please tell me how?

    With an update like that and a clustered index on Col1, SQL's extremely likely to take a row lock anyway. Or do you have proof that it's not?

    Have you considered using read committed snapshot or snapshot isolation?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • goussarova

    SSC Veteran

    Points: 256

    Hi Gail,


    No, I have no proof of page locking.

    I have not considered read committed snapshot or snapshot isolation as I never used them before.

    I read about snapshot isolation this morning, and I got utterly lost.


    First, I found that there are at leasrt two different snapshot isolation flavours ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT, and I have no clue as to which one I should be using, or both.


    Second, do I need to use READCOMMITTED table hint in my UPDATE? Will I need to add any hints to my SELECT?


    Do you think that this is what I need in order to run my statements without timeouts?

    If so, could you please post some sample code of what needs to be done?

    Thank you so much for your help.

  • goussarova

    SSC Veteran

    Points: 256

    Sorry for the horizontal lines. I am new in this blog - I thought that hr would give me a line break, and by the time I was done composing my reply, my edit option expired. 🙁

  • Gail Shaw

    SSC Guru

    Points: 1004424

    goussarova (12/30/2014)


    Hi Gail,

    No, I have no proof of page locking.

    I have not considered read committed snapshot or snapshot isolation as I never used them before.

    I read about snapshot isolation this morning, and I got utterly lost.

    First, I found that there are at leasrt two different snapshot isolation flavours ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT, and I have no clue as to which one I should be using, or both.

    Maybe read some more? There are lots of blogs out. You need to understand what they do before using them.

    Second, do I need to use READCOMMITTED table hint in my UPDATE?

    No, why would you need any hints at all? Hints should be rare. Read Committed is the default isolation level anyway, hinting it is not going to change anything.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply