Can a Commit Statement (In SQL) Ever Fail? How

SQL Server: How to make a transaction commit fail?

If you are able to time it correctly in a testing framework you can do few things:

1. Kill session from a separate connection in a testing framework.

2. Change firewall configuration to emulate network error.

3. Switch database to single user mode or stop SQL Service.

What happens if you don't commit a transaction to a database (say, SQL Server)?

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.

Why does this SQL Server Transaction Commit even though an Update Statement Fails

No.

You would need to use SET XACT_ABORT ON to get the behaviour you apparently expect or add some error handling and only commit if no errors occurred or rollback otherwise.

Errors in SQL Server may abort the statement, scope, batch or connection.

In this case the error just aborts the statement then execution continues on with the next statement.

See Error Handling in SQL 2000 for more about this.

---

Related Topics