Data Manipulation and Concurrency Control: Update

The SQL update statement

The SQL update statement is used to replace existing values in the database. Its most important aspect lies in its concurrency behavior, as it allows replacing existing values while other users are concurrently working with the database.

In PostgreSQL, all the concurrency features are based on Multiversion Concurrency Control (MVCC), and in the case of the update statement, it means that internally PostgreSQL is doing both an insert of the new data and a delete of the old. PostgreSQL system columns xmin and xmax allow visibility tracking of the rows so that concurrent statements have a consistent snapshot of the server’s dataset at all times.

As row locking is done per tuple in PostgreSQL, an update statement only ever blocks another update, delete, or select for update statement that targets the same row(s).

Assigning names

We created some users without a nickname before, and maybe it’s time to remedy that by assigning them their uname as a nickname for now.