Sql-server – Lock only a row in insert command sql server

lockingsql server

I have this initial code below to create and insert some values on my table

IF (OBJECT_ID('dbo.Teste_Nolock') IS NOT NULL) DROP TABLE dbo.Teste_Nolock
CREATE TABLE dbo.Teste_Nolock (
    Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    Nome VARCHAR(100)
)

INSERT INTO dbo.Teste_Nolock
VALUES ('User'), ('Name'), ('DataBase'), ('SQL Server'), ('Power BI'), ('SSRS'), ('SSIS'), ('SSAS'), ('Blog')

BEGIN TRANSACTION

UPDATE dbo.Teste_Nolock
SET Nome = 'Nolock Test'
WHERE Nome = 'User'

When I execute update without a commit or rollback, SQL Server is locking my table and i can't execute the command SELECT * FROM dbo.Teste_Nolock WHERE Nome = 'SQL Server' for example. How can i solve this kind of problem?

Best Answer

Make sure you have an index on the Nome column, allowing SQL Server to "bypass" (not having to read) those rows you exclude in your WHERE clause. I.e.

CREATE INDEX yourindexname ON Teste_Nolock(Nome)
Related Question