How to avoid UPDATE statement locking out the entire table when updating large number of records
I am fairly new to locks and hints.
I have a table with very frequent
INSERT operations. The table has 11 million records.
I have added a new column to it and I need to copy over the data from an existing column in the same table to the new column.
I am planning to use
ROWLOCK hint to avoid escalating locks to table level locks and blocking out all other operations on the table. For example:
UPDATE SomeTable WITH (ROWLOCK) SET NewColumn = OldColumn
- Would a
ROWLOCK? Note, once the records are inserted in the table, the value for OldColumn does not change, so
NOLOCKwould not cause dirty reads.
NOLOCKeven make sense in this case, because the SQL Server would have to anyways get update locks for
- Is there a better way of achieving this?
I know hints are to be avoided and SQL Server usually makes smarter choices, but I don't want to get the table locked out during this update.
Try to update in batches.
DECLARE @Batch INT = 1000 DECLARE @Rowcount INT = @Batch WHILE @Rowcount > 0 BEGIN ;WITH CTE AS ( SELECT TOP (@Batch) NewColumn,OldColumn FROM SomeTable WHERE NewColumn <> OldColumn OR (NewColumn IS NULL AND OldColumn IS NOT NULL) ) UPDATE cte SET NewColumn = OldColumn; SET @Rowcount = @@ROWCOUNT END
Update Rows in Large Tables Without Locking Out Users, Number 8860726. When the table is small, you might be able to update the entire table with a or all rows in a large table and you want to avoid locking out users, rows have already been updated so that you don't update them a OUTPUT clause with the UPDATE, INSERT, and DELETE statements. The statement as you have it will update all records in the table within a single transaction so no matter how you cut it, you will be locking all records within the transaction. – MikeS Jan 24 '17 at 18:21 I once saw Kendra Little do a nice trick with update.
I took @pacreely's approach (see his answer on this question) of updating in batches and created a
update...top variation. I added the (rowlock) hint tell SQL server to keep the locks at row level.
See update...top for details. Also note, that you cannot use
order by when using
delete statement so the referenced rows are not arranged in any order.
declare @BatchSize int = 1000 declare @RowCount int = @BatchSize while @RowCount > 0 begin update top (@BatchSize) SomeTable with (rowlock) set NewColumn = OldColumn where NewColumn <> OldColumn or ( NewColumn is null and OldColumn is not null ) select @RowCount = @@rowcount end
LOCK TABLE statement, The LOCK TABLE statement allows you to explicitly acquire a shared or exclusive need to lock a particular row in exclusive mode in order to update the row. To lock the entire Flights table in share mode to avoid a large number of row locks, If a transaction needs to look at a table before updating the table, acquire an� Update lock (U) is used to avoid deadlocks. Unlike the Exclusive lock, the Update lock places a Shared lock on a resource that already has another shared lock on it. However, it is not possible to place a shared lock on a resource that has an update lock. When the transaction is ready to make its changes, the update lock converts to an
This question has an answer on the Database Administrators site on StackExchange here: https://dba.stackexchange.com/questions/127158/how-to-get-sql-insert-and-or-update-to-not-lock-entire-table-on-ms-sql-server
Ask TOM "How to Update millions or records in a table", Answered by: Tom Kyte - Last updated: May 10, 2019 - 8:06 am UTC I want to update and commit every time for so many records ( say 10,000 you can do that using parallel query, with nologging on most operations Is this answer out of date? If it is 3 million records on an indexed table will take considerable time. RE:[sql-server-l] How to avoid Lock while updating a table 2nd process is for..base on few data conditions from table A.. it updates the 3rd table.. and because of this we have 2nd process
We recently had a case where we wanted to do something similar, but slowly over days (updating only a certain number of records per run, and only during certain hours). The most recent data was fine, but millions of rows of older data needed to be updated. Our data table looks something like this:
Create Table FileContent ( FileContent varchar(max), File_PK bigint, NewFileContent varchar(max) )
And we only needed to update certain rows, but millions of them. We created a table to store our progress so we could use a scheduled job to iterate through and update the main table, then populated this table with the primary keys of the main table records that needed updating:
Create Table FilesToUpdate ( File_PK bigint, IsUpdated bit NOT NULL DEFAULT 0 )
Then we scheduled the following script to do the updating (for your own use, play with the batch sizing and scheduling for what works with your system).
/*** Script to update and fix records. ***/ DECLARE @Rowcount INT = 1 -- , @BatchSize INT = 100 -- how many rows will be updated on each iteration of the loop , @BatchesToRun INT = 25 -- the max number of times the loop will iterate , @StartingRecord BIGINT = 1; -- Get the highest File_PK not already fixed as a starting point. Select @StartingRecord = MAX(File_PK) From FilesToUpdate where IsUpdated = 0 -- While there are still rows to update and we haven't hit our limit on iterations... WHILE (@Rowcount > 0 and @BatchesToRun > 0) BEGIN print Concat('StartingRecord (Start of Loop): ', @StartingRecord) UPDATE FileContent SET NewFileContent = 'New value here' WHERE File_PK BETWEEN (@StartingRecord - @BatchSize + 1) AND @StartingRecord; -- @@Rowcount is the number of records affected by the last statement. If this returns 0, the loop will stop because we've run out of things to update. SET @Rowcount = @@ROWCOUNT; print Concat('RowCount: ', @Rowcount) -- Record which PKs were updated so we know where to start next time around. UPDATE FilesToUpdate Set IsUpdated = 1 where File_PK BETWEEN (@StartingRecord - @BatchSize + 1) AND @StartingRecord; -- The loop will stop after @BatchSize*@BatchesToRun records are updated. -- If there aren't that many records left to update, the @Rowcount checks will stop it. SELECT @BatchesToRun = @BatchesToRun - 1 print Concat('Batches Remaining: ',@BatchesToRun) -- Set the starting record for the next time through the loop. SELECT @StartingRecord -= @BatchSize print Concat('StartingRecord (End of Loop): ', @StartingRecord) END
The Curious Case of the Table-Locking UPDATE Query, Update: On closer inspection, the lock type was not on the table, but on a tuple. began to grow, and the number of jobs being processed fell to nearly zero. occasional performance issues, nothing stood out as a huge problem. out a row lock, was my query taking out a lock against the whole table? The above statement will be updating the table with non-critical information meaning that if it skips some records the first time, it can update them the second time and so on. If even one of the other UPDATE statements has locked a row that needs to be modified by your UPDATE statement, this will cause the latter to wait (blocking).
SQL Server UPDATE lock and UPDLOCK Table Hints, In SQL Server databases with actively running transactions, it is a an UPDATE lock by using the UPDLOCK table hint in order to avoid In SQL Server Management Studio (SSMS), we open two query As a row in ##TableB will be updated in the first transaction, the Thanks for this great explanation� For example, if you want to make sure the locks are never escalated above individual rows, you can use this: UPDATE mytable WITH (ROWLOCK) SET col1 = 'something' WHERE col2 = 'somethingelse'. This suggests SQL to lock only the rows affected by the query, and never escalate locks to contain pages or the entire table.
How to resolve blocking problems that are caused by lock escalation , If lock escalation is occurring, verify that the escalated table lock is blocking other users. The simplest and safest way to prevent lock escalation is to keep This means that what may look similar to a SELECT statement at a a batch job that modifies a large number of rows in the mytable table and that� Very much a newbie on DB work, so appreciate your patience with a basic question. I'm running SQL Server 2014 on my local machine, and I have a small table and a basic client application to test different approaches with. I'm getting what appears to be a table lock during both INSERT INTO and UPDATE statements. The client is an ASP.NET
Transaction Locking and Row Versioning Guide, A ROLLBACK statement backs out all modifications made in the Locking and row versioning prevent users from reading uncommitted Lost updates occur when two or more transactions select the same Bulk Update (BU), Used when bulk copying data into a table and the TABLOCK hint is specified. Session 1: Lock Acquired: IX lock on the table — trying to update Description field to Value2 Session 1: Lock Acquiring: U lock on the key of the none clustered primary key — this is blocked Session 1 status: Trying to get U lock on the index key and get X lock on the Row(although it already owns the X lock on the row)
- The statement as you have it will update all records in the table within a single transaction so no matter how you cut it, you will be locking all records within the transaction.
- I once saw Kendra Little do a nice trick with update. The update would not go parallel, but to speed things up she used select in cte to achieve parallel read.
- Thanks for showing that updates to CTE cascade to the underlying table.
- one more variation of batch updates using
update...topcombination in my answer.
- Keep batch size less than 5000 stackoverflow.com/questions/17905777/…