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

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

Answer 2

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 3

You're fine. All the other queries will go through just fine.

Answer 4

You need to review the ACID properties of transactions. You see that there is nothing to worry about if a transaction is rolled back or committed it has no effect on the outcome of other transactions.

Answer 5

It should roll back all queries made in the transaction, so it is more specific than your user\connection and definitely not all queries on the box.

Answer 6

Your rollback affects only your transaction. The I in ACID.

However, the rows, pages or whole table you locked will affect other users if they want to use them. It depends on:

• what they want to do
• lock timeout
• client command timeout