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..

Answer 1

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.