As we've seen, the SQL DML statements map to the CRUD operations. When discussing the features of the SQL transactions, we'll be looking at the sequences of the INSERT, SELECT, UPDATE, and DELETE statements.

The ACID properties are Atomic, Consistent, Isolated, and Durable. These are essential features of a transaction that consists of multiple database operations. For more information, see Chapter 11, Storing and Retrieving Objects via Shelve.

All the SQL DML statements operate within the context of an SQL transaction. The entire transaction must be committed as a whole, or rolled back as a whole. This supports the atomicity property by creating a single, atomic, indivisible change from one consistent state to the next.

SQL DDL statements (that is, CREATE, DROP) do not work within a transaction. They implicitly end any previous in-process transaction. After all, they're changing the structure of the database; they're a different kind of statement, and the transaction concept doesn't really apply. Each SQL database product varies slightly in whether or not a commit is necessary when creating tables or indices. Issuing a database.commit() can't hurt; for some database engines, it may be required.

Unless working in a special read uncommitted mode, each connection to the database sees a consistent version of the data containing only the results of the committed transactions. Uncommitted transactions are generally invisible to other database client processes, supporting the consistency property.

An SQL transaction also supports the isolation property. SQLite supports several different isolation level settings. The isolation level defines how the SQL DML statements interact among multiple, concurrent processes. This is based on how locks are used and how a process' SQL requests are delayed waiting for locks. From Python, the isolation level is set when the connection is made to the database.

Each SQL database product takes a different approach to the isolation level and locking. There's no single model.

In the case of SQLite, there are four isolation levels that define the locking and the nature of transactions. For details, see http://www.sqlite.org/isolation.html.

The following bullet list talks about these isolation levels:

• isolation_level=None: This is the default, otherwise known as the autocommit mode. In this mode, each individual SQL statement is committed to the database as it's executed. This can break the atomicity of complex transactions. For a data warehouse query application, however, it's ideal.
• isolation_level='DEFERRED': In this mode, locks are acquired late in the transaction. The BEGIN statement, for example, does not immediately acquire any locks. Other read operations (examples include the SELECT statements) will acquire shared locks. Write operations will acquire reserved locks. While this can maximize the concurrency, it can also lead to deadlocks among competing transaction processes.
• isolation_level='IMMEDIATE': In this mode, the transaction BEGIN statement acquires a lock that prevents all writes. Reads, however, will continue normally. This avoids deadlocks, and works well when transactions can be completed quickly.
• isolation_level='EXCLUSIVE': In this mode, the transaction BEGIN statement acquires a lock that prevents all access except for connections in a special read uncommitted mode. 

The durability property is guaranteed for all committed transactions. The data is written to the database storage.

The SQL rules require us to execute the BEGIN TRANSACTION and COMMIT TRANSACTION statements to bracket a sequence of steps. In the event of an error, a ROLLBACK TRANSACTION statement is required to unwind the potential changes. The Python interface simplifies this. We can execute an SQL BEGIN statement. The other statements are provided as functions of the sqlite3.Connection object; we don't execute SQL statements to end a transaction. We might write things like the following code to be explicit:

database = sqlite3.connect('p2_c11_blog.db', isolation_level='DEFERRED') 
try:
    with closing(database.cursor()) as cursor:
        cursor.execute("BEGIN")
        # cursor.execute("some statement")
        # cursor.execute("another statement")
    database.commit()
except Exception as e:
    database.rollback()

We selected an isolation level of DEFERRED when we made the database connection. This leads to a requirement that we explicitly begin and end each transaction. One typical scenario is to wrap the relevant DML in a try block and commit the transaction if things worked, or roll back the transaction in the case of a problem. We can simplify this by using the sqlite3.Connection object as a context manager as follows:

database = sqlite3.connect('p2_c11_blog.db', isolation_level='DEFERRED') 
with database: 
    database.execute("some statement") 
    database.execute("another statement") 

This is similar to the previous example. We opened the database in the same way. Rather than executing an explicit BEGIN statement, we entered a context; the context executes the BEGIN statement for us.

At the end of the with context, database.commit() will be done automatically. In the event of an exception, a database.rollback() will be done, and the exception will be raised by the with statement.

This kind of shorthand is helpful in a web server where a connection only needs to last for the duration of a single web request. When doing queries, the isolation level can be left as the default, resulting in a very quick access to data. 

In the next section, we'll design primary and foreign database keys.