UPDATE when the values are the same

Question

UPDATE when the values are the same

Viewing 15 posts - 106 through 120 (of 159 total)

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

Paul White SSC Guru Points: 150467 More actions · Answer 1

Sergiy (9/6/2012)
SQL Kiwi (9/6/2012)
Sergiy (9/6/2012)
SQL Kiwi (9/6/2012)
Taking a shared lock would not give the same behaviour as taking an exclusive lock.
Can you bring sopme details please?
The two lock types are different, and so have different effects. An exclusive lock might block concurrent reads, for example.
But why do you need to block concurent reads if the data is not actually being changed?

No idea, actually. Perhaps it's just simpler to always take an exclusive lock prior to possibly changing data. Perhaps it wouldn't be safe to allow people to acquire a 'U' lock. Perhaps another connection running at a different isolation level requires it (thinking serializable here). Perhaps it is required because of some other subtle interaction inside the server code itself. Who knows.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Sergiy SSC Guru Points: 110208 More actions · Answer 2

I've come accross a very ineresting thing.

I was trying to test for some other case, but the original test geve me some different result.

I was running this:

BEGIN TRANSACTION

UPDATE dbo.test

SET name = 'Tom'

WHERE NAME = 'Tom'

SELECT [Current LSN] ,

Operation ,

Context ,

[Transaction ID] ,

AllocUnitId ,

AllocUnitName ,

[Checkpoint Begin] ,

[Num Transactions] ,

[Checkpoint End] ,

[Dirty Pages] ,

[Log Record] FROM fn_dblog(NULL, null)

EXEC sp_lock

COMMIT

Checkpoint

and have got this outcome:

I could not make any sense out of it until I checked the table definitions in the Object Explorer:

Oh, system statistics...

They might have been updated and that's what gives me dirty pages!

But - why would Statistics pages become dirty if no writing is performed against the data page?

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 3

SQL Kiwi (9/6/2012)
No idea, actually. Perhaps it's just simpler to always take an exclusive lock prior to possibly changing data. Perhaps it wouldn't be safe to allow people to acquire a 'U' lock. Perhaps another connection running at a different isolation level requires it (thinking serializable here). Perhaps it is required because of some other subtle interaction inside the server code itself. Who knows.

Or - if you trust BOL on this:

Exclusive (X)

Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.

_____________
Code for TallyGenerator

Paul White SSC Guru Points: 150467 More actions · Answer 4

Sergiy (9/6/2012)
Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.

I thought you were asking why a shared lock wouldn't be enough to prevent a concurrent update? I'm really not sure what question you are asking now. Perhaps you could explain more clearly?

Are you agreeing that even logical changes (where the data stays the same) must be separated?

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 5

Sergiy (9/6/2012)
But - why would Statistics pages become dirty if no writing is performed against the data page?

See http://technet.microsoft.com/en-us/library/cc966425.aspx (a white paper referenced by BOL)

Statistics updates are triggered when a modification threshold is exceeded. The modification counters count logical changes - even rolled back changes count. It's not easy to see colmodctr directly, but rowmodctr is easy enough:

SELECT s.indid, s.rowmodctr

FROM sys.sysindexes AS s

WHERE s.id = OBJECT_ID(N'dbo.Test', N'U');

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Sergiy SSC Guru Points: 110208 More actions · Answer 6

SQL Kiwi (9/6/2012)
Sergiy (9/6/2012)
Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.
I thought you were asking why a shared lock wouldn't be enough to prevent a concurrent update? I'm really not sure what question you are asking now. Perhaps you could explain more clearly?

Which update?

You said there is no update because SQL Server can figure out that the is no change in data before hand and do not proceed with any update.

Perhaps you could explain more clearly?

_____________
Code for TallyGenerator

Paul White SSC Guru Points: 150467 More actions · Answer 7

Sergiy (9/6/2012)
You said there is no update because SQL Server can figure out that the is no change in data before hand and do not proceed with any update. Perhaps you could explain more clearly?

The logical update operation. If you don't stop being snarky in your replies, I am reluctantly going to stop assisting you.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Sergiy SSC Guru Points: 110208 More actions · Answer 8

Sergiy (9/6/2012)
SQL Kiwi (9/6/2012)
No idea, actually. Perhaps it's just simpler to always take an exclusive lock prior to possibly changing data. Perhaps it wouldn't be safe to allow people to acquire a 'U' lock. Perhaps another connection running at a different isolation level requires it (thinking serializable here). Perhaps it is required because of some other subtle interaction inside the server code itself. Who knows.
Or - if you trust BOL on this:
Exclusive (X)
Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.

