Wait, I changed my mind.

We don't always have the right decision all the time. Sometimes we change our minds because of a sudden change in conditions. This is undoubtedly an excellent opportunity to make decisions that are genuinely by current needs. This can also be done by database technology and has even been applied several times to real cases worldwide. One example is that we enter information that can only be accessed by the admin. However, it turns out that if we do not have that access, the database will immediately cancel the process. This process can be achieved by using SQL Transaction.

SQL Transactions

SQL transaction is one of the procedural languages ​​in SQL that can increase computation and reduce the memory usage when executing a query. Not only that, SQL transactions will facilitate us to meet our previous needs. At least two conditions will be achieved when running a SQL transaction. First, when the query is executed because it satisfies the condition.

Second, it will be aborted when the executed query does not meet the requirements.

Anatomy of SQL Transaction

Like PL/SQL, SQL Transaction consists of identifiers such as DECLARE, BEGIN, and END. But to note is the unique part:

1. Start Transaction => to initialize SQL Transaction
2. Commit => Processing the query
3. Rollback => Cancel query

Case study

To implement it, let's go to the banking sector. Suppose we have a cash withdrawal machine or ATM. This machine has special provisions if a customer wants to withdraw money, including:

1. The balance must be sufficient
2. The minimum total balance in the account is 50,000 IDR
3. The nominal taken is in the form of a sheet of 50.000 IDR
4. The withdrawal amount is in the range of 50,000 IDR – 1,000,000 IDR Suppose a user has a balance of 400,000 IDR, and then he plans to withdraw money 4 times with each withdrawal of 100,000 IDR. What happened to the funds in his account?

Analysis is the key!

Disclaimer! We must assume that the database that we create will only be related to ATM machines that can make money withdrawals. To simplify troubleshooting, let's create a table structure for this case.

After we realize what crucial things need to be saved, we ensure one piece of data is in the table with dummy personal data, but the remaining balance is definitely 400,000 IDR. After creating a registered user, it's time to use our knowledge to complete a SQL transaction that satisfies the previous four conditions. Let's discuss each state that our PL/SQL must-have one by one.

1. The balance must be sufficient, meaning that we create a condition if the user withdraws money with a withdrawal nominal greater than the remaining balance, meaning the transaction will be canceled.
2. The remaining balance must be available at least 50,000 IDR, meaning that we create a condition where withdrawals cannot be made if the remaining balance is only 50,000 IDR.
3. This ATM is only available in 50,000 IDR currency. Make sure each user can only make withdrawals in multiples of 50,000 IDR.
4. Do not allow users to withdraw balances below 50,000 IDR or above 1,000,000 IDR. After everything is clear, let's do the execution in managing this ATM database.

ATM, here we come!

Start by disabling auto-commit, which by default is enabled by the database we use.

First, we need to create a database and tables that fit the structure. We will use MySQL with a database connection to MariaDB.

Then let's register one user into the customer's table.

The awaited moment has arrived. Let's make a transaction that can facilitate these four conditions. We will divide it into several stages.

• First of all, let's change the delimiter of each query with a // flag (this is for execution in multiple questions in SQL transactions). Don't forget when it's activated (and the SQL transaction has been created), the delimiter mark must be returned to its original state by changing it to ;

• Next, we need to define the procedure's name that will contain our SQL Transaction. Then we also need to initialize any parameters, either input or output, when calling the procedure. Let's call this procedure withdrawal with the parameters account_number, pin, total withdrawal, and a notification containing the transaction's success.

• After that, let's define when an anomaly occurs we are using so that there is no need for

Troubleshooting when the database is not running correctly through Error Handling. In addition, several local variables are created that can be used as a calculation process in the stored procedure.

• We first ensure that the customer who will enter the system has been registered and entered the appropriate account or pin. We also define a local variable that can hold the business processes in this ATM by reducing the remaining balance available by withdrawing the balance made.

• Condition number four can be done in this way. This ensures that withdrawals are made within the defined range.

• The nominal multiple of 50,000 IDR can be found by finding the remainder of the quotient by 50,000.

• The following condition is that there must be at least 50,000 IDR in the balance.

• When all conditions are met, the remaining balance value of the user will be deducted and updated automatically.

So the whole SQL transaction procedure looks like this.

MariaDB [atm]> CREATE PROCEDURE withdrawal(
    -> IN in_account INT(64),
    -> IN in_pin INT(8),
    -> IN in_amount INT(16),
    -> OUT notif VARCHAR(255))
    -> BEGIN
    ->  DECLARE exist INT;
    ->  DECLARE balance_cust DECIMAL(16,2);
    ->  DECLARE remain_balance DECIMAL(16,2);
    ->  DECLARE EXIT HANDLER FOR SQLEXCEPTION
    ->  BEGIN
    ->          SET notif = 'System Error';
    ->          ROLLBACK;
    ->  END;
    ->  START TRANSACTION;
    ->          SELECT COUNT(*) INTO exist FROM customers WHERE account_number = in_account AND pin = in_pin;
    ->          IF exist != 1 THEN
    ->                  SET notif = 'Incorrect account number or pin entered';
    ->                  ROLLBACK;
    ->          ELSE
    ->                  SELECT remaining_balance INTO balance_cust FROM customers WHERE account_number = in_account AND pin = in_pin;
    ->                  SET remain_balance = balance_cust - in_amount;
    ->          END IF;
    ->          IF in_amount < 50000 OR in_amount > 1000000 THEN
    ->                  SET notif = 'Withdrawal range is between 50.000 IDR AND 1.000.000 IDR';
    ->                  ROLLBACK;
    ->          ELSE
    ->                  IF in_amount % 50000 != 0 THEN
    ->                          SET notif = 'The multiple of withdrawal is 50.000 IDR';
    ->                          ROLLBACK;
    ->                  ELSE
    ->                          IF remain_balance < 50000 THEN
    ->                                  SET notif = 'Minimum remaining balance is 50.000 IDR';
    ->                                  ROLLBACK;
    ->                          ELSE
    ->                                  UPDATE customers SET remaining_balance = remain_balance WHERE account_number = in_account AND pin = in_pin;
    ->                                  SET notif = 'Withdrawal of balance successfull';
    ->                                  COMMIT;
    ->                          END IF;
    ->                  END IF;
    ->          END IF;
    -> END //

It's time to validate our query by calling this procedure four times according to the case study. Each withdrawal is 100,000 IDR.

Yup, the database for the ATM machine that we made has worked! Thank you for reading!

Comments

[SUCHINTAN DAS]

Nice way to attract a lot of readers 🙂. I personally don't open posts over such topics. But let me agree this fact that you caught me off guard by using such a title for your blog.

Just a small suggestion , rather than putting a lot of texts put some images to illustrate what you are explaining. Visual learning is far better than reading, and yes that's why use of graphs to illustrate a thing is preferred by most people.

Here's a blog reference which you can use to understand what I was saying . Hope this helps ...

[geraldew]

May I suggest adding the tag "sql" for this article, as I only saw it by accident. I don't use MySQL or MariaDb but am generally interested in seeing techniques from across the SQL spectrum.

[Fyodor]

I personally like the post title very much but it might sound a bit confusing and obscure for some people navigating through their feeds here 😅