Transactions are intended to run completely or not at all. The only way to complete a transaction is to commit, any other way will result in a rollback.
Therefore, if you begin and then not commit, it will be rolled back on connection close (as the transaction was broken off without marking as complete).
As long as you don’t COMMIT or ROLLBACK a transaction, it’s still “running” and potentially holding locks.
If your client (application or user) closes the connection to the database before committing, any still running transactions will be rolled back and terminated.
Note:
If there was a syntax error then no transaction was ever created. If you try to rollback tran, you will get a message saying there was no transactions. If you try to exit the query without rollback or commit, the Window will close and the records will still remain.
If there was a non-syntax error, then transaction will be created. You can manually run rollback to roll back the transaction. If you try to exit the query without rollback or commit, the SSMS GUI will prompt you to commit/rollback the transaction before you can close the query.
begin tran
delete from stbTable01 -- 2
delete from stbTable02 -- 9
delete from stbTable03 where abc=2 -- syntax error
rollback tran
/*
-- Records should remain since there is no commit tran.
-- If you try to rollback, message will say there is no transaction.
-- If you close the query, the records will still remain.
select COUNT(*) from stbTable01 -- 2
select COUNT(*) from stbTable02 -- 9
*/
Comments