469,616 Members | 1,806 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,616 developers. It's quick & easy.

manage transaction to avoid locks

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

Jul 23 '05 #1
4 3471
(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
Jul 23 '05 #2
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

Jul 23 '05 #3
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
Jul 23 '05 #4
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

Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

12 posts views Thread by John Sidney-Woollett | last post: by
15 posts views Thread by Zeng | last post: by
2 posts views Thread by Christian Stooker | last post: by
2 posts views Thread by Ryan Liu | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.