Soft Introduction to SQL Transactions

In this article we will tackle the basics of SQL Transactions.

🤔 First of all, what is transaction processing?

By ensuring that batches of SQL operations run entirely or not at all, transaction processing is used to preserve database integrity.

Before we continue, let’s imagine that we have an E-commerce system that have 2 tables for the orders (Orders, OrderItems) and a table for the customers.

Customers Table

Orders Table

OrderItems Table

An excellent illustration of this are the Orders tables. Orders are kept in two tables: Orders keeps track of the actual orders, while OrderItems keeps track of the specific items ordered. Using unique IDs referred to as primary keys, these two tables are connected to one another. These tables are linked to others that contain data on customers and products.

The following steps are involved in adding an order to the system:

1. Verify the database to see if the customer is already there. If not, add the person.
2. Obtain the ID of the customer.
3. A row should be added to the Orders table linking it to the customer ID.
4. Obtain the newly assigned order ID from the Orders table.
5. For each item ordered, add a row to the OrderItems table and link it to the Orders table using the retrieved ID (and with the Products table by product ID).

🥸 What is Transaction Processing

Transaction processing is a method for managing sets of SQL operations that must be carried out in groups, making sure that databases never store the outcomes of incomplete operations. With transaction processing, you can make sure that sets of operations don’t stop processing in the middle of the process; instead, they either finish processing completely or not at all (unless explicitly instructed otherwise). The entire set of statements is committed (written) to the database tables if there are no errors. A rollback (undo) can be performed to return the database to a known and secure state if an error does occur.

So basically:

1. Transaction: A block of SQL statements.
2. Rollback: The process of undoing specified SQL statements.
3. Commit: Writing unsaved SQL statements to the database tables.
4. Save point: temporary placeholder in a transaction set to which you can issue a rollback (as opposed to rolling back an entire transaction).

Which Statements Can You Roll Back?

The management of INSERT, UPDATE, and DELETE statements uses transaction processing. Statements made using SELECT cannot be reversed. (Doing so would serve little purpose anyway.) Operations like CREATE and DROP cannot be undone. These statements can be used in a transaction block, but they won’t be reversed if you do a rollback.

So, using the same example, the procedure would go as follows:

1. Verify whether the customer is already in the database and add them if not.
2. Pass on the customer data.
3. Obtain the ID of the customer.
4. To the Orders table, add a row.
5. Roll back if there is a problem adding the row to Orders.
6. Obtain the newly assigned order ID from the Orders table.
7. For every ordered item, add a row to the OrderItems table.
8. Roll back all of the OrderItems rows that were added and the Orders row if a failure occurs while adding rows to OrderItems.

🥳 Properties of Transactions

The following four common properties of transactions are referred to as ACID and are as follows:

• Atomicity − makes sure that every operation inside the work unit is successful; if not, the transaction is halted at the point of failure, and earlier operations are undone.
• Consistency − ensures that after a successful committed transaction, the database changes states appropriately.
• Isolation − allows for the independent and transparent operation of transactions.
• Durability − ensures that, in the event of a system failure, the outcome or impact of a committed transaction remains.

⚠️ Controlling Transactions

Note: The key to managing transactions involves breaking your SQL statements into logical chunks and explicitly stating when data should be rolled back and when it should not.

Note: Some DBMSs require that you explicitly mark the start and end of transaction blocks.

SQL Server

Postgres

In this case, all SQL that is executed in between the BEGIN TRANSACTION and COMMIT TRANSACTION statements must be completed, or it cannot be used.
Variations of the above are used by other DBMSs. You’ll observe that the majority of implementations lack a clear end of transaction. Instead, the transaction continues until something ends it, typically a ROLLBACK to reverse changes or a COMMIT to save changes.

👾 Using ROLLBACK

The SQL ROLLBACK command is used to roll back (undo) SQL statements, as seen in this next statement:

In this illustration, a DELETE operation is carried out, and it is then reversed with a ROLLBACK statement. Even though it’s not the best illustration, it does show that DELETE operations — unlike INSERT and UPDATE operations — are never final within a transaction block.

🫡 Using COMMIT

Usually, SQL statements are executed and written directly to the database tables. This is known as an implicit commit — the commit (write or save) operation happens automatically.

Within a transaction block, however, commits might not occur implicitly. This, too, is DBMS specific. Some DBMSs treat a transaction end as an implicit commit; others do not.

To force an explicit commit, you use the COMMIT statement. The following is a SQL Server example:

In the SQL Server illustration, order number 12345 is completely removed from the database. A transaction block is used to make sure that the order is not partially deleted because this involves updating two database tables, Orders and OrderItems. If there were no errors, the final COMMIT statement only writes the change. The DELETE would not be committed if the first DELETE succeeded but the second one failed.

✅ Using Save points

Simple ROLLBACK and COMMIT statements enable you to write or undo an entire transaction. Although this approach works for simple transactions, more complex transactions might require partial commits or rollbacks.

For example, the process of adding an order described previously is a single transaction. If an error occurs, you only want to roll back to the point before the Orders row was added. You do not want to roll back the addition to the Customerstable (if there was one).

To support the rollback of partial transactions, you must be able to put placeholders at strategic locations in the transaction block. Then, if a rollback is required, you can roll back to one of the placeholders.

In SQL, these placeholders are called save points. To create one in MariaDB, MySQL, and Oracle, you use the SAVEPOINTstatement, as follows:

Each save point takes a unique name that identifies it so that, when you roll back, the DBMS knows where you are rolling back to. To roll back to this save point, do the following

🚧 Full Example:

SQL Server Transaction

Here, a transaction block encloses four INSERT statements. After the first INSERT, a save point is established so that, should any subsequent INSERT operations fail, the transaction is only rolled back to that point. If an operation failed, it can be checked by looking at the @@ERROR variable in SQL Server. (Other DBMSs return this data using various functions or variables.) An error occurred, and the transaction will roll back to the save point if @@ERROR returns a value other than 0. A COMMIT will be sent to save the data if the entire transaction is successful.

You can find a great example for Postgres here https://www.tutorialspoint.com/postgresql/postgresql_transactions.htm