0

I have a stored procedure called processWinningBet. It takes a pendingBet and does these things.

update balance table for user

creates a row in historic bets table

deletes the corresponding row in pending bet table

creates 2 entries in a ledgers table.

While writing this ( I know it's not great right now and im pretty sure I completely missed the mark with my implementation of optimistic locking but that's a different question i guess) I ran into times where I would have 1 row updated and then the other inserts and deletes would not happen. I thought transactions prevented this. I thought that if you had say 4 inserts wrapped in a transaction you would have 4 inserts or no inserts.

How do I absolutely guarantee I have 3 inserts, 1 update, and 1 delete happen, or all of it is rolled back?

I think I'm misunderstanding how transactions work and i need some of my own error handling? Would something like this be typical to make sure everything happens?

START TRANSACTION;
update
IF (SELECT ROW_COUNT() = 1 ) THEN
    delete
        IF (SELECT ROW_COUNT() = 1 ) THEN
            insert
                IF (SELECT ROW_COUNT() = 1 ) THEN
                    insert
                        IF (SELECT ROW_COUNT() = 1 ) THEN
                            insert
                                IF (SELECT ROW_COUNT() = 1 ) THEN
                                    COMMIT
                                ELSE ROLLBACK
                        ELSE ROLLBACK
                ELSE ROLLBACK
        ELSE ROLLBACK
    ELSE ROLLBACK

here is the current stored procedure

DELIMITER $$
 DROP PROCEDURE IF EXISTS ProcessWinningBet $$
  CREATE PROCEDURE ProcessWinningBet(
  IN pending_bet_id BIGINT,
    IN  _user_id BIGINT, 
    IN  _game_account_id BIGINT, 
    IN _amount DECIMAL(10,2),
    IN _bet_odds DECIMAL(10,2),
    IN _fee_percentage DECIMAL(10,2),
    IN _fee_amount DECIMAL(10,2),
    IN _user_has_reported BOOLEAN,
    IN _user_reported_result VARCHAR(40),
    IN _user_notes VARCHAR(400),
    IN _bet_created_date DATETIME,
    IN _user_reported_date DATETIME,
    IN _manager_id BIGINT,
    IN _manager_reported_result VARCHAR(40),
    IN _manager_notes VARCHAR(400)
    )
BEGIN
     DECLARE _old_version BIGINT DEFAULT 0;
     DECLARE _user_balance_before_fee DECIMAL(10,2) DEFAULT 0;
     DECLARE _user_balance_after_fee DECIMAL(10,2) DEFAULT 0;
     DECLARE _historic_bet_id BIGINT DEFAULT 0;
     

    SELECT id, user_id, balance, (available_balance + _amount + _amount -_fee_amount) AS new_available_balance,
    (balance + _amount -_fee_amount) AS new_balance ,
    version  INTO @id,@user_id, @balance, @new_available_balance, @new_balance, @_old_version 
       FROM balances
       WHERE user_id = _user_id;
       
       
       SET _old_version = @_old_version;
       SET _user_balance_before_fee = @new_balance;
       SET _user_balance_after_fee = @new_balance - _fee_amount;
       
       
       
       START TRANSACTION;
        
       UPDATE balances
       SET available_balance = @new_available_balance, balance = @new_balance, modified = CURRENT_TIMESTAMP,
           version = version + 1
       WHERE user_id = _user_id
           AND version = _old_version;
           
        

    IF (SELECT ROW_COUNT() = 1 ) THEN
     INSERT INTO historic_bets (user_id, game_account_id, amount, bet_odds, fee_percentage, fee_amount,user_has_reported, user_reported_result, user_notes, bet_created_date, user_reported_date, manager_id, manager_reported_result, manager_notes)
        VALUES (_user_id, _game_account_id, _amount, _bet_odds, _fee_percentage, _fee_amount,_user_has_reported, _user_reported_result, _user_notes, _bet_created_date, _user_reported_date, _manager_id, _manager_reported_result, _manager_notes);
    
    SELECT LAST_INSERT_ID() as historic_bet_id INTO @historic_bet_id LIMIT 1;
    SET _historic_bet_id = @historic_bet_id;
    
    call log_msg(concat('_historic_bet_id is: ', _historic_bet_id, ' and _user_balance_after_fee is: ', _user_balance_after_fee));
    
    
    DELETE FROM pending_bets WHERE id=pending_bet_id;
    
    INSERT INTO ledger (user_id, game_account_id, credit_user_id, debit_user_id, amount, reason , user_balance_result,   historic_bet_id, manager_id, manager_reported_result, manager_notes)
        VALUES (_user_id, _game_account_id, 77777777, _user_id, _fee_amount, "Fee For Bet Win",  _user_balance_after_fee,   _historic_bet_id, _manager_id, _manager_reported_result, _manager_notes);
    
    INSERT INTO ledger (user_id, game_account_id, credit_user_id, debit_user_id, amount, reason , user_balance_result,   historic_bet_id, manager_id, manager_reported_result, manager_notes)
        VALUES (_user_id, _game_account_id, _user_id, 77777777, _amount, "Bet Win",  _user_balance_before_fee,   _historic_bet_id, _manager_id, _manager_reported_result, _manager_notes);
        
           
    
        COMMIT;
    ELSE
        ROLLBACK;
    END IF;
END$$

Thank you

1 Answer 1

0

Perhaps the SELECT should be inside the transaction and with FOR UPDATE on the end:

START TRANSACTION;
SELECT ... FOR UPDATE;
UPDATE balances ...;
IF ... THEN ROLLBACK; RETURN; END IF;
...
COMMIT;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.