If you don't rollback a transaction will the change still be made?

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?

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?

StackExchange Database Administrators Q#109392, answer score: 19