Default behavior

In practice you never write lock/unlock. Running SELECT, INSERT, UPDATE, or DELETE makes the DBMS acquire the appropriate locks in the appropriate modes automatically 58:35. But sometimes you want to override what the system would pick, giving it a hint about how you plan to use the data later 58:57, 59:20. Postgres also offers advisory locks on named objects, covered next class 59:20.

SELECT ... FOR UPDATE

  • Problem: the read-modify-write pattern (the bank transfer). A plain SELECT takes a shared lock, so the later UPDATE forces a second trip into the lock manager to upgrade to exclusive 59:40, 1:00:16.
  • Fix: append FOR UPDATE. The query returns the same result as a normal SELECT, but locks everything it reads in exclusive mode up front 1:00:16, 1:00:46.
  • Postgres documents several variations, including SELECT ... FOR SHARE, which is useful at lower isolation levels to force transactions to hold locks longer than they otherwise would 1:00:46.
  • FOR UPDATE is very common and supported by most systems 1:00:46, 1:01:22.

SELECT ... FOR UPDATE SKIP LOCKED

  • Instead of blocking on locked rows, the scan skips anything already locked by another transaction 1:01:22, 1:01:54.
  • Killer use case: using the database as a work queue 1:01:54.
    • A table holds the tasks; many workers pull from it 1:02:19.
    • A worker starts a transaction, reads the table, takes the first task, deletes it or marks it complete, and commits 1:02:19.
    • A second worker doesn't want that same first row — it just skips the locked one and picks the next 1:02:19.
  • Lecturer's take: it "leaks the abstraction" of transactions into the application, arguably good or bad, but a worthwhile variation for handling different application scenarios 1:02:46, 1:03:11.