CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Neo
Neo

Reputation: 16219

Is it correct to put WITH (NOLOCK, READUNCOMMITTED) inside update block in select statement?

 UPDATE
        BondPrices
    SET
        MarketValueOwned = Holdings.Amount
    FROM
        BondPrices BondPrices
        INNER JOIN
        (
            SELECT
                PM.SecurityId,
                SUM(Pos.QuantityTraded * Pos.Mark) AS Amount
            FROM
                Position Pos --WITH (NOLOCK, READUNCOMMITTED)
                INNER JOIN
                PositionMaster PM --WITH (NOLOCK, READUNCOMMITTED)
                       ON
                    Pos.PositionMasterId = PM.PositionMasterId
            WHERE
                Pos.Date = @ReportDate 
            GROUP BY
                PM.SecurityId
        ) Holdings ON
            BondPrices.SecurityId = Holdings.SecurityId
    WHERE
        BondPrices.Date = @ReportDate

please help me is that correct that i put WITH (NOLOCK, READUNCOMMITTED) inside select statement of update block? i need to do this for reporting purpose but i'm confused whether it is correct or not?

Upvotes: 1

Views: 8539

Answers (2)

Michał Powaga
Michał Powaga

Reputation: 23183

According to Table Hints (Transact-SQL):

READUNCOMMITTED and NOLOCK cannot be specified for tables modified by insert, update, or delete operations. The SQL Server query optimizer ignores the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement.

Tables Fireball..Position and Fireball_Reporting..PositionMaster aren't modified so this hint should be fine.

Edit:

I assume that you wanted write READUNCOMMITTED instead of UNCOMMITTED.

Upvotes: 1

Andrew
Andrew

Reputation: 27294

It's hard to judge whether the NOLOCK hint is correct or not without understanding why it was placed in there.

The intention is that it will place no row / pages locks and should not get blocked from reading, but it doesnt prevent locking entirely since a Sch-S lock (Schema Stability Lock) will still be issued.

The no lock comes at a price however, you could read uncommited / dirty data and not be transactionally consistent, a NOLOCK can even theoretically read the same row twice.

The UNCOMMITTED hint doesnt make sense in that is not a valid hint - it is perhaps supposed to be READUNCOMMITED, which is another way of specifying NOLOCK - so that part seems pointless.

Upvotes: 2

Related Questions