Transactions

Databases are designed to run all the incoming queries at the same time atomically.

Transactions & Atomicity

Databases use locks to isolate areas before it starts a SQL command to prevent other commands accessing this data, All other access to the data must wait until the area is unlocked, this is called atomicity.

All single SQL queries are atomic.

On Conflict

Whenever we try to add a new record that already exists, the query will fail due to the constraints in most scenarios, but we can still update the record that exists with the new value if we wish to do so, using ON CONFLICT keyword.

INSERT INTO foo (bar,car)
VALUES (dead,alive)
ON CONFLICT (bar)
DO UPDATE SET car = car+1
RETURNING *;

In this manner we can update a column based on the constraints without having queries failing!