Does a transaction affect all requests?

Question

I started the transaction using BEGIN TRANSACTION in Management Studio, but I forgot ROLLBACK or COMMIT for 10 minutes. I got scared and went back to rollback my changes. Did this rollback remove all requests that went through the server during this time or only through my user / connection?

Answer 1

Just your connection :-)

(Edit: most likely your transaction, since BEGIN TRANSACTION. If you made updates before BEGIN TRANSACTION in the same session, they, of course, will not be thrown back)

BUT: It might give SELECT other sessions the wrong answer depending on what types of locks and query hints were used ...

Example:

In a single SQL Studio session, complete the following steps:

 CREATE TABLE a(a INT) INSERT INTO a VALUES(1) BEGIN TRANSACTION UPDATE a SET a = 2 SELECT *, @@TRANCOUNT FROM a 

-> As a result, you will see "2, 1"

Open a new session (tab in Sql studio)

do:

 SELECT *, @@TRANCOUNT FROM a (NOLOCK) 

You will see "2, 0"

Now, in the first session, do

 ROLLBACK TRANSACTION SELECT *, @@TRANCOUNT FROM a 

-> transaction rollback and you see '1, 0'

-> the choice in the second session will also show '1, 0'

like this: if you use the hint (NOLOCK), you can get unfixed data as a result →, which can lead to very unexpected effects :-)

Do not forget:

 DROP TABLE a 

when you finish;)

Answer 2

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

Answer 3

Are you okay. All other queries will go very well.

Answer 4

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

Answer 5

You need to view the ACID transaction properties . You see that there is nothing to worry about, if a transaction is rolled back or is committed, it does not affect the result of other transactions.

Answer 6

Rollback only affects your transaction. I'm in ACID.

However, the rows, pages, or the whole table that you have blocked will affect other users if they choose to use them. It depends on the:

• what do they want to do
• lock time
• client team timeout