Fun with Isolation Levels

Open two sessions (query-windows) to an sql-server.

In the first session execute the following:

drop table t
create table t
(a int primary key, b int ,c int unique)
insert into t values (1,1,1)
insert into t values (2,2,2)
insert into t values (3,3,3)

begin tran
update t set b=20 where a=2
-- switch to the other session

Remember the unique constraint on column c.

Now execute the following in the second session:

select * from t

(it should block…)

Switch back to the first session:

update t set a = 4 where a = 1
update t set a = 0 where a = 3
select * from t
commit tran

So far, so good… nothing unexpected here. But the query in the second session should now also have finished:

Hmm… well, not quite the same as above… and what happened to the unique constraint on column c?