2

A single action in PHP has to go through the following steps:

  1. Execute a no. of mysql statements from PHP PDO, based on lots of business logic.
  2. Execute a stored procedure.
  3. Perform some more MySQL statements from PDO.

The entire process needs to be a single transaction. If any error takes place in the MySQL stored procedure, the entire transaction has to be rolled back. (The stored procedure has queries to create temporary tables, do a cursor-based scan and perform inserts.) Even if the error occurs in the PDO after the stored procedure, the transaction has to be completely rolled back, including any changes that occurred in the stored procedure.

The PDO based queries were programmed quite sometime back. And the stored procedure is being newly introduced based on new requirements from client.

In PHP, a Transaction is started at the start of Step 1. And there is a commit at the end of Step 3. And there is a catch block at the end, which causes rollback.

Should a Transaction be started in the stored procedure? If so, how to rollback completely on error? Or should we manually set auto-commit to false in the stored procedure? Or, is there some other way to inform MySQL that this stored procedure is already a part of a transaction.

If not, is the atomicity guaranteed for the entire action?

Drew
  • 29,895
  • 7
  • 74
  • 104
mohitp
  • 1,305
  • 2
  • 16
  • 20
  • 1
    Stored procedure would be a part of the transaction which you'd start via PDO. Naturally, you'd have to use a storage engine that supports transactions. – N.B. Oct 05 '12 at 11:38
  • Interesting question. I don't think PDO natively supports nested transactions. You could probably use [savepoints](http://stackoverflow.com/q/1306869/871050) – Madara's Ghost Oct 05 '12 at 11:40
  • @N.B.: I just tried by introducing an error in the stored procedure. And the action did not rollback. Infact, all changes till the point of the error have been committed (including those that occurred before the call to the stored procedure). The storage engine is InnoDB. – mohitp Oct 05 '12 at 12:16
  • Im betting that the PDO db handle and the implied handle in the SP are different. The transaction will not be shared between them. I can't find any specific statement that shows this though. – ethrbunny Oct 05 '12 at 12:16
  • 1
    @N.B.: Just found that the commit was being caused due to a coding error in my Stored Procedure. Before the creation of the temporary Table, i was issuing a `Drop Table` statement, without the 'Temporary` keyword, which was causing an auto-commit. As you have mentioned, stored procedure seems to be a part of the Transaction. For sake of completion and to help some confused coder like me, this is a note from MySQL docs **Within all stored programs the parser treats BEGIN as the beginning of a BEGIN ... END block. Begin a transaction in this context with START TRANSACTION instead** – mohitp Oct 06 '12 at 04:20

1 Answers1

-1

It's the link for mohip's quote: http://dev.mysql.com/doc/refman/5.6/en/begin-end.html Transaction is not run in stored procedure automatically, need to trigger it by "START TRANSACTION", I was about to ask this question. Thanks for the quote.

N Zhang
  • 119
  • 1
  • 3