Does a transaction affect all queries?

Question

I started a transaction using BEGIN TRANSACTION in Management Studio but I forgot to ROLLBACK or COMMIT it for about 10 minutes. I freaked out and went back to ROLLBACK my changes. Did this rollback all queries that went through the server during that time or just through my user/connection?

Answer 1

Just your connection :-)

(Edit: rather your transaction, since the BEGIN TRANSACTION. If you did updates before the BEGIN TRANSACTION in the same session, they will of course not be rolled back)

BUT: It could have given SELECTs of other sessions the wrong answer depending on what lock types and query hints that were being used...

An example:

In one SQL Studio session, do the following:

CREATE TABLE a(a INT)

INSERT INTO a VALUES(1)

BEGIN TRANSACTION

UPDATE  a 
SET a = 2

SELECT *, @@TRANCOUNT
FROM a

-> You will see '2, 1' as result

Open a new session (tab in Sql studio)

Do:

SELECT *, @@TRANCOUNT
FROM a (NOLOCK)

You will see '2, 0'

Now, in first session, do

ROLLBACK TRANSACTION

SELECT *, @@TRANCOUNT
FROM a

-> transaction rolled back, and you see '1, 0'

-> a select in second session will also show '1, 0'

so: If you use (NOLOCK) hint, you can get uncommitted data as result -> which might lead to very unexpected effects :-)

Dont forget:

DROP TABLE a

when you're done ;)

Answer 2

It should only affect your transaction, so only things that were done in your session during that time.