Strange update behaviour

Question

Strange update behaviour

cmullen77 29296

Valued Member

Points: 71
More actions
August 10, 2016 at 6:23 pm

#329178

Okay, I MUST be missing something;
update EmpActions set isactive = 'N' where 1=2
When I run this, my MS SQL server goes to about 33% and just hangs there, and the query never returns.
There is a trigger on this table, but with the where clause being what it is, I don't see how that should make any difference....
Has anyone ever seen anything like this?
Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

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

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

cmullen77 29296 (8/10/2016)
Okay, I MUST be missing something;
update EmpActions set isactive = 'N' where 1=2
When I run this, my MS SQL server goes to about 33% and just hangs there, and the query never returns.
There is a trigger on this table, but with the where clause being what it is, I don't see how that should make any difference....
Has anyone ever seen anything like this?
Thanks

There's WHERE 1=2 clause should prevent any rows from being updated. What is your update intended for?

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

cmullen77 29296 Valued Member Points: 71 More actions · Answer 2

Hey Alan,

That was my point.. I had an issue running an update statement, so I started eliminating rows to figure out what was causing my problem. Eventually I discovered that the problem ONLY happened when the condition in the where clause caused no rows to be targeted for the update.

The update runs using variables, and my work around was to check to see if any records existed prior to running the update statement.

The odd thing to me was, why should an update on a table where the where clause excludes all rows (because of date ranges, etc), be a problem. To test, I put in a 1 = 2 (no rows ever), and found that I couldn't run the simplest of updates on this table when NO rows are selected for update...

I'm just baffled as to why....

Chris

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

cmullen77 29296 (8/10/2016)
Hey Alan,
That was my point.. I had an issue running an update statement, so I started eliminating rows to figure out what was causing my problem. Eventually I discovered that the problem ONLY happened when the condition in the where clause caused no rows to be targeted for the update.
The update runs using variables, and my work around was to check to see if any records existed prior to running the update statement.
The odd thing to me was, why should an update on a table where the where clause excludes all rows (because of date ranges, etc), be a problem. To test, I put in a 1 = 2 (no rows ever), and found that I couldn't run the simplest of updates on this table when NO rows are selected for update...
I'm just baffled as to why....
Chris

That's what I assumed. I almost never use triggers accept for some old-school auditing scenarios (I'm not a fan of triggers) so I'm kind of a trigger novice. That said, my best guess would be that it has to do with the trigger. Two questions:

1. Have you tried disabling the trigger to exclude/identify it as the culprit?

2. Have you attempted running this query with OPTION (RECOMPILE)?

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

cmullen77 29296 Valued Member Points: 71 More actions · Answer 4

No, but thats a good thought... I'll try disabling the trigger and see what happens-

I'll let you know tomorrow when I'm back in the office-

Chris

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

cmullen77 29296 (8/10/2016)
Hey Alan,
That was my point.. I had an issue running an update statement, so I started eliminating rows to figure out what was causing my problem. Eventually I discovered that the problem ONLY happened when the condition in the where clause caused no rows to be targeted for the update.
The update runs using variables, and my work around was to check to see if any records existed prior to running the update statement.
The odd thing to me was, why should an update on a table where the where clause excludes all rows (because of date ranges, etc), be a problem. To test, I put in a 1 = 2 (no rows ever), and found that I couldn't run the simplest of updates on this table when NO rows are selected for update...
I'm just baffled as to why....
Chris

Can you post up an estimated plan, Chris?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

cmullen77 29296 Valued Member Points: 71 More actions · Answer 6

cmullen77 29296

Valued Member

Points: 71

August 11, 2016 at 9:53 am

#1895037

I can sure try!

Thanks,

Chris

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Thanks.

The UPDATE affects the PK. Is this PK referenced by FK constraints in other tables? If so, are they indexed?

Edit: or it would, if there were any rows affected...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

cmullen77 29296 Valued Member Points: 71 More actions · Answer 8

Right?! That's what has me baffled... If I put a value in the where clause that actually returns a row, this update runs just fine. Its only when NO rows are returned, that it just straight up hangs. Never comes back.

So weird...

DouglasH SSCommitted Points: 1669 More actions · Answer 9

Apologies if I'm stating the obvious as there are some big hitters on this thread but the trigger will fire exactly once if no rows are affected (or a million).

There's either something bizarre going on with the logic in the trigger OR the tables/indexes involved are corrupted.

For something like this I'd start a Profiler trace (including the individual sql statements -starting and completed- of the trigger) and see what's going on. Usual caveats apply about running Profiler if it's a production system.

Post back with your findings.

cmullen77 29296 Valued Member Points: 71 More actions · Answer 10

Well I had no idea that a trigger would fire even if nothing is inserted/updated...

I modified the trigger to verify that SOMETHING was inserted before proceeding and the issue was resolved.

Thanks Douglas for that bit of insight! I never knew this about triggers-

Chris

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

cmullen77 29296 (8/11/2016)
Well I had no idea that a trigger would fire even if nothing is inserted/updated...
I modified the trigger to verify that SOMETHING was inserted before proceeding and the issue was resolved.
Thanks Douglas for that bit of insight! I never knew this about triggers-
Chris

Good trigger code almost always references the inserted/deleted tables. If your trigger code wasn't, then you might want to post it up here for folks to peer review.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden