Update many rows without locking

StuFox

I am trying to run a simple update statement that updates around 1 million records on SQL Server 2005. For example:

update customers

set CustCode='AAB'

where CustType=72

I would like to update the table WITHOUT locking. In this case, there is no need to have "all or nothing" transactions. If it does a partial update and then fails, it's ok to only have half the records updated.

The server is using up a lot of resources creating and releasing the locks. Plus users are getting locked out of the records during the update. I know this is by design, but in this case it's OK. I know I can use the "set transaction isolation level READ UNCOMMITTED" statement to fix the select statements from getting blocked, but there are way too many places that would have to be changed. Plus there are other updates to this table that need to be locked.

So here is my question: Is there a way to do a transaction-less update

Thanks,

Stuart Fox

oj

Short answer is "no". Without transaction the update is not guaranteed.

David Frommer

Try breaking the transaction up into smaller chunks. This should make each transaction hold locks for a shorter time an will use less resources for locks at any one time.

StuFox

David,

Thanks, but I was hoping for another solution. Your suggestion was going to be my last resort type of thing.

Thanks,

Stu

Tom Phillips

The way I handle this type of problem is to break it down to only do X at a time.

DECLARE @found int
SET @found = 1

WHILE @found > 0
BEGIN

UPDATE customers

SET CustCode = 'AAB'

WHERE CustomersPK IN

(SELECT TOP 10000 CustomersPK FROM customers WHERE CustType=72 and CustCode <> 'AAB')

SET @found = @@ROWCOUNT

END

This will do 10,000 and a time and release the locks so other work can get done.

Update many rows without locking

Re: Update many rows without locking

Re: Update many rows without locking

Re: Update many rows without locking

Re: Update many rows without locking