Início / dba / Perguntas / 114572
Accepted
marius-O
Asked: 2015-09-10 05:25:21 +0800 CST

Por que UPDLOCK faz com que SELECTs travem (travem)?

  • 772

Tenho um select no SQL SERVER que trava toda a tabela.

Aqui está o script de configuração (certifique-se de não sobrescrever nada)

USE [master]
GO

IF EXISTS(SELECT 1 FROM sys.databases d WHERE d.name = 'LockingTestDB')
DROP DATABASE LockingTestDB
GO

CREATE DATABASE LockingTestDB
GO

USE [LockingTestDB]
GO
IF EXISTS(SELECT 1 FROM sys.tables t WHERE t.name = 'LockingTestTable')
  DROP TABLE LockingTestTable
GO

CREATE TABLE LockingTestTable (
  Id int IDENTITY(1, 1),
  Name varchar(100),
  PRIMARY KEY CLUSTERED (Id)
)
GO

INSERT INTO LockingTestTable(Name) VALUES ('1')
INSERT INTO LockingTestTable(Name) VALUES ('2')
GO

Abra uma nova janela de consulta e execute a seguinte transação (que contém uma espera):

USE [LockingTestDB]
GO

BEGIN TRANSACTION
  SELECT * FROM LockingTestTable t WITH (UPDLOCK, ROWLOCK) WHERE t.Name = '1'
  WAITFOR DELAY '00:01:00'

COMMIT TRANSACTION
--ROLLBACK
GO

USE [master]
GO

E outro que será executado (certifique-se de que sejam executados ao mesmo tempo):

USE [LockingTestDB]
GO

SELECT * FROM LockingTestTable t WITH (UPDLOCK, ROWLOCK) WHERE t.Name = '2'

USE [master]
GO

Você notará que a segunda consulta será bloqueada pela primeira. Pare a primeira consulta e execute o ROLLBACK e a segunda será concluída.

Por que isso está acontecendo?

PS: Adicionar um índice não clusterizado (com cobertura total) sobre o nome corrigirá isso:

USE [LockingTestDB]
GO

CREATE NONCLUSTERED INDEX [IX_Name] ON [dbo].[LockingTestTable] 
(
  [Name] ASC
)
INCLUDE ( [Id]) WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Novamente por quê?

sql-server locking

1 respostas

  1. Best Answer

    Conforme documentado nos Manuais Online , UPDLOCKpega os bloqueios de atualização e os mantém até o final da transação.

    Sem um índice para localizar a(s) linha(s) a ser(em) bloqueada(s), todas as linhas testadas são bloqueadas e os bloqueios nas linhas qualificadas são retidos até que a transação seja concluída.

    A primeira transação contém um bloqueio de atualização na linha em que nome = 1. A segunda transação é bloqueada quando tenta adquirir um bloqueio de atualização na mesma linha (para testar se nome = 2 para essa linha).

    Observação: o SQL Server sempre aceita um bloqueio de atualização. Se a linha não corresponder ao(s) predicado(s) na consulta, o bloqueio pode ser liberado antes de testar a próxima linha.

    Com um índice, o SQL Server pode localizar e bloquear rapidamente apenas as linhas que se qualificam, portanto, não há conflito.

    Você deve revisar o código com um profissional de banco de dados qualificado para validar o motivo da dica de bloqueio e garantir que os índices apropriados estejam presentes.

    Informações relacionadas: Modificações de Dados em Isolamento de Captura Instantânea Confirmada de Leitura

    • 22

relate perguntas