| Author |
Topic |
|
robsher
Starting Member
3 Posts |
 Posted - 2009-10-15 : 06:06:12
|
| Hi there,Using SQLServer 2005, we are having problems with a particular field having it's data overwritten with seemingly random letters and numbers at different times of the week/day. This happens to random records but each time they are all adjacent to each other in the table.We have been trying to monitor this in order to pinpoint what/who is causing this, but we have so far not been able to find a cause.However, I do remember around the same time this started happening, we ran an UPDATE query using the WITH NOLOCK hint as SQLServer was saying that the record could not be updated, even though we did not have it open at the time.Could using NOLOCK with an update query cause the above symptoms to occur? This is still happening at random times of the week, although we have not used the NOLOCK hint since we first tried it which was about 1 month ago.Any insights on the topic would be really appreciated...Thanks |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2009-10-15 : 06:15:23
|
| A NOLOCK hint will simply be ignored for an update...there is no way to bypass locking when updating data in a table.- Lumbagohttp://xkcd.com/327/ |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-10-15 : 06:15:41
|
| Nolock is ignored in an update query. Only selects can run without a lock. Updates (and inserts/deletes) always lock exclusive regardless of the isolation level.Run a trace and watch for updates to the affected tables?Run CheckDB to see if it's data corruption (don't think it is)--Gail ShawSQL Server MVP |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2009-10-15 : 06:17:22
|
From BOL about UPDATE statement: quote:
WITH ( <Table_Hint_Limited> )Specifies one or more table hints that are allowed for a target table. The WITH keyword and the parentheses are required. NOLOCK and READUNCOMMITTED are not allowed. For information about table hints, see Table Hints (Transact-SQL).
- Lumbagohttp://xkcd.com/327/ |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2009-10-15 : 06:19:01
|
 - Lumbagohttp://xkcd.com/327/ |
|
|
|
robsher
Starting Member
3 Posts |
Posted - 2009-10-15 : 07:42:59
|
| Hi all,Lumbago - thanks for the info on NOLOCK vis-a-vis updates. It's funny though as the update worked when we used NOLOCK and it didn't without. How about using NOLOCK with SELECT then - could that cause data to actually corrupt or would it just be the actual SELECT query output that may be inaccurate?GilaMonster - We have run both of these. No consistency issues. For trace, at the moment we can't see a pattern to when this happens and we don't have the space for all the trace logs that the Trace is producing to leave it on all the time. At present we have scheduled an hourly query to query the affected fields and look for changes. That way we can try to find a timing pattern and run the Trace when we expect things to happen.Cheers guys.Rob |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2009-10-15 : 08:02:42
|
| Using NOLOCK in a select will expose you to reading uncommitted or "dirty" data and you should be really careful when doing this. I find it very hard to believe that this will corrupt your data in any way, but as you say; the query output may be inaccurate and can cause inconsistencies. But I don't think this nolock-stuff is the root of your problems...- Lumbagohttp://xkcd.com/327/ |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-10-15 : 08:33:53
|
quote:
Originally posted by robsher
How about using NOLOCK with SELECT then - could that cause data to actually corrupt or would it just be the actual SELECT query output that may be inaccurate?
There's no query or combination of queries that will cause data to corrupt (unless you run into a SQL bug).I suspect you've got something like SQL Injection here. Someone/something's running malicious updates against the server. Is this server behind a web app? --Gail ShawSQL Server MVP |
|
|
|
robsher
Starting Member
3 Posts |
Posted - 2009-10-15 : 13:27:50
|
| Hi guys,Lombago - thanks for confirming that.Gila - It's a VB application but not on a WAN. It's used via Citrix which adds another potential issue into the equation. We have another version of this app which has has some light corruption to the data, but only a few records. Part of the testing we are doing will involve looking at whether this is application related although we have not changed the application recently...I think it may be worth us resetting the (virtual) SQL Server just to see if that helps. Maybe it's tired!!!Thanks for your help guys. |
|
|
|
|
NOLOCK hint and Data Corruption