Is using commit in a transaction enough?

Question

Is using Commit in a transaction enough or I should use rollback too.

tabel1
  name   nvarchar(10)
  family nvarchar(20)

table 2 
    name nvarchar(10)
    family nvarchar(20) not null

begin transaction
insert into table2 (name) values('john')
insert into table1 (name,family) values('Joe','Lando')
commit transaction

as you see the first statement has error. should I use rollback somewhere?

How do you run the script? Are the table definitions part of the script (in that case they are 1. incorrect and 2. outside the transaction)? — Anders Abel, Jun 25 '11 at 10:28

score 2 · Accepted Answer · edited May 23 '17 at 11:48

You can skip ROLLBACK if you use SET XACT_ABORT ON (SQL Server 2000 link)

SET XACT_ABORT ON
begin transaction
insert into table2 (name) values('john')
insert into table1 (name,family) values('Joe','Lando')
commit transaction

From the link:

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

Now, if you don't use it you need rollback. Or close your connection.

One useful side effect of SET XACT_ABORT ON is that after a client CommandTimeout event, locks are released and the transactions rolled back. Otherwise, it doesn't happen until the connection is hard removed from SQL Server: it can stay open because of pooling.

Your bible should be "Error Handling in SQL 2000 – a Background" by Erland Sommarskog: read it. And on SO: Do I really need to use "SET XACT_ABORT ON"?

score 0 · Answer 2 · answered Jun 25 '11 at 10:27

Firstly, I doubt that stored procedure will actually compile because it will recognise that table2 does not have a 'family' field.

Secondly, it is probably advisable to incorporate some form of checking for your own sanity, but in any case, SQL server will rollback automatically on error.

Is using commit in a transaction enough?

2 Answers2