varbinary(max) and NOLOCK in SQL 2008

  • I'm trying to decide whether there would be any negative repercussions in using WITH (NOLOCK) when selecting a row that contains a varbinary(max) field.

    Lets say the binary column contains a 25 meg chunk of data. What happens when a stored procedure is in the middle of a SELECT against that row WITH (NOLOCK) and while the data is being transmitted (big row, takes more than a split second...) a user executes a DELETE or UPDATE on the same row?

    The default behavior in SQL 2008 would be for the DELETE/UPDATE to wait until the SELECT was finished... but what happens if nolock is specified? I would expect a DELETE would have to wait until the SELECT was done anyway, but could an UPDATE cause the currently-running SELECT to terminate early? Or would UPDATE wait until the SELECT was finished even with NOLOCK specified?

  • what the nolock hint does is tell SQL server you will accept uncommitted data. If the application doing the select truly doesn't care whether the data is accurate AND using the nolock hint actually does something for you then go for it.

    Otherwise don't use it.

    The probability of survival is inversely proportional to the angle of arrival.

  • The whole point of With NoLock is the other way around. If someone is in the middle of deleting or updating a row, With NoLock will still read the read as if it hadn't been touched. It's about reading dirty data.

    Here's what you should do: Create a test database, set up a table that has the kind of structure you want, including a varbinary(max) or varchar(max) column in it. Add data till you have rows that are the kind of size you're looking for. (You can either randomly generate it, or use MP3 files for large varbinary data, or something like that.) Then, from one connection, issue a select with a nolock hint, and from a second connection, issue a delete or update on the same row. Using the WaitFor Time command, you can make sure the commands overlap or are "simultaneous". See what happens. Try this with multiple rows at the same time.

    When you do that, you'll know what to expect. Until you do, you'll just have what you've been told. There's a big difference between something you've been told, and something you know.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 3 posts - 1 through 2 (of 2 total)

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