Database Atomicity (practice)

We talked in a previous blog post about whats ACID in a theoretical way, in the following series of blog posts we’re gonna see some ACID principals in action.

By now you might have noticed that i like to present some theory first and then do some practice to make the theory stick and make sure i understand the concepts in a more concrete way, the right balance between theory and practice is key in my own learning process

What we’re going to do?

1- Spin up a postgreSQL docker container

2- Create a table, and insert some data

3- Play around to see ACID principals in action

Prerequisites

• I am assuming you have docker already installed on your machine
• Also the commands shared are MacOS commands, some simple google/chatGPT search can give you the commands for windows or any other operating system

Preparation

1- first we’re gonna spin up the docker container:

docker run -d --name postgres-acid  -p 5432:5432 -e POSTGRES_PASSWORD=postgres postgres

This command basically will run a postgreSQL docker container and named it `postgres-acid`

then we’re going to start an interactive terminal into our container to start executing sql queries directly into it:

docker exec -it postgres-acid psql -U postgres

I explained those commands before in a previous post, you might want to check it out

We know we were successful if we see the postgres prompt as follows:

postgres=#

2- Second we’re going to insert some data

Lets assume we have a bank database, so we’re going to create a table called account with 3 columns, auto incremented primary key called id, integer balance, and name which is the account holder name

create table account (id serial PRIMARY KEY, balance int, name VARCHAR(255));

Next we need some rows, all we need for now is two rows:

insert into account (balance,name) values (1000, 'Mohamed');

insert into account (balance,name) values (900, 'Jack');

make sure you have the two rows as expected by executing:

select id, balance, name from account;

1- Atomicity

The first principal i want to demonstrate is atomicity, the definition is in my previous theory blog post, you might want to read it first

To demonstrate atomicity, we’re going to implement a transaction that transfer an amount of 100 cents/dollars -or whatever unit :-D- from Mohamed to Jack

we need to do all queries inside a transaction, if we just execute queries, the database engine will create a transaction for each query alone and commit it immediately, and this will not guarantee atomicity between queries

begine;

this will begin a transaction

now the first query will debit 100 units from Mohamed:

update account set balance = balance - 100 where id = 1; 

now lets verify

select * from account;
 id | balance |  name   
----+---------+---------
  2 |     900 | Jack
  1 |     900 | Mohamed

Mohamed now is 100 dollars poorer :-), but keep in mind that we didn't commit the transaction, so without atomicity if we crash the container or quit in the middle of the transaction, 100 dollars will have disappeared into thin air, so lets try to do that

just stop the container either from the docker GUI app or from another terminal window:

docker stop postgres-acid

now start again the interactive terminal into the container to see what happened to our data:

docker start postgres-acid

docker exec -it postgres-acid psql -U postgres

select * from account;
id | balance |  name   
----+---------+---------
  1 |    1000 | Mohamed
  2 |     900 | Jack
(2 rows)

you see, no money was lost, the database rolled back the changes that we did to make sure either all queries succeed or non succeed, the essence of atomicity!

lets continue with the rest of principals in another post, lets keep each post short and sweet!