Re: Question regarding how databases support atomicity

> On May 3, 2024, at 20:02, Siddharth Jain <siddhsql(at)gmail(dot)com> wrote:
>
>
> The way I understand this is that if there is a failure in-between, we start undoing and reverting the previous operations one by one. But what if there is a failure and we are not able to revert an operation. How is that situation handled? e.g., something failed when we tried to do Step 3. now we revert Step 2 and succeed. but when we try to revert step 1 we fail. what happens now? To me, it seems its impossible to guarantee true atomicity in general.

PostgreSQL does not "undo" operations as such. When modifications are made to the database, those modifications (inserts, updates, deletes) are marked with the ID of the transaction that made them. A COMMIT or ROLLBACK in PostgreSQL just notes if those modifications are now "permanent" (if the transaction committed) or "invisible" (if the transaction rolled back). This technique in general is called Multi-Version Concurrency Control. Here's a good presentation that describes how it works in PostgreSQL:

https://momjian.us/main/writings/pgsql/mvcc.pdf