Can Select block a table from Insert?

A SELECT will take a shared lock on the rows, but it shouldn't effect inserts correct?

No, it's not exact. When you make a SELECT, it can acquire shared locks on pages and even on the whole table, you can test it by yourself by using paglock or tablock hints (of course you should use repeatable read or serializable to see them, as all the shared locks in read committed are released as soon as they are no needed anymore).

The same situation can be modelled this way:

if object_id('dbo.t') is not null drop table dbo.t; select top 10000 cast(row_number() over(order by getdate()) as varchar(10)) as col into dbo.t from sys.columns c1 cross join sys.columns c2; set transaction isolation level serializable; begin tran select * from dbo.t where col like '%0%'; select resource_type, request_mode, count(*) as cnt from sys.dm_tran_locks where request_session_id = @@spid group by resource_type, request_mode; 

Here you see lock escalation result, my query wanted more than 5000 locks per statement so instead of them server took only one lock, shared lock on the table.

Now if you try to insert any row in this table from another session, your INSERT will be blocked.

This is because any insert first need to acquire IX on a table and IX on a page, but IX on a table is incompatible with S on the same table, so it will be blocked.

This way your select could block your insert.

To see what exactly happens on your server you should use sys.dm_tran_locks filtered by both your session_id.

General info - this is called SQL Server Concurrency and in SQL Server you will find two models:

• Pessimistic;
• Optimistic.

Answering your question - yes, you can block any insert during read and this is called "Pessimistic Concurrency". However, this model comes with specific properties and you have to be careful because:

• Data being read is locked, so that no other user can modify the data;
• Data being modified is locked, so that no other user can read or modify the data;
• The number of locks acquired is high because every data access operation (read/write) acquires a lock;
• Writers block readers and other writers. Readers block writers.

The point is that you should use Pessimistic Concurrency only if the locks are held for a short period of time and only if the cost of each lock is lower than rolling back the transaction in case of a conflict, as Neeraj said.

I would recommend to read more about isolation levels applying both Pessimistic and Optimistic models here.

EDIT - I found a very detailed explanation about isolation levels on Stack, here.