CODEWITHSUNDEEP
sql-servert-sql
ralf.w.
ralf.w.

Reputation: 1696

How to update tables with nvarchar(max) columns with minimal locks

I have trouble to update some rows in SQL Server (2005 and 2008).

Often, when I try to update one row while having a running query (select * from thistable), I start the update command and it will fail due to a timeout/lock issue.

It only appears on tables with nvarchar(max)/text columns!

Even if I try to SELECT * FROM thistable WITH(ROWLOCK), I do encounter the same problem.

So my basic question here is:

Can I motivate SQL Server NOT to lock more than the actual row ?

Edit: I first run the SELECT afterwards I try to UPDATE...

There is a great explanation on Locking in SQL-Server on simple talk

Upvotes: 0

Views: 780

Answers (1)

shawty
shawty

Reputation: 5829

Try using:

SELECT * FROM thistable (NOLOCK)

for your select statement.

Then run your update as normal.

Upvotes: 1

Related Questions