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?

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. Jun 29 '20 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? Jun 29 '20 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. Jun 29 '20 at 17:01
  • Perfect @AaronBertrand! Now its clear for me. Thanks! Jun 29 '20 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? Jun 29 '20 at 16:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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