Bug #68885 a new parameter to control whether rollback a whole transaction on statment fail

• Submitted: 8 Apr 2013 7:46
• Modified: 21 Apr 2013 12:05
• Reporter: xiaobin lin (OCA)
• Status: Verified
• Category: MySQL Server: DML
• Severity: S4 (Feature request)
• Version: 5.5+
• OS: Any
• CPU Architecture: Any
• Tags: Contribution, rollback transaction

[8 Apr 2013 7:46] xiaobin lin

Description:
When errors like " Duplicate key" occurs, the current strategy only makes the statment rollback, but the whole tranaction continue.

Can the server offer a new parameter for user to choose whether rollback the transacion? It will reduce the interactive when transaction need to rollback (auto rollback).

for example:
delimiter ;;
begin; insert into t values(1,'a'); insert into t values(2,'a'); insert into t values(3,'a'); commit;;

if the pk values 2 already in the table, the client will receive a duplicate-key-error. And if the transaction can auto-rollback , the  "rollback" command can be saved.

How to repeat:
As above

Suggested fix:
a simple patch attached

[8 Apr 2013 7:47] xiaobin lin

based on 5.6.10

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: rollback_trans_on_stmt_fail.5610.diff (application/octet-stream, text), 1.45 KiB.

[21 Apr 2013 12:05] MySQL Verification Team

Thank you for a feature request and contribution!

[26 Jun 2013 12:50] Ståle Deraas

Hi Xiaobin Lin,

We have considered your contribution, and we have decided not to include it in the general product. The decision was taken based on the fact that it will increase code complexity while there is a good workaround - the complete transaction can be ROLLBACKed manually.