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:
@transactional
decoratorTransactions 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:
select, update
, and insert
.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.
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!
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.
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:
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.
conn.commit()
, hence not offering any option to rollback. transfer
is called, there may be special handling required. In our example, we abruptly commit previous work and then start a new transaction with the initial commit
. 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. commit
and the rollback
. 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.