How to Retrieve Old Column Values Before an SQL UPDATE?

How to Retrieve Old Column Values Before UPDATE Using Only SQL

Problem Statement

Despite its robust functionality, SQL's RETURNING clause faces a limitation: it only retrieves post-update values. This can be a hurdle when seeking the original values of updated columns, especially in highly concurrent environments.

Solution Using a Self-Join

Erwin Brandstetter's solution elegantly addresses this challenge by using a self-join in the FROM clause:

UPDATE my_table
SET processing_by = our_id_info -- unique to this worker
FROM my_table AS old_my_table
WHERE trans_nbr IN (
  SELECT trans_nbr
  FROM my_table
  GROUP BY trans_nbr
  HAVING COUNT(*) > 1
  LIMIT our_limit_to_have_single_process_grab
)
AND my_table.row_id = old_my_table.row_id
RETURNING my_table.row_id, my_table.processing_by, old_my_table.processing_by AS old_processing_by

By joining to another instance of the table (old_my_table), the query can seamlessly retrieve the values before the update occurred.

Alternative for Concurrent Environments

In scenarios with substantial concurrent write loads, additional measures are necessary to prevent race conditions:

1. Serializable Isolation Level: Run the transaction under the most strict isolation level to prevent other transactions from conflicting.
2. Explicit Row Locking: Add a FOR UPDATE clause to the subquery that retrieves the row to be updated, ensuring that only locked rows are processed. This technique is more efficient than using a serializable isolation level.

The above is the detailed content of How to Retrieve Old Column Values Before an SQL UPDATE?. For more information, please follow other related articles on the PHP Chinese website!