Best Way To Cancel An Ongoing Mysql Transaction Asynchronously in PHP?

[Torite]

I am doing this in a PHP script, but it may be mostly a database question. I am trying to cancel a mysql transaction by checking some external flag.

I have a long running mysql transaction that sometimes I want to cancel and just rollback based off come factor external to the transaction itself. The same program making the connection must be the one to check the value.

I thought a basic flag where if X = true, stop and rollback the transaction would work. The problem is that using the db itself to set the flag only works if the transaction isolation level is set to read-uncommitted, because otherwise the first time the program checks the flag, it will retain the same value every other time it checks it during the transaction, even if it has been changed in the db.

What I do currently is place a function that checks the db flag wherever I want to potentially stop the transaction.

My script looks basically like this:

<?php
// set transaction isolation level to read-committed
$pdo->beginTransaction();

// do work

CheckIfTransactionShouldContinue();

// do work

CheckIfTransactionShouldContinue();
$pdo->commit();

I would rather not have to set the transaction isolation level to read-uncommitted due to an increased likelihood of (very hard to diagnose) transaction errors.

Is this the correct architecture? And if it is, am I implementing it correctly?

[rpkamp]

The easiest is probably to open a second connection to the database so you can read outside the transaction and can actually see changes as they happen.

Or if the process that is running the transaction and the process that is signalling that process are running on the same host you can also look into sending a POSIX signal from one to the other telling it to stop.

As far as “correct” goes, the code does what you say it should do, but I have doubts whether this is the solution to your actual problem - it seems a bit flaky to it like this.

[Torite]

Thanks, that’s a really good idea.

@rpkamp

As far as “correct” goes, the code does what you say it should do, but I have doubts whether this is the solution to your actual problem - it seems a bit flaky to it like this.

Maybe I can explain better.

The transaction represents the creation of an entity, but this takes quite a while, so a user may decide to cancel and create a new entity while the first one is being created. The user can only have one entity at a time. In order to allow canceling during the process, I have a flag that records the last attempt to create the entity and if that flag is not the one associated with the current on-going process of creating the current entity (because the creation of the current entity has been canceled), the current entity’s creation should abort and rollback.

Maybe there is a better solution still? (The 2nd database connection idea is still better than what I was doing.)

Here is how it would look with a 2nd database connection. I tested this (in a testing environment) and it worked. (This is very simplified.)

<?php

$pdo1 = new PDO('mysql:host=127.0.0.1;dbname=my_db, user, password');
$pdo2 = new PDO('mysql:host=127.0.0.1;dbname=my_db, user, password');

// get the id of the current process using pdo1

$pdo1->beginTransaction();

// do work. all work uses $pdo1 as its connection

CheckIfTransactionShouldContinue($pdo2, $pdo1, $id); // pass and use pdo2 connection

// do work. all work uses $pdo1 as its connection
CheckIfTransactionShouldContinue($pdo2, $pdo1, $id);
$pdo1->commit();

function CheckIfTransactionShouldContinue(PDO $pdo2, PDO $pdo1, $id)
{
	// get the flag's current value
	$q = $pdo2->query('SELECT continue_transaction FROM flags WHERE id = :id');
	$q->bindValue(':id', $id);
	$q->execute();
	
	$r = $q->fetch();
	
	// roll back the transaction of pdo1 if the flag says to stop
	if ($r['continue_transaction'] == false)
	{
		$pdo1->rollBack();
	}
	
}

[rpkamp]

Why does the creation of the entity take so long? Are the external services involved?

Just performing a few queries shouldn’t be too hard.

[Torite]

No external APIs are involved, and the entity is the result of what amounts to a huge, complex operation that involves tens of thousands of inserts, selects, and updates (all of which have been batched for speed). But speeding it up to the point where it doesn’t matter whether a user can cancel it or not isn’t really an option.

[m_hutley]

I’m not… entirely certain that $pdo2 is actually necessary, though I suppose it depends a bit on what’s being doing in the commented out section, but otherwise the theory looks mostly solid to me.

There exists a flaw in the idea of calling a void function in the middle of the work cycle without keying an abort into it; rolling back doesnt prevent $pdo1 from being used as an existing database connection by the second half of the ‘do work’.

EDIT: actually you’ll have an exception thrown if you reach that state, because commit(); will flag that there is no active transaction and throw PDOException to that effect.