Title: Use transactions to prevent data errors in MariaDB and Python

[A transaction ensures that a group of operations are either all performed or all not performed]

Suppose you have an Accounts table defined like this:

CREATE OR REPLACE TABLE Accounts ( Name VARCHAR(50) NOT NULL, Balance DECIMAL(10,2) NOT NULL

Now suppose Alice wants to transfer $100 from her account to Bobby's account (to pay for her share of an inflatable pool flamingo). One way to do that is to (1) subtract $100 from Alice's balance and then (2) add $100 to Bobby's balance. But what happens if there's an error after step 1 and before step 2? For example, the database server might crash, the user program might crash, or the network connection to the server might crash. IN any of those cases (and probably others), $100 has mysteriously disappeared from the system.

You could reverse the operations and (1) add $100 to Bobby before (2) subtracting $100 from Alice, but that only changes the problem and doesn't solve it. Now if something goes wrong, $100 is mysteriously generated out of thin air. (Bobby probably doesn't mind but you surely do if it's you running the accounts.)

Many databases have a way to handle these scenarios: transactions. A transaction bundles two or more database operations into a package that either succeeds or fails as a single unit. In this example, either the money is correctly transferred from Alice to Bobby or it is not removed from Alice and not given to Bobby.

Here's how to use transactions in MariaDB.

Autocommit

If you set the database connection object's autocommit property to True, then MariaDB automatically creates a transaction for every command you issue. Each time you execute a command, the database commits it so it cannot undo that command if there's a later error.

If there is a problem in the middle of the $100 transfer, the first operation goes through and the second does not.

The following code simulates this scenario.

# Trial with autocommit turned on. try: # Recreate the tables. drop_tables(cur) create_tables(cur) insert_records(cur) conn.autocommit = True print('Autocommit ON') show_records(cur) # Subtract money from Alice. cur.execute(''' UPDATE Accounts SET Balance = Balance - 100 WHERE Name = "Alice" ''') # Raise an error. raise Exception('Something bad happened!') # Give money to Bobby. cur.execute(''' UPDATE Accounts SET Balance = Balance + 100 WHERE Name = "Bobby" ''') except Exception as e: print(f'Error: {e}') finally: # Display the result. show_records(cur) print()

This code recreates a database where Alice and Bobby both have $1000, turns on autocommit, and calls show_records to show the database's current values. Here's the output at this point.

Autocommit ON Alice 1000.00 Bobby 1000.00

Next, the code subtracts $100 from Alice, but before it can give $100 to Bobby, the code raises an exception. The except section displays an error message and the finally section calls show_records to show the database's new state. Here's the result.

Error: Something bad happened! Alice 900.00 Bobby 1000.00

You can see that Alice lost $100 but Bobby did not gain $100 so $100 disappeared into the aether.

Transaction With an Error

The following code performs the same transfer but this time inside an explicit transaction.

# Trial with explicit transactions and an error. try: # Recreate the tables. drop_tables(cur) create_tables(cur) insert_records(cur) conn.autocommit = False print('Autocommit OFF (with error)') show_records(cur) # Subtract money from Alice. cur.execute(''' UPDATE Accounts SET Balance = Balance - 100 WHERE Name = "Alice" ''') # Raise an error. raise Exception('Something bad happened!') # Give money to Bobby. cur.execute(''' UPDATE Accounts SET Balance = Balance + 100 WHERE Name = "Bobby" ''') except Exception as e: # Rolback the transaction. conn.rollback() print(f'Error: {e}') print('ROLLBACK') else: # Commit the transaction. conn.commit() print('COMMIT') finally: # Display the result. show_records(cur) print()

The code recreates the tables and sets the database connections autocommit property to False. Like the previous code, it subtracts $100 from Alice and then raises an exception.

The except block calls the connection's rollback method to cancel the transaction and displays an error message. The else section isn't used because we know an error occurred, but if there was no error that section would commit the transaction. The finally section displays the database records.

Here's the output displayed by this code.

Autocommit OFF (with error) Alice 1000.00 Bobby 1000.00 Error: Something bad happened! ROLLBACK Alice 1000.00 Bobby 1000.00

This time the transaction was canceled so the money is not removed from Alice's account.

Transaction Without an Error

The program's final trial uses the following code which uses a transaction and no error occurs.

# Trial with explicit transactions and no error. try: # Recreate the tables. drop_tables(cur) create_tables(cur) insert_records(cur) conn.autocommit = False print('Autocommit OFF (without errors)') show_records(cur) # Subtract money from Alice. cur.execute(''' UPDATE Accounts SET Balance = Balance - 100 WHERE Name = "Alice" ''') # Raise an error. #raise Exception('Something bad happened!') # Give money to Bobby. cur.execute(''' UPDATE Accounts SET Balance = Balance + 100 WHERE Name = "Bobby" ''') except Exception as e: # Rolback the transaction. conn.rollback() print('ROLLBACK') print(f'Error: {e}') else: # Commit the transaction. print('COMMIT') conn.commit() finally: # Display the result. show_records(cur) print()

This trial is the same as the previous one except there's no error. This time the else section executes and the program calls the database connection's commit method to finalize the transaction. Here's the output.

Autocommit OFF (without errors) Alice 1000.00 Bobby 1000.00 COMMIT Alice 900.00 Bobby 1100.00

This time $100 was removed from Alice's account and $100 was added to Bobby's.

Conclusion

Transactions let you group database commands that you do not want to execute partly. They either all happen or none of them happen. None of them happening is logically equivalent to the database crashing before you started the group. For example, it's as if the system went down before you started the transfer from Alice to Bobby.

Transactions only work on the normal CRUD (Create, Read, Update, Delete) operations: INSERT, SELECT, UPDATE, and DELETE. They do not work on DDL (Data Definition Language) commands like CREATE TABLE, CREATE INDEX, or DROP TABLE. That makes sense because those commands could require the database to store ginormous amounts of data. Some DDL commands and other administrative commands may also end open transactions, so just finish the transactions before you start messing with the database's structure.

Also note that large transactions can impact the database's performance so try to keep transactions small. Don't start a transaction in the morning and commit it at the end of the day.

With those few caveats, transactions are very useful when you really need to execute commands as a group.

Download the example to experiment with it and to see additional details.

For a lot more information about database design and implementation, see my book Beginning Database Design Solutions, Second Edition.