How to update field in SQL table without blocking it?

Asked

Viewed 750 times

3

I have a table with many records in production (almost 3 million), and I need to update a field with a very simple query:

UPDATE tabela SET enviado = 1 WHERE enviado is null

About 2.5 million will be updated.

The problem is that it will take a long time, and meanwhile the table will be locked, unable to return records.

There’s a way around it?

Any query similar to the SELECT WITH(NOLOCK)?

Note: I cannot change the application to do the SELECT WITH(NOLOCK).

sql
  • 1

    Note: NOLOCK is a hint, you are merely suggesting for the engine it does not use Locks but it can choose to scale the lock and often it does so when dealing with lots of data at once. As for your update, try doing it during a maintenance window, you have any idea how many records will be updated?

  • @Jean, about 2.5 million

  • All records will be updated?! Well, I advise to really use a maintenance window, leave the bank in single mode, take advantage p/ make a backup, maybe a reindex and send see.

  • I created an index for the column to change, I went through a maintenance window and it was relatively fast: 1min30

  • @Andréfigueiredo, but the column is only used as filtering? Or is there reading in any query of all columns of this table?

1 answer

2

Long live,

There are some solutions to this problem:

1 - Hammering (Not tested)

You can temporarily change the table name, create a view with the table name you previously had with WITH(NOLOCK) and update the table. After UPDATE runs, you delete the VIEW and re-name the table to the original name.

2 - Instalments UPDATES

Instead of doing UPDATE to the entire table at once, you can/should do smaller updates.

UPDATE TOP(1000) tabela SET enviado = 1 WHERE enviado is null

WHILE @@rowcount > 0
BEGIN
   UPDATE TOP(1000) tabela SET enviado = 1 WHERE enviado is null;
END

What this will do is run UPDATE until no record has been changed.

3 - Create a new table and copy the data

You can also create a new table (table v2) with the new structure you want to add (A column with default value for example) and copy the old contents of table to the table v2 down to the last known id. The trick here is to add triggers in the table table to synchronize the information while the data is being copied. In the end, the process is similar to the first point, the table for table and name of table v2 for table. and will have your data updated and the new structure without any downtime.

4 - SQL Parallel Boost (http://sqlparallelboost.codeplex.com) You can always take a look at this site.

I hope you’ve been helpful.

  • 1

    The installment update is a good one. As for hammering, numerous problems can occur: triggers in the table, the operation of Name itself will give a tablock, other transactions of Insert/update may be occurring, etc

  • Yes, hammering is dangerous and should not be used. Perhaps in the order of the answer, it should be the last.

  • Depending on the BD disable the Audit and run in exclusive mode, then reactivate the Audit, but I’ve done something similar (even on volume) in Oracle and only had to do it in an hour of idle BD, no major worries only having space in the rollback area, if there are triggers in the table give a temporary disable as well.

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.