If a commit failed then the connection should be closed. This will cause the database to free any open transactions.

@renthraysk No, the connection must stay open and it is. The transaction is closed. But transaction here is just some data on client side (like some data used for prepared statements). So the transaction closing doesn't send 'ROLLBACK;' command to the sql server.

The COMMIT; command fail cases are quite rare. But such errors can appear in highly loaded environments or with nonstandard configurations and cause unpredictable application crushes if transaction isn't rolled back.

For example, COMMIT; can return error if PostgreSQL database is configured with synchronous_commit off to speed up it in production (Can committing an transaction in PostgreSQL fail?).
Or if memory-optimized tables are used in MS SQL server 2014 (https://www.sqlskills.com/blogs/bobb/a-more-complex-discussion-of-user-transactions-and-memory-optimized-tables/).

Those errors are usually hard to reproduce and to test with a real database (some db mocks would help driver developers of course, and tests with mocks should be written).

The requirement for ROLLBACK; after failed COMMIT; was described here: Why should i rollback after failed commit (with link to Oracle documentation).
If COMMIT-failed transactions aren't rolled back they can overflow transaction pool of database connection and hung some of threads or the whole application for a timeout (if that timeout is not an infinity).
Well advanced DBAs can construct their own cron jobs to kill such transactions if their databases allow this (an example for PostgreSQL: How to close idle connections in PostgreSQL automatically?).

@renthraysk Sorry I missread your reply (just missed should).

There are plenty of code samples where Tx.Commit() is used in a function or a method like:

func foo(db *DB) error {
	if tx, err := db.Begin(); err != nil {
		return err
	}
	defer tx.Rollback()
	if _, err = tx.Exec("[SOME SQL]"); err != nil {
		return err
	}
	return tx.Commit()
}

With this code it is not possible for callers to distinguish was it an error from tx.Exec() and we can continue to use our connection as the transaction was rolled back with defered tx.Rollback(),
or it was an error in tx.Commit() after wich defered tx.Rollback() has no effect and if buggy driver doesn't send ROLLBACK; we must use fatality or panic to close the connection.

To workaround this case with buggy driver we can only do everything ourselves without Tx at all:

func bar(db *DB) error {
	if _, err := db.Exec("BEGIN;"); err != nil {
		return err
	}
	if _, err = db.Exec("[SOME SQL]"); err != nil {
		db.Exec("ROLLBACK;")
		return err
	}
	if _, err = db.Exec("COMMIT;"); err != nil {
		db.Exec("ROLLBACK;")
	}
	return err
}

But it is preffered to not have such bugs in any of sql drivers.

If a commit fails, the transaction is (or will be) rolled back. Drivers are not buggy. Better off with a func like this to deal with transactions...

func Tx(db *sql.DB, fn func(tx *sql.Tx) error) error {
	tx, err := db.Begin()
	if err != nil {
		return err
	}
	if err := fn(tx); err != nil {
		tx.Rollback()
		return err
	}
	return tx.Commit()
}

@renthraysk Thanks for Your time.

I wish to notice a small difference in using of a "commit" term.
In the case of database a "commit" can mean just sending COMMIT; command to the database.
But db.Commit() is used to send "COMMIT;" and prepare connection for next transaction.
This difference should be covered by an sql driver.

Actually this is database dependent issue. For example, PostgreSQL differs from Oracle documentation
and rolls back automatically in case if COMMIT; fails in easily reproduced logical cases
(when an error message returned is "could not serialize access due to read/write dependencies among transactions"). So for this cases with PostgreSQL there is no need to support failed COMMIT; in a driver. And a transaction can be retried on the same connection immediately.
And so I'm going to just close this issue.

@renthraysk Small note about bugs in driver implementations.

The SQLite database allows only 1 open transaction at time. So it is very sensitive for mentioned problem.

Lets look what will be executed when tx.Commit() is called with current driver implementations listed officially:

• mattn/go-sqlite3/.../sqlite3.go#L442
• gwenn/gosqlite/.../sqlite.go#L522
• mxk/go-sqlite/.../sqlite3.go#L300
• rsc/sqlite/.../driver.go#L209

Two drivers do rollback on commit errors, two others don't.