Lumen/Laravel 5.1 - calling stored procedure appears to commit transaction

Question

I'm building integration tests in Lumen 5.1.3. Most of my tests roll back their transactions just fine. Here is a summary of what a successful rollback may look like (I'm simplifying the code - really the transaction management is done via a trait I've attached to PHPUnit's @before annotation):

DB::beginTransaction();
DB::table('user')->insert(
            [
                'guid' => $guid,
                'username' => $username,
                'email' => $email,
                'status' => USER_STATUS_ACTIVE,
                'type' => USER_TYPE_REGULAR,
                'created_on' => $currentDateTime
            ]
        );
DB::rollBack();

All is good. The user table is empty after this test.

Now - here is where I get confused. If I add an SP to the mix, things don't get rolled back:

DB::beginTransaction();
DB::table('user')->insert(
            [
                'guid' => $guid,
                'username' => $username,
                'email' => $email,
                'status' => USER_STATUS_ACTIVE,
                'type' => USER_TYPE_REGULAR,
                'created_on' => $currentDateTime
            ]
        );

DB::statement('CALL s_generate_leaderboards(?)', [$oneWeekAgo]);
DB::rollBack();

The new User record is indeed committed now. DB::rollBack(); has no effect once I've executed a stored proc.

There are no errors - everything succeeds. I have even made sure that the DB::rollBack(); command is being reached.

So - why is my transaction being committed if and only if I call an SP? So frustrating.. :(

EDIT 1:

Your suspicion was bang on. Here is the SP (it's large so I've collapsed 90% of it):

CREATE PROCEDURE s_generate_leaderboards (IN week_ago_date DATETIME)
BEGIN

-- Empty and regenerate the all-time leaderboard:

TRUNCATE TABLE all_time_leaderboard;

INSERT INTO all_time_leaderboard (...)
...;

-- Empty and regenerate last week's leaderboard:

TRUNCATE TABLE last_week_leaderboard;

INSERT INTO last_week_leaderboard (...)
...;

END

So maybe it's not the SP itself that is committing, but only the TRUNCATE statements within the SP?

TRUNCATE is being used because there is a position column in the leaderboard tables that auto-increments, and needs to be reset. DELETE FROM table does not reset auto-increments..

Bogdan · Accepted Answer · 2015-07-21T01:05:32.010

2

Some MySQL statements cause an implicit commit that ends any transaction active in the current session. So your issue might be caused by a statement that is part of the stored procedure.

Based on the name of the procedure I'm guessing you might perhaps be using TRUNCATE TABLE to empty a table where you need to refresh the information, I might be wrong though :). In any case you can check the official MySQL Documentation I've linked below and see if this is indeed the case (since the list of statements that can cause this behavior is a bit long):

Statements That Cause an Implicit Commit

Since you have columns with auto-increment and the need to reset them, you might be out of luck. Because the only two ways (that I know of) to reset that is by either using:

TRUNCATE TABLE tablename

or

ALTER TABLE tablename AUTO_INCREMENT = 1

But both TRUNCATE TABLE and ALTER TABLE are statements that trigger implicit commits.

edited Jul 21 '15 at 01:05

answered Jul 20 '15 at 20:46

Bogdan

43,166
12
128
129

Yes I believe so. You could try replacing the `TRUNCATE TABLE all_time_leaderboard` statements with `DELETE FROM all_time_leaderboard` (and the same of course for `last_week_leaderboard`). This will clear the tables of data (although it does have the drawback of not resetting the primary key auto increment value to 1). But to test if that is indeed the issue it will have to do. – Bogdan Jul 21 '15 at 00:57
I've updated my answer with an explanation, but unfortunately not a solution. If you could post the entire contents of the stored procedure, maybe I could offer an alternative solution for generating the position value in another manner, although I cannot promise anything :). – Bogdan Jul 21 '15 at 01:08
Thanks, I'm going to give my SP a tweak to remove the ```TRUNCATE```, just to see if your theory is correct. After that, I (perhaps with your help? :)) will worry about how to re-work the SP. Either way, if it is indeed the ```TRUNCATE```, I'll give you the answer, since I'll know exactly what's the problem! – Matthew Housser Jul 21 '15 at 02:10
Transaction indeed started working once I removed the ```TRUNCATE``` from my SP. So - it looks like the problem is not SP's in general, which is at least some good news. We have over a dozen SPs in the system, and this is the only one that does a ```TRUNCATE```. It's a special one. I'll either adjust the SP logic or accommodate its behaviour in the integration test fixture. Either way, I have given you the answer; I know where the problem lies now. Thanks! – Matthew Housser Jul 21 '15 at 02:47

Lumen/Laravel 5.1 - calling stored procedure appears to commit transaction

1 Answers1