I need to recover and increment a counter, which is not a self-incremental id in a table, and avoid concurrency errors. I do not want to do table locks because of its high cost in resources.
I have been viewing this article ( link ) and I've tried the part that says "Select and Update in Single Statement" and it works perfect for me in MariaDB.
I've been trying to implement it with PDO and I do not succeed.
This is my function:
$dbConnection = new \PDO('mysql:host='.$host.';dbname='.$dbal_dbname.';charset=utf8', $dbal_user, $dbal_password, [\PDO::ATTR_PERSISTENT => true, \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION]);
$stmt = $this->dbConnection->prepare('UPDATE config SET config_value = (@cur_value := config_value) + 1 WHERE config_name = "proximo_asiento"');
$stmt->execute();
$stmt = $this->dbConnection->prepare('SELECT @cur_value;');
$returned_field = $stmt->execute();
/* Tambien he probado sustitutendo la ultima linea con: */
$stmt->execute();
$returned_field = $stmt->fetch(\PDO::FETCH_ASSOC);
The table counter, that is, the config_value field corresponding to the config_name of "next_seat" is increased well but the result, the returned_field, is always 1 even if the field "next_seat is 235".
The connection, the PDO commands, ... are checked.
I think the problem is in how I retrieve the last SQL. But I can not find where it is wrong.
A little help ... thanks.