SQL operations are used in lots of applications in order to supply data. A key ingredient used to grow applications to enterprise scale are transactions. They add integrity to data management by defining an atomic unit of work.
An atomic unit of work means that the whole sequence of steps when completed must appear like a single step. If there is any failure in the chain of steps, everything must rollback to the state before the transaction started. For SQL transactions, this means that the state of the database must update or rollback atomically.
This chapter will inspect the pattern of coding and using SQL transactions, and how Spring Python makes it easy to code an otherwise monotonous pattern.
In this chapter, you will learn:
- The classic pattern of SQL transactions and the issues imposed when coding transactions by hand
- That it is easy to add transactions to a banking application using Spring Python's
@transactionaldecorator - You can choose between coding transactions programmatically or by decorator
- You can choose between using and not using the IoC container
- Spring Python provides the means to non-intrusively mix in transactions to non-transactional code without editing the source
Transactions have a simple pattern of execution as shown in this block of pseudo-Python.
# Start transaction try: #******************************************* # Execute business logic that # that contains database operations. #******************************************* # Commit transaction except: # Rollback transaction
Let's look at the steps involved in defining a transaction:
- First, the transaction must be started.
- Then, a block of business code is executed, which contains several database operations, including
select, update, andinsert. - Finally, the transaction is committed. If any type of error occurs, an exception handler rolls back the transaction, undoing all the changes that were made.
The problem with writing transactions manually is similar to the problem with writing SQL operations manually. There is a lot of boilerplate code that must be written. The boilerplate has a tangling effect because it must be written before and after the business code in order for the transaction to be handled correctly. This pattern must then be copied into every location where some business logic needs to be wrapped in a transaction.
This violates the DRY (Don't Repeat Yourself) principle. It also tangles our code with not only business logic, but transactional logic as well. And it breaks the Single Responsibility Principle (SRP) by having our code depend on both business and integrity requirements.
Violating these principles puts us at risk. We might introduce bugs when changes to the transactional logic are needed, but we fail to to repeat them in all the right places.
This is a crosscutting problem because it extends beyond our class hierarchy. Steps contained within the unit of work can quickly cut across several classes. This is a perfect use case for aspect oriented programming that was discussed earlier in this book. As we delve into this in more detail in this chapter, we will see how to solve it with ease using Spring Python.
Until now, the various chapters have shown sample code that involved designing a wiki engine. For this chapter, a banking application will be used as the basis for code examples. Banks must be able to move money around and not lose a single penny, maintaining a high integrity. This provides a simple problem space to demonstrate writing transactional code.
- First, let's write a simple transfer operation that transfers a certain amount of money from one account to another without any concept of a transaction.
def transfer(transfer_amt, source_act, target_act): cursor = conn.cursor() cursor.execute(""" update ACCOUNT set BALANCE = BALANCE - %s where ACCOUNT_NUM = %s""", (transfer_amt, source_act)) cursor.execute(""" update ACCOUNT set BALANCE = BALANCE + %s where ACCOUNT_NUM = %s""", (transfer_amt, target_act)) cursor.close()Let's assume we are transferring $10,000 from the SAVINGS account to the CHECKING account. In our business logic, we accomplish this by first withdrawing $10,000 from the SAVINGS account and then depositing $10,000 into the CHECKING account.
Imagine if there was some system error that happened after we had withdrawn from the SAVINGS account that caused the system to restart. Having never deposited the transfer amount into the CHECKING account, the bank would have leaked $10,000 to nowhere. How long would you keep your money in a bank like that? Probably not for long!
- Let's combine this operation with the transaction pattern we looked at earlier, to give our bank some integrity.
def transfer(transfer_amt, source_act, target_act): conn.commit() try: cursor = conn.cursor() cursor.execute(""" update ACCOUNT set BALANCE = BALANCE - %s where ACCOUNT_NUM = %s""", (transfer_amt, source_act)) cursor.execute(""" update ACCOUNT set BALANCE = BALANCE + %s where ACCOUNT_NUM = %s""", (transfer_amt, target_act)) cursor.close() conn.commit() except: conn.rollback()
In our updated example, we now start with conn.commit() in order to start a new transaction.
Note
If you use a Python DB API compatible database module, then transactions are available when you connect to the database. connection.commit() and connection.rollback() finishes an existing transaction and implicitly starts a new one (http://www.pubbs.net/python/200901/18953/). This is the reason this block of code starts with a commit statement.
We enter the try/except block and start executing the same business code that we wrote earlier. In the end, we execute conn.commit(), to commit our results to the database.
In the event of some system error, the commit would never be executed. This means the withdrawal from the SAVINGS account would not be written into the database. If this was really a hard system failure, relational databases would revert to the state before the transaction started.
For softer failures where some sort of application level exception raised would result in conn.rollback() being called, reverting all changes back to when the transaction started.
This solution solves the problem such that our bank doesn't leak money due to system faults and errors. But in order to re-use this transactional pattern in other parts of our banking application, we must repeat the coding pattern over and over, increasing the risk of bugs.
Transactions carry four distinct properties:
- Atomicity—We need to guarantee that all steps complete, or no steps complete
- Consistent—This is concept of ensuring our data maintains a consistent state
- Isolated—When we withdraw money from the bank, it would be bad if our business partner was withdrawing at the same time, and we ended up with a negative balance
- Durable—We expect completed transactions to survive hardware failures
Consistency and durability tend to be related to resources, such as the database and the server it runs on, and typically doesn't affect the way we write code. Atomicity and Isolation however are commonly handled by developer code.
To guarantee atomicity, we must start a transaction, and then commit or rollback. Throughout this chapter we will explore how Spring Python makes it easy to define atomic transactions.
Python's DB API specification (http://www.python.org/dev/peps/pep-0249/) doesn't define Isolation levels. Instead, each vendor implements this differently. To alter Isolation levels, we must investigate the database engine we are using, and then access either the connection or the cursor provided with our factory in order to alter this setting from vendor defaults.
Transactions can either be supported locally or be distributed across multiple databases. Local transactions typically involved a single database schema. In order to extend to other systems, the Python specification utilizes a 'two-phase commit' mechanism.
Another factor in transaction definition is propagation. When we have multiple operations that are defining a transaction, it is important to combine them together in the right fashion. This involves dealing with circumstances where a new transaction is encountered while one is already in progress. We will look at this in more detail later in this chapter.
The transactional pattern shown earlier above is very simplistic and incomplete. There are many issues that can occur which requires even more detail.
- The most important issue is whether or not the underlying database engine supports transactions, and if it's been properly configured to avoid auto commits. If this isn't set up appropriately, the code won't function as expected. Auto commits work by committing every SQL statement as executed, instead of waiting for
conn.commit(), hence not offering any option to rollback. - If a transaction was already in progress when the
transferis called, there may be special handling required. In our example, we abruptly commit previous work and then start a new transaction with the initialcommit. With a transaction in progress, this may not be the right step. It's hard to tell considering we don't have any surrounding business context. - Exception handling isn't the only type of rollback scenario to handle. While our transfer doesn't have any alternate return paths, more complex business logic can easily code guard clauses and other return statements that bypass both the
commitand therollback. Leaving the transaction hanging and not committed to the database would be very sloppy and risky.
The result is that the pattern shown above and utilized for our simple example isn't comprehensive enough to handle the simplest risks. A more complex pattern has to be coded. Given that it must be repeated for every transactional point in our application, it makes our integrity problem even harder to solve.
There is a side effect of efficiency when performing transactions. Because the same connection is used to conduct the transaction, the cost of opening and closing connections is avoided.