Transactional Database

With the balance field, we can know how much money there is in retailer's wallet. But we also want to track the history of balance changes. It's similar to the bank account statement that lists the time and amount of all the deposit/withdraw operations as well as the balance before/after each operation.

TimeAmountBegingingEnding
00:00:001000100
00:00:01-1010090
00:00:02-209070

When operations are sorted in time, two properties must be hold:

  • for every operation, ending balance must be equal to the beginning balance plus amount(amount may be negative for withdraw operations)
  • beginning balance of each operation must be equal to the ending balance of its immediate previous operation or the initial balance if it is the first operation.

It's obvious that we need a new table to track the operation history.

CREATE TABLE `retailer_statement_tab` (
...
`time_stamp` DATETIME NOT NULL,
`retailer_id` INT(11) NOT NULL,
`amount` DECIMAL(20,2) NOT NULL,
`ending_balance` DECIMAL(20,2) NOT NULL,
...

We did not design a field for beginning balance because it can be calculated by subtracting amount from ending_balance.

Now we need to update two tables for any deposit or withdraw operation. One UPDATE statement and one INSERT statement.

mysql> UPDATE retailer_tab set balance=balance-10;
mysql> INSERT INTO retailer_statement_tab (`time_stamp`, `retailer_id`, `amount`, `ending_balance`) values(now(), 1, '10.00', '90.00');

Specially, we want to those two statements act atomically. Either all succeeded, or nothing happened. We never want to run into a scenario that the UPDATE succeeded while the INSERT failed, which will lead to a corrupted state like the following:

TimeAmountBegingingEnding
00:00:001000100
00:00:02-209070
It does not help much if we swap the order of INSERT and UPDATE. We might get the following scenario if INSERT succeeded while UPDATE failed.
TimeAmountBegingingEnding
00:00:001000100
00:00:02-10100100

What we need here is a support for multiple database operations to be executed as a whole to ensure data consistency. If any error occurs, the whole operations failed and are rolled back. Transactional Database comes to rescue and serves exactly for this purpose. MySQL provides transactional support with its InnoDB storage engine. It's easy to group a bunch of SQL statements into a transaction by wrapping them with two keywords:

mysql> BEGIN;
mysql> ... # anything that should be run together
mysql> COMMIT;

We can use the ROLLBACK keyword to rollback the transaction explicitly.

mysql> BEGIN;
mysql> ...
mysql> ROLLBACK;

The transaction can also be rolled back implicitly upon errors. If the MySQL session that initiated the transaction is aborted due to broken network connection, the transaction will be rolled back automatically.

The syntax of a transaction in Django ORM is created by a function decorator :

@transaction.atomic()
def deposit_operation(...)
...

or a handy context manager:

with transaction.atomic():
    ... # UPDATE AND INSERT OPERATIONS

Any uncaught exception thrown from inside will roll back the transaction automatically. But anything that are not related to database operations like file logging will NOT be rolled back since it is beyond the control of database. Thus it is recommended to delay non-database operations with side-effect until the transaction is committed.

To use transactions correctly and efficiently, we must understand on how they are processed when there are multiple transactions at the same time. As we will see in the next post, things got more complicated when concurrency comes into the play.