December 26, 2014 at 8:03 am
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.
December 26, 2014 at 8:50 am
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
December 26, 2014 at 11:40 am
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
Change is inevitable... Change for the better is not.
December 29, 2014 at 3:02 pm
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?
December 29, 2014 at 7:47 pm
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
Change is inevitable... Change for the better is not.
December 30, 2014 at 1:30 am
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
December 30, 2014 at 7:33 am
Hi Gail,
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.
If so, could you please post some sample code of what needs to be done?
Thank you so much for your help.
December 30, 2014 at 7:38 am
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. 🙁
December 30, 2014 at 10:27 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply