Nov 20 2020 09:13 AM - edited Nov 20 2020 09:23 AM
I have a table and I have to count its rows every 10 m
like (SELECT COUNT(ID) FROM dbo.MyTable)
the problem that when I count the rows my select put a shared lock on the table and block any insert to the table
my two questions
1- is that tue that my count select put a shared lock on the table and block any insert to the table ?
2- is there any solution fro this problem but using (nolock and readpast hints) in my query ?
thank you for your help
Nov 23 2020 10:49 PM
Yes, the SELECT create a shared lock on the table, but that don't block INSERTS, only DDL statements.
You can get the row count also from DMV's, that's much faster:
=>
Nov 24 2020 05:19 AM
Hi @HassanShaddad --
I would also consider the use of read committed snapshot isolation to improve concurrency. Take care.