Partitioned view doesn't allow to query itself while insertion

I created 2 similar tables for different accounts within one database and
partitioned view on them.
create table t
(
[ID] int identity(1,1),
[Name] varchar(15),
[Account] varchar(20) default 'Account1' check(Team = 'Account1'),
PRIMARY KEY CLUSTERED
(
[ID], [Account]
)
)
create view v
as
select * from Account1.t
union all
select * from Account2.t
I run query like this:
"select top 100 * from v where Account = 'Account1'"
and massive insertion from staging table like this at the same time:
"insert into t ( name ) select name from tt under different account"
my query stops until insertion is completed.
FYI: each tables reside in it's own filegroup. Each filgroup has its files
on physically different HDD.
There are 2 multithreaded processors there and parallelism works.
Execution plpan shows DIFFERENT CLUSTER KEYS ARE USED for any ss and so o
n.
Isolation level is Read Commited (when I set read uncommited it works
without locks, of course).
I don't know why my query wait for end of insertion. Query and insertion
don't use common resources. They MUST works independently...Can you cut and paste the exact SQL you're running, or provide
a repro without typos? What you posted here includes a CHECK
constraint that refers to a non-existent column named [Team].
You might try reversing the order of the primary key columns.
Steve Kass
Drew University
OSA wrote:

>I created 2 similar tables for different accounts within one database and
>partitioned view on them.
>create table t
>(
> [ID] int identity(1,1),
> [Name] varchar(15),
> [Account] varchar(20) default 'Account1' check(Team = 'Account1'),
> PRIMARY KEY CLUSTERED
> (
> [ID], [Account]
> )
> )
>create view v
>as
> select * from Account1.t
> union all
> select * from Account2.t
>I run query like this:
>"select top 100 * from v where Account = 'Account1'"
> and massive insertion from staging table like this at the same time:
>"insert into t ( name ) select name from tt under different account"
>my query stops until insertion is completed.
>FYI: each tables reside in it's own filegroup. Each filgroup has its files
>on physically different HDD.
>There are 2 multithreaded processors there and parallelism works.
>Execution plpan shows DIFFERENT CLUSTER KEYS ARE USED for any ss and so
on.
>Isolation level is Read Commited (when I set read uncommited it works
>without locks, of course).
>I don't know why my query wait for end of insertion. Query and insertion
>don't use common resources. They MUST works independently...
>