locked
update with nolock RRS feed

  • Question

  • where can I use 'with(nolock)' in the following statement?

    set @string = 'update ' + QUOTENAME(@Schema1) + '.' + QUOTENAME(@TableName1)+ 'SET ' + QUOTENAME(@ColumnName1) + ' = dbo.func_RegExReplace(''' + @RecordValue + ''',''' + @ValidNumber + ''',replace(space(len(''' + @ValidNumber +''')),' + ''' ''' + ',' + '''X''' + ')) where ' + @Pkey 

    • Moved by Tom Phillips Monday, October 1, 2012 3:41 PM TSQL question (From:SQL Server Database Engine)
    • Changed type Naomi N Tuesday, October 2, 2012 3:12 AM Question rather than discussion
    Thursday, March 17, 2011 7:50 AM

Answers

  • Hi,

    The NOLOCK and READUNCOMMITTED lock hints are not allowed for target tables of INSERT, UPDATE, DELETE or MERGE statements for table on which you are doing DML operation.

    But of you are using UPDATE FROM sysntex, means you are updaing table1 by values from table2, you can have NOLOCK hint against table2. Kindly have a look at example given in below post for this.

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/90044062-6595-468c-a860-a2f6c52e2fd7


    - Chintak (My Blog)

    • Marked as answer by Naomi N Tuesday, October 2, 2012 3:15 AM
    Thursday, March 17, 2011 8:20 AM
  • In response to your question about the update query itself...

    If I had to make an educated guess, it is probably part of a stored procedure that is being used as an abstraction for updating the table itself, as though this were an api. All it's doing is creating a dynamic update statement to update a table and specifically one column which are both variables. I wouldn't advise doing anything like this, especially as it could lead to sql injection... granted the entire script or procedure wasn't posted, but it doesn't look like the input is sanitized (though is might be through the client?) and it could still lead to problems.

    -Sean


    Sean Gallardy, MCC | Blog | Twitter

    • Marked as answer by Naomi N Tuesday, October 2, 2012 3:14 AM
    Friday, September 28, 2012 8:44 PM

All replies

  • CREATE TABLE #t (c INT)
    INSERT INTO #t VALUES (1)
    INSERT INTO #t VALUES (2)


    UPDATE #t SET c=2 FROM #t WITH (NOLOCK)
    WHERE c=1


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, March 17, 2011 8:03 AM
    Answerer
  • I think you wont be able to do the with (nolock)  against Update, Insert and Delete statements.
    Regards, Ashwin Menon My Blog -> sqllearnings.wordpress.com
    Thursday, March 17, 2011 8:08 AM
  • Hi,

    The NOLOCK and READUNCOMMITTED lock hints are not allowed for target tables of INSERT, UPDATE, DELETE or MERGE statements for table on which you are doing DML operation.

    But of you are using UPDATE FROM sysntex, means you are updaing table1 by values from table2, you can have NOLOCK hint against table2. Kindly have a look at example given in below post for this.

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/90044062-6595-468c-a860-a2f6c52e2fd7


    - Chintak (My Blog)

    • Marked as answer by Naomi N Tuesday, October 2, 2012 3:15 AM
    Thursday, March 17, 2011 8:20 AM
  • where can I use 'with(nolock)' in the following statement?

    set @string = 'update ' + QUOTENAME(@Schema1) + '.' + QUOTENAME(@TableName1)+ 'SET ' + QUOTENAME(@ColumnName1) + ' = dbo.func_RegExReplace(''' + @RecordValue + ''',''' + @ValidNumber + ''',replace(space(len(''' + @ValidNumber +''')),' + ''' ''' + ',' + '''X''' + ')) where ' + @Pkey 

    Sorry my post is not related to ur question... But i am curious to know what ur update statement is doing?? Quite complex & interesting :) Can u please explain?
    Friday, September 28, 2012 4:52 PM
  • In response to your question about the update query itself...

    If I had to make an educated guess, it is probably part of a stored procedure that is being used as an abstraction for updating the table itself, as though this were an api. All it's doing is creating a dynamic update statement to update a table and specifically one column which are both variables. I wouldn't advise doing anything like this, especially as it could lead to sql injection... granted the entire script or procedure wasn't posted, but it doesn't look like the input is sanitized (though is might be through the client?) and it could still lead to problems.

    -Sean


    Sean Gallardy, MCC | Blog | Twitter

    • Marked as answer by Naomi N Tuesday, October 2, 2012 3:14 AM
    Friday, September 28, 2012 8:44 PM