Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

database/sql: documentation of Tx.Commit() should notify driver developers to rollback if commit fails #33242

Closed
oshatrk opened this issue Jul 23, 2019 · 7 comments

Comments

@oshatrk
Copy link

@oshatrk oshatrk commented Jul 23, 2019

What version of Go are you using (go version)?

go version go1.12.7 linux/amd64

Does this issue reproduce with the latest release?

yes

What operating system and processor architecture are you using (go env)?

go env Output
any

What did you do?

read https://golang.org/pkg/database/sql/#Tx.Commit

What did you expect to see?

A note for sql database driver developers about the case
when database commit fails then driver's commit implementation should try to rollback database transaction to free database transaction resources.
This rollback is required to be implemented in driver as Tx.Commit() always closes transaction.

What did you see instead?

Commit commits the transaction.

@renthraysk
Copy link

@renthraysk renthraysk commented Jul 23, 2019

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

@oshatrk
Copy link
Author

@oshatrk oshatrk commented Jul 23, 2019

@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.

@oshatrk
Copy link
Author

@oshatrk oshatrk commented Jul 23, 2019

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?).

@oshatrk
Copy link
Author

@oshatrk oshatrk commented Jul 23, 2019

@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.

@renthraysk
Copy link

@renthraysk renthraysk commented Jul 23, 2019

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()
}
@oshatrk
Copy link
Author

@oshatrk oshatrk commented Jul 25, 2019

@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.

@oshatrk
Copy link
Author

@oshatrk oshatrk commented Jul 25, 2019

@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:

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

@oshatrk oshatrk closed this Jul 29, 2019
@golang golang locked and limited conversation to collaborators Jul 28, 2020
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
3 participants
You can’t perform that action at this time.