MySQL Bugs: #36866: Transactions and invalid sequence and no errors

Bug #36866	Transactions and invalid sequence and no errors
Submitted:	21 May 2008 23:45	Modified:	22 May 2008 20:13
Reporter:	Juha Vehnia	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: General	Severity:	S4 (Feature request)
Version:	5.0.22, 4.1, 5.0, 5.1, 6.0 BK	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	innodb, no errors, savepoint, transaction

Description:
Set savepoint doesn't check if the transaction already exists. This should fail immediately. Same seems to apply to rollback and begin. You may call these as many times anywhere you like without any errors. Rollback should not success unless there is no transaction currently going on. I do understand that start transaction can be called multiple times and the old transaction is automatically committed. However, it would be nice to get rid of this behavior and produce an error instead. It is critical that the correct sequence is enforced from application integrity standpoint! 

So basically the only way catch error right now is to check if the RELEASE CHECKPOINT fails. This far I haven't found a way to check if transaction is currently being used.

How to repeat:
Pretty simple:

mysql command prompt:

ROLLBACK; (No error)
SAVEPOINT test; (No error again!)

Suggested fix:
Summary:

1) START TRANSACTION shall have optional failure if another transaction is already in progress as mysql doesn't support nested transactions.
2) SAVEPOINT should always fail if transaction is not in progress
3) ROLLBACK should fail if there is no transaction in progress

+ Bonus add another way of checking if transaction is being used other than

SAVEPOINT test
RELEASE SAVEPOINT test
ERROR 1305 (42000): SAVEPOINT test does not exist

Thank you for a problem report. Please, send the results of

select @@autocommit;

from your testing environment.

select @@autocommit returns 1

This would not seem to be InnoDB specific (at least once there are other transactional engines). The checks requested could be/should be done at the MySQL level and apply to all (transactional) engines.

This also seems like a feature request, not an S2 problem ...

Thank you for the report.

Verified as described.

Triage: risk assessment depends on the fix. If we simply return a warning, it's fairly low. If we prohibit it, as requested in the bug report and prescribed by the standard, it's an incompatible change.
See also feature request in Bug#6861

In any case, this is documented.