The features of PostgreSQL's internal mechanisms allow it to be very fast in some situations and 'not so much' in others. Today, we'll focus on a classic example of the conflict between how the DBMS operates and what developers do with it — UPDATE vs MVCC principles.
A brief plot from :
When a row is modified by the UPDATE command, two operations are effectively performed: DELETE and INSERT. In the current version of the row the xmax is set to the transaction number that executed the UPDATE. Then, a new version is created of the same row; the xmin value matches the xmax value of the previous version.
After some time, following the completion of this transaction, the old or new versions, depending on the COMMIT/ROLLBACK, will be recognized as "dead tuples" during a scan VACUUM of the table and cleaned up.
But this won't happen immediately; issues with 'dead rows' can accumulate very quickly—especially during frequent or in a large table, and later you may find that even .
#1: I Like To Move It
Let's say your method based on business logic is working fine, and suddenly you realize you need to update field X in a specific record:
UPDATE tbl SET X = WHERE pk = $1;Then, as the process continues, it turns out that field Y also needs updating:
UPDATE tbl SET Y = WHERE pk = $1;… and then there's Z as well—why not go all out?
UPDATE tbl SET Z = WHERE pk = $1;How many versions of this record do we now have in the database? Right, 4 of them! One is current, and you'll need to clean up the other 3 with [auto]VACUUM.
Don't do it this way! Use updating all fields in a single query — you can almost always modify the logic of the method like this:
UPDATE tbl SET X = , Y = , Z = WHERE pk = $1;#2: Use IS DISTINCT FROM, Luke!
So, you've decided to (during the application of a script or converter, for instance). And something like this goes into the script:
UPDATE tbl SET X = WHERE pk BETWEEN $1 AND $2;This kind of query appears quite frequently and almost always not for filling a new empty field, but for correcting some data errors. However, the correctness of the already existing data is generally not taken into account — and that's a mistake! This means the record is overwritten even if it contained exactly what was intended—why waste effort? Let's fix it:
UPDATE tbl SET X = WHERE pk BETWEEN $1 AND $2 AND X IS DISTINCT FROM ; Many are unaware of the existence of such a wonderful operator, so here's a cheat sheet on IS DISTINCT FROM and other logical operators to assist:
… and a bit about operations on complex ROW()-expressions:
#3: А я милого узнаю по… блокировке
Two identical parallel processes are initiated, each trying to mark the record as 'in progress':UPDATE tbl SET processing = TRUE WHERE pk = $1;
Even if these processes are independently doing different things, with the same ID, the second client will be 'locked' on this query until the first transaction is completed.Solution #1
: the task is reduced to the previous one.Simply add again
UPDATE tbl SET processing = TRUE WHERE pk = $1 AND processing IS DISTINCT FROM TRUE; IS DISTINCT FROM:
In this form, the second query will simply not change anything in the database, as everything is already 'as it should be' — therefore, no lock will occur. Next, the fact of the record's 'non-existence' is handled in the application algorithm.Solution #2
: advisory locksA large topic for a separate article, where you can read about
the methods of application and the pitfalls of advisory locks. .
: mindless callsYou should definitely have
simultaneous work on the same record. Delta Chat 1.2 for Android and iOS has been released.? Или вы все-таки накосячили с алгоритмами вызовов бизнес-логики со стороны клиента, например? А если подумать?..
Source: habr.com
