Blocking

Transact-SQL
1

Why does a select that inserts into a temporary table cause blocking?

create table #mytab (....);
Insert into #mytab 
select a, b, c from tab1 
inner join tab2 on tab1.id = tab2.id1 
left join tab3 on tab1.id = tab3.id1;

Note currently the insert does not list columns. This is to be rectified but under time constraint at the moment.

2

Which activity is being blocked: other activity in tempdb or activity on tab1|tab2|tab3?

The tab1|2|3 tables will require shared locks to read, so any task that needed an exclusive lock, or certain other types of locks, on those tables could be blocked.

Tempdb activity should be blocked only if you're running into resource constraints on tempdb.

3

Thanks Scott. Yes it is the tab1 that has a problem, I forgot to think about the need for exclusive locks needed by others.