Bug #64776 | Stored procedures are not ACID compliant | ||
---|---|---|---|
Submitted: | 27 Mar 2012 11:12 | Modified: | 31 Mar 2012 10:56 |
Reporter: | Patrick Mulvany (OCA) | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Stored Routines | Severity: | S3 (Non-critical) |
Version: | 5.5.21 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[27 Mar 2012 11:12]
Patrick Mulvany
[27 Mar 2012 12:22]
Patrick Mulvany
A dirty work arround is to do an explicit rollback and resignal within an exit handler. Note this appears to work on 5.5.21 but is not covered by the docs. CREATE PROCEDURE outer1 () BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END; INSERT INTO t1 (level,dt_sysdate,dt_outer) VALUES ('outer1-1', sysdate(),NOW()); CALL inner2(NOW()); INSERT INTO t1 (level,dt_sysdate,dt_outer) VALUES ('outer1-2', sysdate(),NOW()); CALL inner1(NOW()); INSERT INTO t1 (level,dt_sysdate,dt_outer) VALUES ('outer1-3', sysdate(),NOW()); CALL inner2(NOW()); INSERT INTO t1 (level,dt_sysdate,dt_outer) VALUES ('outer1-4', sysdate(),NOW()); END ;
[27 Mar 2012 22:25]
Patrick Mulvany
Probably S3 not S2 but might mean issue with entire Stored Procedure stack
[28 Mar 2012 5:47]
Valeriy Kravchuk
I had repeated your results (with autcommit=0), but unfortunately I fail to understand what exactly is a bug here. You had committed the results inserted successfully before the duplicate key -1. Then you call procedure again, it inserts one row and again fails at call to inner2() that tries to insert duplicate key -1. In case of error user gets error message and can decide if it is OK to commit or entire transaction should be rolled back. So, what is the problem?
[28 Mar 2012 8:26]
Patrick Mulvany
Altered Synopsis from: 'nested stored procedures are asynchronously committed' to 'Stored procedures are not ACID compliant' Further investigation indicates the general case is simply that stored procedures on an ACID compliant engine and not ACID. Further examples following
[28 Mar 2012 8:28]
Patrick Mulvany
Example script of ACID and non ACID actions
Attachment: example-sp.sql (application/octet-stream, text), 1.99 KiB.
[28 Mar 2012 8:29]
Patrick Mulvany
Example output of script of ACID and non ACID actions
Attachment: example.log (application/octet-stream, text), 7.42 KiB.
[28 Mar 2012 8:32]
Patrick Mulvany
Should have had at start of sql script mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) darn copy and paste
[28 Mar 2012 9:17]
Patrick Mulvany
The following is a better work around to produce an ACID compliant stored procedure that rolls back updates and resignals. DELIMITER $$ CREATE PROCEDURE outer2 () BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK TO SAVEPOINT savepoint_outer2; RESIGNAL; END; SAVEPOINT savepoint_outer2; INSERT INTO t1 (level,dt_sysdate,dt_outer) VALUES ('outer2-1', sysdate(),NOW()); CALL inner2(NOW()); INSERT INTO t1 (level,dt_sysdate,dt_outer) VALUES ('outer2-2', sysdate(),NOW()); CALL inner1(NOW()); INSERT INTO t1 (level,dt_sysdate,dt_outer) VALUES ('outer2-3', sysdate(),NOW()); CALL inner2(NOW()); INSERT INTO t1 (level,dt_sysdate,dt_outer) VALUES ('outer2-4', sysdate(),NOW()); END ; $$ DELIMITER ;
[31 Mar 2012 10:56]
Sveta Smirnova
Thank you for the feedback. Actually this behavior is documented at http://dev.mysql.com/doc/refman/5.5/en/begin-end.html: "The optional [NOT] ATOMIC clause is not supported. This means that no transactional savepoint is set at the start of the instruction block and the BEGIN clause used in this context has no effect on the current transaction." So this is not a bug. But your request is valid, so I change it to feature request: add support for [NOT] ATOMIC clause for stored procedures.