Mysql – Rows not updated in transactions without any errors

Posted on

Question :

I have a stored procedure that updates a user’s balance and inserts a transaction log. I don’t know why, but sometimes it doesn’t update the users table.

Stored Procedure Code

DELIMITER $$
USE `db_name`$$
DROP PROCEDURE IF EXISTS `update_balance`$$
CREATE DEFINER=`username`@`%` PROCEDURE `update_balance`
    (IN balancechange INT,
     IN userid INT,
     IN tableName VARCHAR(100),
     IN `type` INT,
     IN description  VARCHAR(1000) CHARSET utf8,
     IN trans_id VARCHAR(150))
BEGIN
START TRANSACTION;
SET @tmp = (SELECT id FROM users WHERE id = userid FOR UPDATE); 
UPDATE users SET gold = @gold := ((@oldbalance:=gold) + balancechange)
    WHERE id = userid AND (gold + balancechange) >= 0;
SET @countRow =  ROW_COUNT();
IF (@countRow > 0) THEN
    SET @t1 =CONCAT("INSERT INTO ", tableName,
                    " (user_id, type, balance_change, old_balance, new_balance, description, trans_id)
                    values ('", userid, "', '", `type`, "', '", balancechange, "', '", @oldbalance, "', '", @gold, "', '", description, "', '", trans_id, "')");
    PREPARE stmt3 FROM @t1;
    EXECUTE stmt3;
    DEALLOCATE PREPARE stmt3;
    SELECT @oldbalance AS oldbalance , @gold AS gold,
           balancechange, 0 AS result;
ELSEIF (balancechange = 0) THEN
    SET @oldbalance = (SELECT gold FROM users WHERE id = userid LIMIT 1);
    SET @t1 =CONCAT("INSERT INTO ", tableName,
                    " (user_id, type, balance_change, old_balance, new_balance, description, trans_id)
                    values ('", userid, "', '", `type`, "', '", 0, "', '", @oldbalance, "', '", @oldbalance, "', '", description, "', '", trans_id, "')");
    PREPARE stmt3 FROM @t1;
    EXECUTE stmt3;
    DEALLOCATE PREPARE stmt3;  
    SELECT @oldbalance AS oldbalance , @oldbalance AS gold,
           0 AS balancechange, 0 AS result;
ELSE 
    SET @oldbalance = (SELECT gold FROM users WHERE id = userid LIMIT 1);
    SELECT @oldbalance AS oldbalance , @oldbalance AS gold,
           0 AS balancechange, -1 AS result;
END IF;
COMMIT;
END$$

DELIMITER ;

Usage

CALL update_balance(1000, 123, 'user_transaction201806', 1, '{$desc}', '123456789')

The users table is InnoDB, the user_transaction201806 is MyISAM.

Sometime the data for a user isn’t updated in the users table, but the data for a user is still added in the relevant transaction table (e.g. user_transaction201806) with correct data.

Because the table containing the user’s transactions is in myISAM format, the transaction cannot be rolled back.

Answer :

MyISAM ignores all transaction semantics. Convert to InnoDB !

But maybe that does not answer the Question. The one MyISAM table seems to be merely an audit log.

Further head scratching….

Please provide the transaction log for the bad case.

Leave a Reply

Your email address will not be published. Required fields are marked *