Transactional Withdrawal
start transaction;
commit;
delimiter $$
create procedure withdraw(in account_id int, in amount numeric(7, 2), out success varchar(50))
begin
declare current_balance numeric(7, 2) default 0.0;
start transaction;
select balance into current_balance from accounts where id=account_id for update;
if current_balance >= amount then update accounts set balance = balance - amount where id=account_id;
set success= "With drawn";
else set success="try with Lower amounts";
end if;
commit;
end$$
delimiter ;
call withdraw(1, 251, @success);
select @success;
--------------------------------------
Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.25 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use classicmodels;
Database changed
mysql> select * from accounts;
+------+----------+
| ID | balance |
+------+----------+
| 1 | 30500.50 |
| 2 | 4500.50 |
| 3 | 500.11 |
+------+----------+
3 rows in set (0.00 sec)
mysql> select * from acounts;
ERROR 1146 (42S02): Table 'classicmodels.acounts' doesn't exist
mysql> show errors;
+-------+------+---------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------+
| Error | 1146 | Table 'classicmodels.acounts' doesn't exist |
+-------+------+---------------------------------------------+
1 row in set (0.00 sec)
mysql> select ram from accounts;
ERROR 1054 (42S22): Unknown column 'ram' in 'field list'
mysql> show errors;
+-------+------+--------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------+
| Error | 1054 | Unknown column 'ram' in 'field list' |
+-------+------+--------------------------------------+
1 row in set (0.00 sec)
mysql> select fff from acounts;
ERROR 1146 (42S02): Table 'classicmodels.acounts' doesn't exist
mysql> select fff from accounts;
ERROR 1054 (42S22): Unknown column 'fff' in 'field list'
mysql> select * from accounts where id=33;
Empty set (0.00 sec)
mysql> show warnings;
Empty set (0.00 sec)
mysql> select balance into @value from accounts where id=3;
Query OK, 1 row affected (0.00 sec)
mysql> select @value;
+--------+
| @value |
+--------+
| 500.11 |
+--------+
1 row in set (0.00 sec)
mysql> select balance into @value from accounts where id=33;
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql> show warnings;
+---------+------+-----------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------+
| Warning | 1329 | No data - zero rows fetched, selected, or processed |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)
mysql> select @value;
+--------+
| @value |
+--------+
| 500.11 |
+--------+
1 row in set (0.00 sec)
mysql> select balance into @value1 from accounts where id=33;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select @value1;
+------------------+
| @value1 |
+------------------+
| NULL |
+------------------+
1 row in set (0.00 sec)
mysql>