MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE bank1 (user_id INT,
                    balance DECIMAL(10,2) CHECK (balance >= 0));
CREATE TABLE bank2 LIKE bank1;
INSERT INTO bank1 VALUES (1,1000);
INSERT INTO bank2 VALUES (2,2000);
SELECT * FROM bank1;
SELECT * FROM bank2;


user_id
balance


1
1000.00




user_id
balance


2
2000.00


CREATE PROCEDURE transfer (id_from INT, id_to INT, amount DECIMAL(10,2))
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
    END;
    START TRANSACTION;
    UPDATE bank2 SET balance = balance + amount WHERE user_id = id_to;
    UPDATE bank1 SET balance = balance - amount WHERE user_id = id_from;
    COMMIT;
END
-- try to transfer available money amount
CALL transfer (1,2,600);
SELECT * FROM bank1;
SELECT * FROM bank2;


user_id
balance


1
400.00




user_id
balance


2
2600.00


-- try to transfer excess money amount
CALL transfer (1,2,600);
SELECT * FROM bank1;
SELECT * FROM bank2;


user_id
balance


1
400.00




user_id
balance


2
2600.00