Data Manipulation and Concurrency Control: Update
Learn how to use update statements for data manipulation and concurrency control.
We'll cover the following
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.
Get hands-on with 1200+ tech skills courses.