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
Triage: Triaged: D5 (Feature request) / R3 (Medium) / E2 (Low)

[21 May 2008 23:45] Juha Vehnia
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
[22 May 2008 4:26] Valeriy Kravchuk
Thank you for a problem report. Please, send the results of

select @@autocommit;

from your testing environment.
[22 May 2008 17:48] Juha Vehnia
select @@autocommit returns 1
[22 May 2008 19:08] Ken Jacobs
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 ...
[22 May 2008 20:13] Sveta Smirnova
Thank you for the report.

Verified as described.
[2 Oct 2008 21:14] Konstantin Osipov
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
[2 Oct 2008 21:15] Konstantin Osipov
In any case, this is documented.