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

%3CLINGO-SUB%20id%3D%22lingo-sub-1918626%22%20slang%3D%22en-US%22%3Eis%20there%20a%20way%20to%20make%20a%20count%20select%20statement%20without%20SharedLock%20on%20the%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1918626%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20table%20and%20I%20have%20to%20count%20its%20rows%20every%2010%20m%26nbsp%3B%3CBR%20%2F%3Elike%20(%3CSPAN%20class%3D%22crayon-e%22%3ESELECT%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22crayon-e%22%3ECOUNT%3C%2FSPAN%3E%3CSPAN%20class%3D%22crayon-sy%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22crayon-v%22%3EID%3C%2FSPAN%3E%3CSPAN%20class%3D%22crayon-sy%22%3E)%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22crayon-e%22%3EFROM%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22crayon-v%22%3Edbo%3C%2FSPAN%3E%3CSPAN%20class%3D%22crayon-sy%22%3E.MyTable%3C%2FSPAN%3E)%3CBR%20%2F%3Ethe%20problem%20that%20when%20I%20count%20the%20rows%20my%20select%20put%20a%20shared%20lock%20on%20the%20table%20and%20block%20any%20insert%20to%20the%20table%26nbsp%3B%3CBR%20%2F%3Emy%20two%20questions%3CBR%20%2F%3E1-%20is%20that%20tue%20that%20my%20count%20select%26nbsp%3B%26nbsp%3Bput%20a%20shared%20lock%20on%20the%20table%20and%20block%20any%20insert%20to%20the%20table%20%3F%3CBR%20%2F%3E2-%20is%20there%20any%20solution%20fro%20this%20problem%20but%20using%20(nolock%20and%20readpast%20hints)%26nbsp%3B%20in%20my%20query%20%3F%3CBR%20%2F%3Ethank%20you%20for%20your%20help%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New 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
Highlighted

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

Highlighted

Hi @HassanShaddad --

 

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

 

Read Committed Snapshot Isolation