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?
asked Aug 7, 2015 at 15:20
-
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).– mustaccioCommented Aug 7, 2015 at 15:26
-
3Explicitly declared transactions do not auto-commit. Since Josh has declared the start of a transaction it will behave exactly as Justin Cave has answered.– DaveCommented Aug 7, 2015 at 15:37
Add a comment
|
3 Answers
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.– NelsonCommented Aug 7, 2015 at 19:43
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.
answered Aug 7, 2015 at 15:27
-
This is the proper answer. Without this piece of information it made no sense for me for both the COMMIT and ROLLBACK statements to be used altogether.– TarecCommented Feb 6, 2020 at 12:50
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 .
