Dev Notes: Select Locks In PostgreSQL

Learning some useful techniques about selecting rows for locking in PostgreSQL. For example, you can use SELECT … FOR UPDATE for locking specific rows from other transactions touching them:

SELECT * FROM mytable FOR UPDATE

Then there’s the SKIP LOCKED clause which will only return rows that are currently unlocked:

SELECT * FROM mytable SKIP LOCKED

I haven’t tried this, but based on the grammar, it looks like this can be combied, to lock and return only the rows that are currently unlocked:

SELECT * FROM mytable FOR UPDATE SKIP LOCKED

Could be useful for using PostgreSQL tables as a shared outbox. I’m imagining one process writing rows to this table, while another one is selecting them for update while skipping the locked ones, doing something with them that involves side-effects (e.g. writing them to a queue), then removing it from the table, all within a transaction:

inTxn(func(txn) error {
  rows := txn.selectForUpdateSkippedLocked()

  if err := doSomething(rows); err != nil {
    txn.rollback()

    return err
  }

  txn.deleteRows(rows)

  return txn.commit()
})

Of course, either the doSomething() or commit() here could fail. I guess you could move the doSomething() outside the transaction, and only execute the thing if the transaction was successful. But then any failures resulting from doSomething() would result in those rows being lost to retries. Keeping it in the transaction could lead to duplicates, but that’s usually better than loosing data. Best to make doSomething() idempotent or resistant to duplicates.