Control concurrency for READ THEN UPDATE THEN WRITE transaction in Postgres

Explanatory note: I'm assuming you mean a read-modify-write workload, and that the capital letters for "READ THEN UPDATE THEN WRITE" are not intended to signify some special transaction option SQL syntax from a product I'm unfamiliar with.

If your webapp is doing read-modify-write cycles with high concurrency and traffic, then you can't use traditional row locking:

• BEGIN
• SELECT primarykey, col1 FROM thetable WHERE ... FOR UPDATE
• process in the application
• UPDATE blah SET col1 ... WHERE primarykey ...
• COMMIT

because user "think time" and network latency is potentially unbounded. Most of your connections will be stuck for an indefinite amount of time in the "process in the application" phase. Each waiting session means an open, idle transaction, which means finite database resources such as connection limits and memory consumed.

The conventional, well-established solution to this is to use optimistic concurrency control, sometimes misleadingly referred to as optimistic locking. Some ORMs support this natively. It's easy enough to implement if you're working with SQL directly or via a framework that doesn't though. The principle is that your logic flow looks more like this:

• BEGIN READ ONLY TRANSACTION
• SELECT primarykey, col1, row_version FROM thetable WHERE ...
• COMMIT
• process in the application and wait for user response
• BEGIN
• UPDATE blah SET col1 ..., row_version = row_version + 1 WHERE primarykey ... AND row_version = 'prev_row_version'
• Check to see if the UPDATE affected any rows using the affected-row-count returned by the database in the UPDATE response
  • If it affected zero rows, the WHERE clause didn't match, suggesting that someone else updated the row since we SELECTed it. Go back to the beginning and start again.
  • If it affected one row, we know nobody else beat us to updating this row, so COMMIT and tell the user everything's OK.

Frameworks like Hibernate support this automatically by annotating a column as a row version.

Optimistic concurrency control can inter-operate with traditional locking with appropriate database triggers. See, e.g. the sample trigger I wrote for Hibernate inter-operation.