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%3CLINGO-SUB%20id%3D%22lingo-sub-1925965%22%20slang%3D%22en-US%22%3ERe%3A%20Is%20there%20any%20way%20to%20make%20a%20count%20select%20statement%20without%20SharedLock%20on%20the%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1925965%22%20slang%3D%22en-US%22%3E%3CP%3EYes%2C%20the%20SELECT%20create%20a%20shared%20lock%20on%20the%20table%2C%20but%20that%20don't%20block%20INSERTS%2C%20only%20DDL%20statements.%3C%2FP%3E%3CP%3EYou%20can%20get%20the%20row%20count%20also%20from%20DMV's%2C%20that's%20much%20faster%3A%3C%2FP%3E%3CP%3E%3D%26gt%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fgallery.technet.microsoft.com%2Ffb515c14-be6c-48f8-b8f4-8fd60de82f05%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3E%3CFONT%3EDetailed%20list%20of%20all%20tables%20and%20their%20size%3C%2FFONT%3E%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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