Which is different from:

Update (U)

Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.

Is it your case?

"Potentially updating resources"?

So, why don't we see U locks in our examples?

Or even these locks:

Shared (S)

Used for read operations that do not change or update data, such as a SELECT statement.

as SQL knows there is no change in data and does not actually write anything?

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 9

SQL Kiwi (9/6/2012)
The logical update operation.

What exactly is logical update operation and what other kind of update operatios are there?

If you don't stop being snarky in your replies

You may notice I just quoted you. Word to word.

If you wannt me to stop being snarky stop being snarky (and snobby) yourself.

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 10

SQL Kiwi (9/6/2012)
Statistics updates are triggered when a modification threshold is exceeded. The modification counters count logical changes - even rolled back changes count.

So, there are modifications?

SQL Server givees you another prove that the row is actually written to.

Can you agree now?

_____________
Code for TallyGenerator

Paul White SSC Guru Points: 150467 More actions · Answer 11

Sergiy (9/6/2012)
Is it your case? "Potentially updating resources"? So, why don't we see U locks in our examples?

Update locks are taken when reading rows that might qualify for a change to reduce the change of a conversion deadlock. Conversion deadlock:

Process 1 reads a row, takes a shared lock.

Process 2 reads a row, also takes a shared lock.

Process 1 attempts to convert its shared lock to exclusive, and is blocked by process 2.

Process 2 attempts to convert its shared lock to exclusive, and is blocked by process 1.

Taking update locks resolves this problem. It has nothing to do with non-updating updates.

SQL Server takes exclusive locks before potentially modifying a row.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 12

Sergiy (9/6/2012)
SQL Kiwi (9/6/2012)
The logical update operation.
What exactly is logical update operation and what other kind of update operatios are there?

I am trying to help you distinguish between a logical update that does not change anything, and a physical update, which changes the page, sets the is_modified bit, and results in checkpoint flushing the page to disk at some point.

If you don't stop being snarky in your replies
You may notice I just quoted you. Word to word.

And that is exactly the problem. My question to you was genuine. You were just being snarky.

If you wannt me to stop being snarky stop being snarky (and snobby) yourself.

What on earth have I said to deserve this?

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 13

Sergiy (9/7/2012)
So, there are modifications? SQL Server givees you another prove that the row is actually written to. Can you agree now?

A modification counter used by statistics update is increased, yes. Rolled-back changes, and changes that don't change anything all count.

What do you want me to agree to? I have said many times that no-one knows if the page is physically written to or not. You don't know, I don't know. What I do know is that the thing that matters is whether the page is marked as changed and flushed to disk by checkpoint or not.

We established long ago that non-updating updates do not set the page-changed flag, and do not result in the page being written to disk.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Sergiy SSC Guru Points: 110208 More actions · Answer 14

SQL Kiwi (9/7/2012)
If you don't stop being snarky in your replies
You may notice I just quoted you. Word to word.
And that is exactly the problem. My question to you was genuine. You were just being snarky.
Can you imagine my question was also genuine?
I just did not want to appear aggressive with my question (because of the cultural difference mentioned above), so I used the wording which is approved to be OK.

What on earth have I said to deserve this?

Something like this:

"I am reluctantly going to stop assisting you."

Aren't we both looking for a truth here?

Don't you think you might be wrong on this topic?

Just like you've been wrong on the topic about index fragmentation?

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 15

SQL Kiwi (9/7/2012)
A modification counter used by statistics update is increased, yes. Rolled-back changes, and changes that don't change anything all count.

Key word - changes.

No matter if it's rolled back - there was a change. IO operation.

Even if everything remained like it was before - there was a change made.

If you remove a screw from a wall and then immediately put it back - it's all the same after all.

The operation is rolled back, the operation did not change anything.

But operation was still performed.

Actually 2 operations - do and un-do.

Energy and resources spent.

That's the fact.

You cannot get the proof of it from the screw in the wall (unless you were not careful and damaged something around).

But you can get a proof from you body temperature or electricity meter (if you used an electric screwdriver).

Same with data. To get the proof you need analyse side indicators, like a temperature of the memory chip.

And it will surely indicate that X lock means actual writing, as they say in BOL.

Isn't it what the OP question was about?

_____________
Code for TallyGenerator