Background
Starting from curiosity about how transactions in databases actually work, a question emerged.
If there are users A and B connected to the same database, and A performs a transaction but hasn't finished yet, will B be blocked?
Setup
The database used is PostgreSQL version 16.2.
It was set up on Docker with the following command:
docker run --name transaction_lab -e POSTGRES_PASSWORD=mysecretpassword -d postgres
Then enter the terminal using this command:
docker exec -it transaction_lab psql -U postgres
First Test
This test is to answer whether user B can still perform a SELECT query while user A is running BEGIN TRANSACTION?
The schema that will be executed is:
- User A and user B are connected to the Database
- User A runs
BEGIN TRANSACTION
- User A runs
UPDATE (not yet committed)
- User B runs
SELECT
- User A runs
COMMIT
- User B runs
SELECT (again)
Here are the results:
Initially, the President's name was "Avatar".
Then user A performs BEGIN TRANSACTION (2) and performs an UPDATE (3) to change the President's name to "Zuko".
During this gap, user B performs a SELECT (4) to check the President's name. It turns out the President's name is still "Avatar"
After user A completes the transaction by running COMMIT (5), when user B runs SELECT (6) again, only then does the President's name change to "Zuko".
So, the conclusion (CMIIW) from this experiment is that when one user performs a TRANSACTION, other users can still perform SELECT queries, but the data they get is the one that hasn't been updated yet, because the main actor hasn't performed COMMIT on their update.
Second Test
If in the first test, user B only performed a read or SELECT, in this test, we will try having both of them perform an UPDATE on the same data.
- User A and user B are connected to the Database
- User A runs
SELECT
- User A runs
BEGIN TRANSACTION
- User A runs
UPDATE on id = 1 (not yet committed)
- User B runs
UPDATE without TRANSACTION on id = 1 as well
- User A runs
COMMIT
- User A runs
SELECT (again)
Here are the results:
During the initial check, the President's name was "Avatar". Then user A starts a TRANSACTION (3) and performs an update to change the President's name to "Zuko" (4). However, user A hasn't performed COMMIT yet.
After that, user B comes in and tries to perform an UPDATE to change the President's name to "Katara". (5) What happened? It turns out user B is waiting. They received no response.
When user A performs COMMIT (6), at that very moment the UPDATE performed by user B is executed.
The result is that the President's name becomes "Katara".
So, the conclusion (CMIIW) from this experiment is that when user A performs a TRANSACTION on one row of data, and then user B comes wanting to edit the same data, user B will be blocked. Until when? Until user A completes their transaction. When user A has completed their transaction, then user B's command is executed. Therefore, the final data is the change made by user B.
Closing
This experiment was only conducted out of curiosity. In more complex cases, the results may differ. Lastly, I am not a backend engineer, which is why I tried to learn by conducting this experiment.
