manage transaction to avoid locks

[extmb]

Hi,
I am quite puzzled how SQLServer manages transactions.
Whatever the isolation level I set when performing an insertion, other
connections do not have access to the table in select mode.

Example in SQL Analyzer:
create table foo (
id numeric(10),
data varchar(100)
)

On Connection 1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
BEGIN TRANSACTION
insert into foo(id,data) values (1,'data');

On Connection 2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
select * from foo
-> QUERY HANGS

On Connection 1
COMMIT

On Connection 2
Get the result

Using READ COMMITTED level, I was expecting not to lock the table when
performing the select.

Thanks in advance for your help,
Cedric

[Erland Sommarskog]

(ex***@yahoo.fr) writes:

I am quite puzzled how SQLServer manages transactions.
Whatever the isolation level I set when performing an insertion, other
connections do not have access to the table in select mode.

Example in SQL Analyzer:
create table foo (
id numeric(10),
data varchar(100)
)

On Connection 1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
BEGIN TRANSACTION
insert into foo(id,data) values (1,'data');

On Connection 2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
select * from foo
-> QUERY HANGS

On Connection 1
COMMIT

On Connection 2
Get the result

Using READ COMMITTED level, I was expecting not to lock the table when
performing the select.

Why not? READ COMMITTED means just that, read committed data, and there
is uncommitted data in the table.

You can access the uncommitted data if you change the isolation level
for connection 2 to READ UNCOMMITTED.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[extmb]

Ok thanks,
However the question is how can I avoid dirty reads i.e. uncommitted
data without being locked ?
This corresponds to the default behaviour in Oracle.
Thanky you

[Alan Brewer [MSFT]]

Concurrency control in SQL Server 2000 is done using locking. SQL Server
2005 introduces a new feature, snapshot isolation, that operates more like
the Oracle default you mentioned. There is a link to a whitepaper describing
the snapshot isolation feature as it is in SQL Server 2005 Beta 2 here:

http://msdn.microsoft.com/SQL/2005/2...s/default.aspx

--
Alan Brewer [MSFT]
Content Architect
SQL Server Documentation Team

This posting is provided "AS IS" with no warranties, and confers no rights

[Gang He [MSFT]]

The scan from the second connection has to wait on the lock on the newly
insert row from the uncommitted transaction in connection1 because under
READ COMMITTED isolation it can't see dirty data.

The next SQL Server release will provide a new isolation level named
SNAPSHOT that will allow the second connection not to block on the
uncommited insert from the first connection, much like Oracle's scan
behavior.

--
Gang He
Software Design Engineer
Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.
<ex***@yahoo.fr> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...

Hi,
I am quite puzzled how SQLServer manages transactions.
Whatever the isolation level I set when performing an insertion, other
connections do not have access to the table in select mode.

Example in SQL Analyzer:
create table foo (
id numeric(10),
data varchar(100)
)

On Connection 1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
BEGIN TRANSACTION
insert into foo(id,data) values (1,'data');

On Connection 2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
select * from foo
-> QUERY HANGS

On Connection 1
COMMIT

On Connection 2
Get the result

Using READ COMMITTED level, I was expecting not to lock the table when
performing the select.

Thanks in advance for your help,
Cedric