SELECT lock when I make an UPDATE - SQLServer

Question

SELECT lock when I make an UPDATE - SQLServer

Navigation

#1 by (0 votes)

0

I'll give an example of what I'm doing, but the very question is at the end.

I have a table with 4,100,000 records and I am creating a theft that reads this data and exports it to MYSQL (it does not enter into the agenda, just to set it up).

I have two columns BIT , one is called processing and the other is migrated, in an infinite loop I execute 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 want to run several thefts at the same time to speed up the process, the point is, when I do SELECT right after the population of my List in C #, I give an update on all items as processing = 1 and this takes a while, imagine that while I give UPDATE another theft of a SELECT and returns the same data that I'm giving UPDATE , that is, duplicate information.

The question is : How do I lock a table while giving UPDATE to it?

sql sql-server c#

asked by anonymous 08.11.2018 / 13:41

1 answer

How to extract the SQL Server log file from the server? pass variable to jquery function

score 0 · Answer 1

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

Code Snippet
SELECT * FROM TABELA (HOLDLOCK)

If you want to lock 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 can slow down your competition and slow your database (more processes will have to wait until the table lock finishes).