UPDATE on a Heap gone bad...

Question

Post reply

UPDATE on a Heap gone bad...

SQL_ME_RICH

SSChampion

Points: 11200
More actions
August 26, 2011 at 9:54 am

#242281

I had an issue where an UPDATE that I ran against a HEAP trashed all the records. Here is an example of the syntax that was used...
UPDATE [dbo].
SET ColumnA = X, ColumnB = Y
WHERE Column1 = 123
The result instead UPDATED the values for ColumnA and ColumnB into ALL of the records in the table (thus - trashing it).
So - what did I do wrong?
Thanks in advance...

Viewing 15 posts - 1 through 15 (of 25 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Didn't highlight the where clause?

Doesn't matter what the structure is, if run in its entirety, that will update the rows where Column1 = 123, only

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

SQL_ME_RICH SSChampion Points: 11200 More actions · Answer 2

That's what I thought, but that was not what ended up happening.

🙁

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

CREATE TABLE ThisIsAHeap (

Column1 INT IDENTITY,

ColumnA CHAR(1),

ColumnB CHAR(1)

)

-- 505 rows

INSERT INTO ThisIsAHeap (ColumnA, ColumnB)

SELECT LEFT(NAME,1), RIGHT(NAME,1) FROM sys.columns AS c

UPDATE [dbo].ThisIsAHeap

SET ColumnA = 'X', ColumnB = 'Y'

WHERE Column1 = 123

(1 row(s) affected)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Rich Yarger (8/26/2011)
That's what I thought, but that was not what ended up happening.
🙁

You either didn't select the where clause when you ran the update (quite common) or every row in the table has Column1 = 123

Edit: Or the actual query is mre complex and in simplifying you've lost the actual cause.

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

SQL_ME_RICH SSChampion Points: 11200 More actions · Answer 5

I should mention - this is an old school OLAP system (not an OLTP dbms). Not that it should matter (it's not a cube or using MDX - obviously), but the database is kind of - well, lets say that best practices in constructing it were not performed way back in the beginning. Is this leading to bigger issues with their tables and other objects in the DBMS? My thought on this is obvious.

SQL_ME_RICH SSChampion Points: 11200 More actions · Answer 6

SQL_ME_RICH

SSChampion

Points: 11200

August 26, 2011 at 10:13 am

#1373917

It was the only code in my session...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Rich Yarger (8/26/2011)
I should mention - this is an old school OLAP system (not an OLTP dbms). Not that it should matter (it's not a cube or using MDX - obviously), but the database is kind of - well, lets say that best practices in constructing it were not performed way back in the beginning.

Doesn't matter. The query you gave in the first post can only update rows where column1 = 123. If you're seeing something else then either your real scenario is different, you're not highlighting the where clause when running the query or you're encountered a catastrophic bug that no one else has seen.

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

SQL_ME_RICH SSChampion Points: 11200 More actions · Answer 8

3 lines of code - that was all.

Identity column was in place on the first column (Column1 in my example) which is why I used it in my WHERE clause.

I'm truly describing what I did. I'm pretty stumped by it. Nothing was highlighted and run partially. I just executed it with F5 and WHAMO...226 records updated.

It really sucked, but luckily it was a semi-static logging table outside of the PROD system.

SQL 2008 R2. Do not know edition nor CU/SP levels (if any).

***EDIT*** 3 lines of code - not 4.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 9

This is a good illustration of why you should always run adhoc updates inside a transaction.

If you see the wrong number or rows updated, you can just roll it back. If everything looks OK, then commit it

-- Check expected number of rows to be updated

select count(*) from [dbo].

WHERE Column1 = 123

begin transaction

UPDATE [dbo].

SET ColumnA = X, ColumnB = Y

WHERE Column1 = 123

/*

rollback

commit

*/

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 10

I've not been able to find the Microsoft document on this problem but we had precisely the same problem way back in SQL Server 2000. It turned out that it was, in fact, a fault in SQL Server but only if certain indexes were in play and only if parallelism took place and it didn't matter if it was a heap or not. Supposedly, sp4 in SQL Server 2000 took care of the problem.

The work around we were given by MS was to use OPTION (MAXDOP 1) on all UPDATEs until MS could come up with a fix and we verified the work around DID work correctly.

I don't know if I can find it again, but I'll search for the documentation that MS provided for a hotfix in SS 2000. As a side bar, I think it sucks that this problem has reared its ugly head again.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Brandie Tarvin SSC Guru Points: 173061 More actions · Answer 11

Rich Yarger (8/26/2011)
I should mention - this is an old school OLAP system (not an OLTP dbms). Not that it should matter (it's not a cube or using MDX - obviously), but the database is kind of - well, lets say that best practices in constructing it were not performed way back in the beginning. Is this leading to bigger issues with their tables and other objects in the DBMS? My thought on this is obvious.

Do you have a cascading trigger between this table and others that may feed back into this table once the update is done?

EDIT: I didn't see Jeff's post before I made my post. Sorry.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

Any chance somebody else had decided to run a similar query at approximately the same time?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

Michael Valentine Jones (8/26/2011)
This is a good illustration of why you should always run adhoc updates inside a transaction.
If you see the wrong number or rows updated, you can just roll it back. If everything looks OK, then commit it
-- Check expected number of rows to be updated
select count(*) from [dbo].
WHERE Column1 = 123
begin transaction
UPDATE [dbo].
SET ColumnA = X, ColumnB = Y
WHERE Column1 = 123
/*
rollback
commit
*/

Same reason I also like to make a copy of the table into a temp backup table when performing these adhoc updates. The table can always be dumped later.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQL_ME_RICH SSChampion Points: 11200 More actions · Answer 14

Guys and Gals - thank you for your best practices and support here. Needless to say - this ended up being a bit of an issue for me with the client that I was working with. I'm alright (for now), but trust me when I say that it didn't come off really impressive.

I'll make sure to run updates like this as a transaction in the future. I dunno - I know I didn't highlight anything, but this goes to show you just how complex something as simple as a single row update can become (if not executed with consequences in mind).

Jeff - if you find anything on the same issue you encountered years ago with SQL 2000, I would be interested in seeing it.

Thanks again, all of you!

Rich