0

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?

2 Answers 2

3

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)
4
  • Which could still have to lock the whole table, or enough to tip/escalate, because name isn’t unique. So depending on stats, actual data, the plan being used, etc., a non-unique index alone might still prevent readers of other rows. Commented Jun 29, 2020 at 15:07
  • It's work, but if i have a table with many colums and i can filter by any one? I'd need to create index for each column? Commented Jun 29, 2020 at 16:27
  • @Murilo if you don’t have a leading key index on a column you’re trying to filter to, how is SQL Server supposed to know which rows have that value? It has to be pessimistic and assume that they might all have that value, so all need to be locked. Instead of trying to solve for some magical every column can be used to filter therefore every column needs an index to avoid locking, make that locking unimportant by using RCSI as Dominique suggests. Or if you really only usually filter by something that is unique or at least selective, index those cases and let broader locking happen in others. Commented Jun 29, 2020 at 17:01
  • Perfect @AaronBertrand! Now its clear for me. Thanks! Commented Jun 29, 2020 at 17:04
1

The best way, if you need to be able to read data while there is some operation that modifies the data is to use Optimistic isolation level.

Turn on "read commited snapshot" and you will be able to run your select against that table even it there is update in progress.

You won't have to care about the locking (row, page, object).

1
  • Works great, but if i use an insert without commit, in row 1 for example, and try to update row 2, isn't work. Do you understand? Commented Jun 29, 2020 at 16:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.