« Writing High-Efficiency Large Python Systems--Lesson #2: Use nothing but local syslogSpecifically designed to be readable »

Writing High-Efficiency Large Python Systems--Lesson #1: Transactions in tests


Permalink 05:02:59 pm, by fumanchu Email , 189 words   English (US)
Categories: WHELPS

Writing High-Efficiency Large Python Systems--Lesson #1: Transactions in tests

Don't write your test suite to create and destroy databases for each run. Instead, make each test method start a transaction and roll it back. We just made that move at work on a DAL project, and the test suite went from 500+ seconds to run the whole thing down to around 100. It also allowed us to remove a lot of "undo" code in the tests.

This means ensuring your test helpers always connect to their databases on the same connection (transactions are connection-specific). If you're using a connection pool where leased conns are bound to each thread, this means rewriting tests that start new threads (or leaving them "the old way"; that is, create/drop). It also means that, rather than running slightly different .sql files per test or module, you instead have a base of data and allow each test to add other data as needed. If your rollbacks work, these can't pollute other tests.

Obviously, this is much harder if you're doing integration testing of sharded systems and the like. But for application logic, it'll save you a lot of headache to do this from the start.


Comment from: Leon [Visitor]

This could be problematic if your tests perform database transactions themselves, or am I missing something basic?

07/04/08 @ 06:41
Comment from: fumanchu [Member] Email


Not necessarily. Many databases provide the ability to nest transactions. PostgreSQL mimics nesting with SAVEPOINTs. See http://www.postgresql.org/docs/8.2/static/sql-savepoint.html

07/04/08 @ 12:23
Comment from: Titus Brown [Visitor] Email · http://ivory.idyll.org/blog/

Rather than dropping/creating databases or tables, I have taken to using truncate myself. I like to test clean databases with small fixtures, and truncate is much faster than most alternatives.

I will take a look at nested transactions, though. Good tip.

07/05/08 @ 12:06
Comment from: Ira Pfeifer [Visitor] · http://nyc-dba.blogspot.com

This depends highly on what DBMS you're using and how it's configured - in SQL Server, for example, a rollback of a transaction requires the transaction log to be read out-of-order, which can be slow. Truncating a table or dropping a database is usually much quicker, as those operations are not logged.

Also, open transactions can block other operations, including shared resources such as tempdb, so that should be kept in mind as well if testing on a shared system.

07/11/08 @ 09:59
Comment from: fumanchu [Member] Email


Very true. I've found Postgres to be the slowest at creating new empty databases, in fact, and this post is somewhat of a reaction to that.

postgres: 4.5 - 6.5 seconds
mysql: 0.00 seconds
sqlserver: 0.68 seconds
sqlite (ram or file): 0.00 seconds
firebird: 0.33 seconds
msaccess: 0.60 seconds

07/11/08 @ 12:07

Leave a comment

Your email address will not be revealed on this site.

Your URL will be displayed.

Please enter the phrase "I am a real human." in the textbox above.
(Line breaks become <br />)
(Name, email & website)
(Allow users to contact you through a message form (your email will not be revealed.)
September 2017
Sun Mon Tue Wed Thu Fri Sat
 << <   > >>
          1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30


The requested Blog doesn't exist any more!

XML Feeds

open source blog