9

Say I am running a query

begin tran

update users
set name = 'Jimmy'
where name = 'john'

If I DON'T rollback the transaction, will these changes still be made, will it throw an error or will it act as a rollback anyway?

  • I guess that depends on the behaviour of the client software you use to run these statements or the application where they are embedded. Some might auto-commit if configured (SSMS will do that by default). – mustaccio Aug 7 '15 at 15:26
  • 3
    Explicitly declared transactions do not auto-commit. Since Josh has declared the start of a transaction it will behave exactly as Justin Cave has answered. – Dave Aug 7 '15 at 15:37
15

You have to understand what a transaction is - It is a single unit of work. It is ALL or NOTHING (follows ACID properties) and this guarantees the database consistency.

If I DON'T rollback the transaction, will these changes still be made

The changes will be made only if you commit. SQL server will record all the changes to transaction log and once you commit, they are hardened to the data file.

If you don't commit, then your transaction will remain OPEN indefinitely - which you can see in sys.dm_tran_active_transactions / sys.dm_tran_database_transactions / sys.dm_tran_session_transactions or using DBCC OPENTRAN.

Also, what you have initiated is an explicit transaction, which should be explicitly ended with a COMMIT or ROLLBACK statement.

Also read - Is it a bad practice to always create a transaction?

  • @JoshStevenson I think its worth the while reading about Atomic Operations in the database context. – Nelz Aug 7 '15 at 19:43
10

If you neither commit nor rollback the transaction, the transaction will continue to exist indefinitely. It will continue to hold its locks, potentially blocking other sessions, until either you end the transaction via a commit or a rollback or until a DBA comes along and kills the session (or until something like a network hiccup causes the connection to fail). If a DBA kills the session, they will implicitly issue a rollback for this and any other open transactions.

1

Changes won't be made until and unless the transaction is committed , its all or nothing as mentioned above answers ,

Atomicity is the reason behind this , You can check ACID transaction properties , its the fundamental properties that a database system follow .

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.