Scene 01
Why lock on read
A change often has three steps. Read the row, compute the new values, write them. Two sessions can run these steps at the same time. Both read the same old value, and the second write overwrites the first. A lock on read prevents this. The first session locks the row when it reads it. The second session waits, or fails, until the first transaction ends.
Example & screen notes
Slide title "Read, change, write". Diagram: two columns "session 1" and "session 2". Each column has three boxes from top to bottom: "read total = 50", "compute 60", "write 60". An arrow marks that both sessions read 50 before either writes.
Scene 02
The option
for_update is a query option. It accepts True and two strings: NOWAIT and SKIP LOCKED, in upper case. Any other value raises ValueError. That includes the integer one and the lower-case string nowait. On PostgreSQL the compiler appends FOR UPDATE OF t0. t0 is the alias of the root table. Only the rows of the root table are locked. A joined table, such as the customer reached through at customer_id, is read but not locked. The lock lasts until commit or rollback.
Example & screen notes
Slide title "for_update". Code:
invoice.query('$inv_number, $total', '$inv_number = :n', n='A1', for_update=True).sqltext
SELECT "t0"."inv_number" AS "inv_number",
"t0"."total" AS "total"
FROM "invc"."invc_invoice" AS "t0"
WHERE ("t0"."inv_number" = %(n)s) AND ("t0"."__del_ts" IS NULL)
FOR UPDATE OF t0
Bullets:
- for_update=True, 'NOWAIT' or 'SKIP LOCKED'
- any other value raises ValueError, including 1 and 'nowait'
Scene 03
NOWAIT and SKIP LOCKED
This example uses a second database object, built from the same recipe, as a second session. The first session locks invoice A1. With for_update equal True, the second session would wait for the lock. With NOWAIT it fails at once. PostgreSQL raises LockNotAvailable, and asqueel rolls back the transaction of the second session, so the session stays usable. With SKIP LOCKED the second session reads the other rows and skips A1. A read without for_update does not wait and sees A1. When the first session commits or rolls back, the lock is released.
Example & screen notes
Slide title "Lock modes on PostgreSQL". Code:
other = AsqueelDb(Invoicing) # a second session
other_invoice = other.table('invc.invoice')
invoice.query('$inv_number', '$inv_number = :n', n='A1', for_update=True).fetchRecord()
other_invoice.query('$inv_number', '$inv_number = :n', n='A1',
for_update='NOWAIT').fetch()
# psycopg.errors.LockNotAvailable: could not obtain lock on row in relation "invc_invoice"
[row['inv_number'] for row in other_invoice.query(
'$inv_number', for_update='SKIP LOCKED', order_by='$inv_number').fetch()]
# ['A2', 'B1', 'B2']
[row['inv_number'] for row in other_invoice.query(
'$inv_number', order_by='$inv_number').fetch()]
# ['A1', 'A2', 'B1', 'B2'] a read without lock does not wait
Scene 04
Columns and refusals
A locked read is meant to be written back. For this reason star with for_update includes the X columns too, as if bagFields were True. A locked row cannot be a group or a distinct row. query raises UnsupportedFeatureError when for_update is combined with distinct or group_by. A sum without group_by is not refused by asqueel, but PostgreSQL refuses it at execution with FeatureNotSupported.
Example & screen notes
Slide title "What for_update changes". Bullets:
- * with for_update includes the X columns, as bagFields=True
- for_update with distinct or group_by: UnsupportedFeatureError, raised by query()
- an aggregate projection without group_by: PostgreSQL raises FeatureNotSupported
Code:
invoice.query('$customer_id', distinct=True, for_update=True)
# UnsupportedFeatureError: for_update is incompatible with distinct or group_by
invoice.query('SUM($total) AS total', for_update=True).fetch()
# psycopg.errors.FeatureNotSupported: FOR UPDATE is not allowed with aggregate functions
Scene 05
SQLite
SQLite has no row locks and no FOR UPDATE clause. The SQLite adapter writes the SELECT without it and takes the write lock of the database instead. If no transaction is open, it starts one with BEGIN IMMEDIATE. If a transaction is already open, it executes an UPDATE that changes no row, on the primary-key column of the root table. Both statements reserve the database for writing, so another connection cannot write until this transaction ends. The string modes have no equivalent. On SQLite, NOWAIT and SKIP LOCKED raise UnsupportedFeatureError.
Example & screen notes
Slide title "for_update on SQLite". Bullets:
- the SQL has no FOR UPDATE clause
- no transaction open: BEGIN IMMEDIATE before the SELECT
- transaction open: UPDATE <table> SET <pkey>=<pkey> WHERE 0 before the SELECT
- 'NOWAIT' and 'SKIP LOCKED': UnsupportedFeatureError
Scene 06
The usual pattern
This is the usual pattern. Read the old record with for_update, here with star, so that every physical column is present. Copy it into a dictionary and change the copy. Pass both to update: the new record and the old record. update takes the key of the WHERE from the old record and does not read the row again. Then commit, which releases the lock. On an error, roll back, which also releases the lock. Lesson twenty explains update and the old_record argument.
Example & screen notes
Slide title "Read with lock, then update". Code:
try:
old = invoice.query('*', '$inv_number = :n', n='A1',
for_update=True).fetchRecord()
record = dict(old)
record['total'] = old['total'] + 10
invoice.update(record, old_record=dict(old))
db.commit()
except Exception:
db.rollback()
raise
Scene 07
Recap
To recap. for_update locks the rows of the root table until the transaction ends. On SQLite it takes the write lock of the database, and the string modes are refused. The usual pattern is a locked read, then update with the old record, then commit. The next lesson starts the section on writing.
Example & screen notes
Slide title "Recap". Bullets:
- for_update=True|'NOWAIT'|'SKIP LOCKED' locks the root-table rows until commit or rollback
- SQLite takes the database write lock; string modes are refused there
- read with for_update, then update(record, old_record=...), then commit
Footer: "Next: lesson 20 — insert, update, delete".