Is there any way to make a count select statement without SharedLock on the table

Copper Contributor

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  :hearteyes:

2 Replies

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:

=>

Detailed list of all tables and their size

Hi @HassanShaddad --

 

I would also consider the use of read committed snapshot isolation to improve concurrency.  Take care.

 

Read Committed Snapshot Isolation