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]
>
>
>

Concurrent Access - New Record, Primary Key problem....

Can someone explain what happens when two users concurrently attempt to
create a new record in a table with an autonumber primary key? For example,
user 1 creates a new record and manipulates it within a transaction making
use (perhaps) of the @.@.IDENTITY value when creating other, related records.
Before this transaction is complete, user 2 creates a new record and does
the same thing. Presumably they will both have the same @.@.IDENTITY? If
this is the case, how is it possible to manage such a situation?

Thanks."Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:csgov0$4e6$1$830fa17d@.news.demon.co.uk...
> Can someone explain what happens when two users concurrently attempt to
> create a new record in a table with an autonumber primary key? For
> example, user 1 creates a new record and manipulates it within a
> transaction making use (perhaps) of the @.@.IDENTITY value when creating
> other, related records. Before this transaction is complete, user 2
> creates a new record and does the same thing. Presumably they will both
> have the same @.@.IDENTITY? If this is the case, how is it possible to
> manage such a situation?
> Thanks.

No, each session will have a different value, so there's no problem with
concurrency - check out SCOPE_IDENTITY(), IDENT_CURRENT() and @.@.IDENTITY in
Books Online.

Note that just defining a column as an identity column is not enough to
guarantee uniqueness - you can still create duplicates manually (see SET
IDENTITY_INSERT in BOL), so if you want to use the column as a PK, make sure
it is declared as a PK when you create the table.

Simon|||Ok that simplifies things somewhat. Yes, the columns in question are both
Identity and Primary Key.

Thanks very much for your reply.

Robin

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:41ebea66$1_3@.news.bluewin.ch...
> "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
> message news:csgov0$4e6$1$830fa17d@.news.demon.co.uk...
>>
>> Can someone explain what happens when two users concurrently attempt to
>> create a new record in a table with an autonumber primary key? For
>> example, user 1 creates a new record and manipulates it within a
>> transaction making use (perhaps) of the @.@.IDENTITY value when creating
>> other, related records. Before this transaction is complete, user 2
>> creates a new record and does the same thing. Presumably they will both
>> have the same @.@.IDENTITY? If this is the case, how is it possible to
>> manage such a situation?
>>
>> Thanks.
>>
> No, each session will have a different value, so there's no problem with
> concurrency - check out SCOPE_IDENTITY(), IDENT_CURRENT() and @.@.IDENTITY
> in Books Online.
> Note that just defining a column as an identity column is not enough to
> guarantee uniqueness - you can still create duplicates manually (see SET
> IDENTITY_INSERT in BOL), so if you want to use the column as a PK, make
> sure it is declared as a PK when you create the table.
> Simon

Concurrency

Do single commands (or stored procedures) execute concurrently, or they are executed one by one. How do you perform a lock during the execution of a command (or stored procedure).

Single commands are 'implied' transactions, that is the command executes completely, or nothing is done.

For multiple commands, where there is a need to make sure the several statements execute in a 'all or nothing' scenario, use TRANSACTIONS.

Refer to Books Online, Topic: Transactions

|||

Other good topics in the BOL are "Isolation mode" and locking hints.

Jens K. Suessmeyer.

http://www.sqlserver2005.de