Scene 01
No transaction object
Asqueel has no transaction object and no context manager that commits for you. The database object itself carries the unit of work. The first statement on a thread opens a connection. The same statement opens a transaction on that connection. Every later statement joins that transaction. The work stays pending until you call db dot commit or db dot rollback. Commit keeps the connection open for the next unit of work. In this lesson we use the course database, already created with the migration of lesson 30.
Example & screen notes
Slide "Transactions in asqueel". Bullets:
- No transaction object
- The first statement opens the connection and the transaction
db.commit()saves,db.rollback()discards- One connection per thread and per connection name
Scene 02
The recipe of this lesson
This layer sits on top of the course recipe, as in lesson 11. It adds a second connection declaration, named audit. The audit connection reads the same DSN as the main connection. It also binds a table class to the region table. The hook refuses the code BAD with a Python error. The hook calls commit when the code is COMM. Both behaviours exist only to show the errors of this lesson.
Example & screen notes
Code block, the layer used by the lesson. The lines db.connection(name='audit', ...) and x_table_class=RegionTable are highlighted.
from genro_bag.resolvers import EnvResolver
from asqueel import AsqueelDb, SqlDatabaseConfig, SqlTable
from invoicing import Invoicing
class RegionTable(SqlTable):
def trigger_onInserting(self, record):
if record['code'] == 'BAD':
raise ValueError('refused by the hook')
if record['code'] == 'COMM':
self.db.commit()
class Lesson27(SqlDatabaseConfig):
def main(self, root):
db = root.db('course', implementation='postgres')
db.connection(name='audit', implementation='postgres',
dsn=EnvResolver('ASQUEEL_DSN', default='dbname=asqueel_course'))
db.packages().package('invc').table('region', x_table_class=RegionTable)
db = AsqueelDb(Lesson27, parents=[Invoicing])
region = db.table('invc.region')
Scene 03
Reads open a transaction too
A read is a statement like any other. Here we read the server process of the main connection. A helper function reads the state of that process from another connection. After commit, the state is idle. After a query, the state is idle in transaction. The query opened a transaction, and the transaction stays open. After rollback, the state is idle again. A long-lived process must close its read transactions too. Commit or rollback both close them.
Example & screen notes
Code block on the left, output on the right.
pid = db.execute('SELECT pg_backend_pid() AS pid').fetchone()['pid']
db.commit()
state_of(pid) # 'idle'
region.query().fetch()
state_of(pid) # 'idle in transaction'
db.rollback()
state_of(pid) # 'idle'
Caption under the code: "state_of reads pg_stat_activity on the audit connection."
Scene 04
Commit, rollback and the automatic rollback
On the left, rollback discards the insert, and commit saves it. On the right, the second insert repeats an existing key. PostgreSQL raises a unique violation. Before the error reaches your code, asqueel rolls back the connection. The pending insert of South is gone too. The connection is usable at once. The next query starts a new transaction and sees only North. This automatic rollback happens for every error raised by the driver, on execute and on fetch.
Example & screen notes
Two code blocks side by side.
Left, "Explicit completion":
region.insert({'code': 'NO', 'name': 'North'})
db.rollback()
region.query().count() # 0
region.insert({'code': 'NO', 'name': 'North'})
db.commit()
region.query().count() # 1
Right, "SQL error":
region.insert({'code': 'SO', 'name': 'South'})
region.insert({'code': 'NO', 'name': 'North again'})
# psycopg.errors.UniqueViolation
region.query(columns='$code').fetchPkeys() # ['NO']
Scene 05
A Python error during a write
A Python error inside a write is different from an SQL error. The hook raised before any SQL ran, so the database knows nothing about it. Asqueel marks the connection rollback-only. Catching the exception does not change that mark. Commit raises TransactionStateError. Every other statement on that connection raises the same error. Only db dot rollback clears the mark. The rollback also discards the insert of Centre. The rule protects you from committing a write that stopped half way.
Example & screen notes
Code block, the two refusals highlighted.
region.insert({'code': 'CE', 'name': 'Centre'})
try:
region.insert({'code': 'BAD', 'name': 'Bad'})
except ValueError:
pass
db.commit()
# TransactionStateError: Connection is rollback-only; call rollback before reuse
region.query().count()
# TransactionStateError: Connection is rollback-only; call rollback before reuse
db.rollback()
region.query().count() # 1
Scene 06
Commit and rollback belong to the caller
The hook of this table calls commit for the code COMM. Asqueel refuses it with TransactionStateError. A write in progress on the connection forbids commit and rollback on that connection. The same rule applies to closeConnection. The failed write also makes the connection rollback-only, so we roll back. Transaction boundaries belong to the code that started the work, never to a hook.
Example & screen notes
Code block, then the message.
region.insert({'code': 'COMM', 'name': 'Commit inside'})
# TransactionStateError: Cannot finish the transaction of a table write or hook in progress
db.rollback()
Bullets under the code:
- No
commit()orrollback()inside a write or a hook - No
closeConnection()inside a write or a hook
Scene 07
closeConnection and session
db dot closeConnection rolls back pending work. Then it closes every named connection of the current thread. It never commits. The database object stays usable, and the next statement opens a new connection. The function session wraps one unit of work outside a web request. On entry, it clears currentEnv and applies the keyword arguments. On exit, it calls closeConnection, also when the body raises. Work you did not commit is rolled back. One session at a time is allowed per database and per thread. A nested session raises TransactionStateError.
Example & screen notes
Two code blocks.
Top, "closeConnection":
region.insert({'code': 'WE', 'name': 'West'})
db.closeConnection()
region.query().count() # 1: West was rolled back
Bottom, "session":
from asqueel import session
with session(db, user='batch'):
dict(db.currentEnv) # {'user': 'batch'}
region.insert({'code': 'EA', 'name': 'East'})
# leaving the block: closeConnection(), East is rolled back
with session(db):
with session(db): # TransactionStateError:
... # a session is already open on this thread for this database
Scene 08
Named connections
The key connectionName of the environment selects a connection. Without it, the selected name is underscore main connection. db dot tempEnv changes the selection for the duration of the block. It does not commit, roll back or close anything. Each name has its own connection and its own transaction. The audit transaction cannot see the uncommitted insert of Islands. Commit and rollback act on the selected name only. Each name needs its own completion. The recipe declares main and audit, so an undeclared name raises ValueError. Two named connections can also wait for each other's locks, like any two database sessions.
Example & screen notes
Code block, then a diagram: one box "thread", two boxes inside it, "_main_connection" and "audit", each with its own arrow to PostgreSQL.
region.insert({'code': 'IS', 'name': 'Islands'})
with db.tempEnv(connectionName='audit'):
region.query().count() # 1: the audit transaction does not see Islands
db.rollback() # completes the audit transaction only
region.query().count() # 2
db.rollback()
with db.tempEnv(connectionName='reporting'):
region.query().count()
# ValueError: Unknown named connection: 'reporting'
Scene 09
Threads, savepoints, async
One database object serves every thread. Its connections, its environment and its write state are local to each thread. The worker thread gets its own server process. A thread cannot close the connections of another thread. Every worker releases its own, here through session. Asqueel has no savepoints: a nested tempEnv does not create one. The API is synchronous. Async tasks that share one thread are not isolated from each other.
Example & screen notes
Code block, then bullets.
def worker():
with session(db):
pids['thread'] = db.execute('SELECT pg_backend_pid() AS pid').fetchone()['pid']
pids['main'] = db.execute('SELECT pg_backend_pid() AS pid').fetchone()['pid']
thread = threading.Thread(target=worker)
thread.start(); thread.join()
pids['main'] != pids['thread'] # True
- The database object and its tables are shared between threads
- Connections, environment and transactions belong to one thread
- Each thread releases its own connections
- No savepoints
- No async API
Scene 10
Recap
Every statement joins the transaction of its thread and of its connection name, and you end it explicitly. SQL errors roll back automatically, while Python errors inside a write leave the connection rollback-only. Session and closeConnection roll back what you did not commit. The next lesson shows the callbacks that run at commit time.
Example & screen notes
Slide "Recap". Bullets:
- Every statement joins the transaction of its thread and connection name; you end it with
commit()orrollback() - SQL errors roll back automatically; Python errors in a write require
rollback() session(db)andcloseConnection()roll back what you did not commit
Next: "Lesson 28 — Deferred callbacks".