Lockar SELECT when I do an UPDATE - Sqlserver

Question

Let me give you an example of what I’m doing, but the question really is at the end.

I have a table with 4.100.000 records and I am creating a robot that reads this data and exports it to MYSQL (not on the agenda, just to set).

I have two columns BIT, one is called Processing and the other migrated, in an infinite loop running this command

SELECT TOP 100 Title,Tamanho,Caption,dataHoraIndexacao,Date,Resolucao,Base,Altura,orientacao,codigo,NomeArquivo FROM migracao.Reuters WHERE processing = 0 and migrated = 0

I intend to run several robots at the same time to speed up the process, the question is, when I do the SELECT right after the population of my List in c#, I give an update on all items like processing = 1 and it takes time, imagine that while I give the UPDATE another robot of the one SELECT and returns the same data that I am giving UPDATE, or is duplicated information.

The question is: How to lock a table while giving one UPDATE in the same?

Answer 1

If you want to block the table for writing until the end of a transaction use:

Code Snippet
SELECT * FROM TABELA (HOLDLOCK) 

If you want to block the table for reading and writing until the end of a transaction use:

Code Snippet
SELECT * FROM TABELA (XLOCK) 

Remember that imposing locks on the entire table may decrease the competition and slow down your database (more processes will have to wait until the blocking of the table ends).