12.4. SQLObject and Transactions
A DB transaction is a series of basic operations that must all succeed or fail to preserve the integrity of the DB. The best example is moving an entity from one table to another. It requires deleting the object from one table and inserting it into the other table. If one of these operations fails, you end up with no object or with two objects. Most databases support transactions and provide a way to start transactions and roll back or commit a transaction.
12.4.1. Transactions and Connections
SQLObject relies on the underlying DB for transaction processing and provides a simple application programming interface (API) for transaction management. Before diving into the murky waters of the implementation, let's see transactions in action.
The background story is that you want to buy some groceries in the store. Your database contains two tables: Store and Basket. The rows in both tables contain the name of the item and its quantity:
groceries = ['Apple', 'Bread', 'Milk', 'Enchilada'] class Store(SQLObject): class sqlmeta: cacheValues = False item = EnumCol(enumValues=groceries, unique=True) quantity = IntCol() class Basket(SQLObject): class sqlmeta: cacheValues = False item = EnumCol(enumValues=groceries, unique=True) quantity = IntCol()
SQLObject transactions are objects that wrap a DB connection and can be used as a connection. To enable transaction processing, you must assign a transaction object to the connection attribute of the relevant tables. As you recall, there are many ways to set the connection attribute. Here is one way to do it:
# create a SQLite connection db_filename = 'test.db' db_filename = os.path.abspath('test.db').replace(':\\', '|\\') db_filename = db_filename connection_string = 'sqlite:/' + db_filename connection = connectionForURI(connection_string) # create a transaction object that wraps the connection trans = connection.transaction() # create the table and set their connection attribute for t in [Store, Basket]: t.dropTable(ifExists=True) t.createTable() t._connection = trans
Buying some units of an item means decrementing the quantity of this item in the Store and adding it to the Basket with the purchase quantity. Interestingly, the item column in both tables is unique. The following function performs the buy business transaction:
def buy(item_name, quantity): print 'buy %d units of %s' % (quantity, item_name) try: product = Store.selectBy(item=item_name) product.quantity -= quantity Basket(item=item_name, quantity=quantity) trans.commit() except Exception, e: print '[EXCEPTION]', e trans.rollback() trans.begin()
The function accepts as input an item name and a purchase quantity. It tries to get a product with the name from the store and decrement its quantity. Then, it creates a new object in the basket with the input name and purchase quantity. If all is well, the transaction is committed and persists to the DB. If something goes wrong, it catches the exception, rolls back the transaction, and starts a new transaction by calling TRans.begin(). The reason it's necessary to call begin() after rollback is that the transaction enters an "obsolete" state, and further actions will fail unless begin() is called and the transaction is reset.
You can purchase some groceries now by calling buy() and checking the status of the store and your basket. The following code calls buy() four times. It tries to buy two units of milk twice (the second one should fail), three units of bread, and two units of a nonexisting product (should fail, too). After the shopping frenzy subsides, it prints the status of the store and the basket:
buy('Milk', 2) buy ('Milk', 2) buy('Bread', 3) buy ('No such product', 2) def printTitle(title): print '\n%s\n' % title + len(title) * '-' printTitle('Store') for x in Store.select(): print x printTitle('Basket') for x in Basket.select(): print x
buy 2 units of Milk buy 2 units of Milk [EXCEPTION] column item is not unique buy 3 units of Bread buy 2 units of No such product [EXCEPTION] list index out of range Store ----- <Store 1 item=u'Apple' quantity=4> <Store 2 item=u'Bread' quantity=1> <Store 3 item=u'Milk' quantity=2> <Store 4 item=u'Enchilada' quantity=4> Basket ------ <Basket 1 item=u'Milk' quantity=2> <Basket 2 item=u'Bread' quantity=3>
The failed transactions didn't hurt the data integrity of the DB. Without a transaction, the second milk purchase would decrement the quantity in the store to 0, but would fail to insert into the basket, and you would have lost two milk bottles. If you think of your code as a closed system, this might be considered a violation of the second law of thermodynamics.
12.4.2. sqlhub and Transactions
Remember sqlhub? It's the object that every model class uses as its connection if an explicit connection wasn't set directly in the class or using its module's __connection__ variable. Well, sqlhub can do transactions, too. The benefit is that if you have multiple classes that need to participate in a transaction, you can set it one time in sqlhub. The downside is that you have to manage it properly to make sure you don't keep transactions around too long. sqlhub has an attribute called threadConnection that you should set to your transaction. All the model classes will use this transaction when they access the DB from the current thread. The threadConnection is stored in the thread local storage, so each thread has its own. This guarantees that separate threads will not step on each other's toes and corrupt the transactions.
TurboGears adds a feature to SQLObject called the AutoConnectHub, which gets the database connection URI from your dev.cfg or prod.cfg file. But more important in this context, it wraps every CherryPy request in a transaction. So, if your controller methods comprise atomic units, all this transaction stuff is taken care of for you in the background. You can also append :notrans to your URI, to turn this feature off, and handle transactions on your own.
The following code demonstrates concurrent access to the DB by multiple threads. It builds on the store and basket classes: but instead of calling buy() from the main thread, it creates three buyer threads that take a short random nap and then call buy() to try to buy milk. The main thread starts the buyer threads and waits for them to complete:
import time import threading import random class Buyer(threading.Thread): def __init__(self, name): threading.Thread.__init__(self) self.name = name sqlhub.threadConnection = sqlhub.getConnection().transaction() def run(self): self.nap() buy('Milk', 1) def nap(self): nap = random.randint(1,4) print self.name, 'is taking a nap for %d seconds' % nap for i in range(0, nap): print self.name, 'ZZZ...' time.sleep(1) buyers = [Buyer('Buyer #%d' % i) for i in range (1,4)] for b in buyers: b.start() for b in buyers: b.join()
The important line is this:
sqlhub.threadConnection = sqlhub.getConnection().transaction()
You must set the sqlhub.threadConnection to a new transaction so that each thread will have its own connection.
12.4.3. Encapsulating Transactions Using a Wrapper Function
Writing code like the buy function is tedious, error prone, and mixes domain logic with the cross-cutting concern of transaction management. It would be much nicer to separate them. Ian Bicking, SQLObject's creator, had the same idea, and he suggested the following function as a solution:
def do_in_transaction(func, *args, **kw): old_conn = sqlhub.getConnection() conn = old_conn.transaction() sqlhub.threadConnection = conn try: try: value = func(*args, **kw) except: conn.rollback() raise else: conn.commit() return value finally: sqlhub.threadConnection = old_conn
You will call do_in_transaction and pass your function object and its arguments. (Functions are first-order citizens in Python.) Here is what it looks like (only the parts of the code that have changed):
for t in [Store, Basket]: t.dropTable(ifExists=True) t.createTable() #t._connection = trans def buy(item_name, quantity): print 'buy %d units of %s' % (quantity, item_name) product = Store.selectBy(item=item_name) product.quantity -= quantity Basket(item=item_name, quantity=quantity) # call buy() Four times with do_in_transaction() for x in [('Milk', 2), ('Milk', 2,), ('Bread', 3), ('No such product', 2)]: try: do_in_transaction(buy, x, x) except Exception, e: print '[EXCEPTION]', e
What's different? There is no need to assign a special transaction to the connection of each class or to sqlhub. The buy method is much simpler, and this is the real win if you have many similar operations. The code to invoke the buy() method is pretty ugly. You must pass your real function todo _in_transaction() with all its arguments.
It's not very readable and definitely looks alien.
12.4.4. Encapsulating Transactions Using a Decorator
Lucky for us, there is a solution. Instead of performing your domain actions through a wrapper function such as do_in_transaction, you can use a decorator instead. The syntax is sweet, and your intention is clear. The only downside is that decorators appeared in Python 2.4. The transaction decorator is similar to do_in_transaction; it sets the sqlhub.threadConnection attribute, intercepts exceptions, calls your function, and commits, or rolls back, if an exception occured. Finally, it restores the original sqlhub connection:
def transaction(): def decorate(func): def decorated(*args, **kwargs): exception_raised = False conn = sqlhub.getConnection() t = sqlhub.threadConnection = conn.transaction() #t.debug = True t.autoCommit = False try: try: return func(*args, **kwargs) except: exception_raised = True raise finally: if exception_raised: t.rollback() else: t.commit() t._makeObsolete() sqlhub.threadConnection = conn return decorated return decorate for x in [('Milk', 2), ('Milk', 2,), ('Bread', 3), ('No such product', 2) ]: try: buy(x, x) except Exception, e: print '[EXCEPTION]', e
As you can see, SQLObject tries to stay out of your way as much as possible when it comes to DB transactions. It provides a basic API that goes pretty much straight to the underlying DB implementation. You must understand the behavior of your DB to work effectively with transactions. Some DBs impose artificial constraints (cough, SQlite, cough), some DBs support nested transactions, and so forth.