Concurrent select is locked while another transaction executes Ins

Our application need to be able to concurrently insert and select from the
same table irrespective of the data.
But, the following issue has been found in the sql server 2000.
Initially , the insert statements (around 1000 rows) for a table is executed
in a transaction inside a stored procedure.
like:
"Insert into Test (pkey,field1,field2,strfield) values
(1400,1144,12025,'test sp insert 2')"
and simultaneously a select statement to retrieve all records from the same
table is executed in the query analyzer. Then the select statement waits
until the insert operation completes.
"select * from Test"
But, if a select statement that filters the data based on the index fields
is used, then it executes without delay.
select * from test where pkey=2000 and field1=1111
(Index: pkey and field1 combination) Suppose if the filter condition
includes the data that is being inserted, then also it is blocked.
In SQL Server 2000, whether it is possible to avoid the blocking of select
statement while insert.
Note that the insert statement is executed in the default isolation level of
sql server 2000.
In MS Access, concurrent inserts and select works without any issue.
Table and Index references:
CREATE TABLE [dbo].[Test] (
[pkey] [bigint] NOT NULL ,
[field1] [bigint] NOT NULL ,
[field2] [int] NULL ,
[strfield] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE INDEX [IX_Test] ON [dbo].[Test]([field1], [pkey]) ON [PRIMARY]Baskar
http://www.sql-server-performance.com/blocking.asp
http://www.sql-server-performance.c...ucing_locks.asp
"Baskar" <Baskar@.discussions.microsoft.com> wrote in message
news:880BF5FF-E31F-4BA2-95B1-CDDE6C8616C8@.microsoft.com...
> Our application need to be able to concurrently insert and select from the
> same table irrespective of the data.
> But, the following issue has been found in the sql server 2000.
> Initially , the insert statements (around 1000 rows) for a table is
> executed
> in a transaction inside a stored procedure.
> like:
> "Insert into Test (pkey,field1,field2,strfield) values
> (1400,1144,12025,'test sp insert 2')"
> and simultaneously a select statement to retrieve all records from the
> same
> table is executed in the query analyzer. Then the select statement waits
> until the insert operation completes.
> "select * from Test"
> But, if a select statement that filters the data based on the index fields
> is used, then it executes without delay.
> select * from test where pkey=2000 and field1=1111
> (Index: pkey and field1 combination) Suppose if the filter condition
> includes the data that is being inserted, then also it is blocked.
> In SQL Server 2000, whether it is possible to avoid the blocking of select
> statement while insert.
> Note that the insert statement is executed in the default isolation level
> of
> sql server 2000.
> In MS Access, concurrent inserts and select works without any issue.
> Table and Index references:
> CREATE TABLE [dbo].[Test] (
> [pkey] [bigint] NOT NULL ,
> [field1] [bigint] NOT NULL ,
> [field2] [int] NULL ,
> [strfield] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE INDEX [IX_Test] ON [dbo].[Test]([field1], [pkey]) ON [PRIMARY]
>
>
>