TRANSACTION and SELECT *

[Frank Uray]

Hi all

I have a question about SQL Transactions:

For example, I have a table with 3 rows (ID 1, 2 and 3).
Now I insert a new row within BEGIN TRANSACTION, and I do not commit yet.
In another SQL Session, I try to select data.
When I do SELECT * FROM Table, it is blocked.
When I do SELECT * FROM Table WHERE ID = 2 I get a result.

How can I make SELECT * FROM Table possible even when
there is a open Transaction ? Of corse I only expect to get
the ID's 1, 2 and 3 back and not the new row.

I have looked about IsolationLevel, but this does not help.

Thanks for any comments !

Best regards
Frank Uray

[Uri Dimant]

Frank
alter database clientdb set allow_snapshot_isolation on

create table t1 (c int not null primary key,c1 char(1))

insert into t1 values (1,'a')

insert into t1 values (2,'b')

insert into t1 values (3,'c')

-----connection 1

set transaction isolation level snapshot

begin tran

insert into t1 values(4,'d')

--rollback

-----connection 2

set transaction isolation level snapshot

select c from t1

"Frank Uray" <Fran...@discussions.microsoft.com> wrote in message
news:513D9FE2-B51A-43D5...@microsoft.com...

[Frank Uray]

Hi Uri

Thanks a lot, that works fine :-))

Best regards
Frank Uray

"Uri Dimant" wrote:

> .
>