Lab: Concurrency Control
In this lab, you will experiment with PostgreSQL’s concurrency control mechanisms.
You should work on this assignment either individually or with a partner.
Instructions
These exercises will use a database consisting of a single table which maintains very minimal information about bank accounts:
account(number, balance)
where number is an integer and is the primary key, and balance is a decimal number representing a dollar amount.
As you complete the steps below, be careful to follow the directions exactly.
Step 0: Set Up PostgreSQL
Run the following command to start a container running a PostgreSQL server.
docker run --name concurrency-lab --detach --env POSTGRES_PASSWORD=lab --rm postgres:16.1
Read the command carefully. Notice that we are not using docker compose, since we only need a single container for this lab, and the container’s configuration is simple. The container is named concurrency-lab, and it will be deleted when it is stopped.
You can open psql, the PostgreSQL shell, by running:
docker exec -it concurrency-lab psql --username=postgres
Open two psql shells in two separate windows or tabs. If your terminal program supports it, change the background colors of the shells, so you can clearly differentiate them.
Next, create the database schema by running the following SQL command in one of the shells:
CREATE TABLE account (
number INTEGER PRIMARY KEY,
balance NUMERIC(20, 2)
);
Finally, run the following command in both shells:
\set AUTOCOMMIT off
By default, psql runs with AUTOCOMMIT on, which has the effect of running commit after each command. The experiments in this lab depend on AUTOCOMMIT being turned off.
Step 1: Visibility Experiment
This exercise deals with visibility of the results of one transaction to another transaction.
| psql Shell 1: Creating two accounts | psql Shell 2: Getting information about the accounts |
|---|---|
\set AUTOCOMMIT off |
\set AUTOCOMMIT off |
insert into account (number, balance) values (1, 100.00); |
|
commit; |
|
insert into account (number, balance) values (2, 500.00); |
|
select * from account where number = 1; |
|
| A) What do you observe? Why do you think you observe this? | |
select * from account where number = 2; |
|
| B) What do you observe? Why do you think you observe this? | |
commit; |
|
select * from account where number = 2; |
|
| C) What do you observe? Why do you think you observe this? |
Record your observations and explanations.
Step 2: Serializable Schedule Experiment
Initially, the account balances sum to $600. Next, you will attempt a transfer between the accounts.
| Shell 1: Transfer $50 from account 2 to 1 | Shell 2: Transfer $20 from account 1 to 2 |
|---|---|
update account set balance = balance - 50 where number = 2; |
|
update account set balance = balance + 20 where number = 2; |
|
| A) What do you observe? Why do you think you observe this? | |
update account set balance = balance + 50 where number = 1; |
|
commit; |
|
| B) What do you observe? Why do you think you observe this? | |
update account set balance = balance - 20 where number = 1; |
|
commit; |
|
select * from account; |
select * from account; |
C) Record the account balances reported in each shell. Do the account balances sum to $600?
Step 3: Attempted Non-Serializable Schedule Experiment
Next, you will attempt a transfer with a slightly different sequence of operations.
| Shell 1: Transfer $50 from account 2 to 1 | Shell 2: Transfer $20 from account 1 to 2 |
|---|---|
update account set balance = balance - 50 where number = 2; |
|
update account set balance = balance - 20 where number = 1; |
|
| A) What do you observe? Why do you think you observe this? | |
update account set balance = balance + 50 where number = 1; |
|
| B) What do you observe? Why do you think you observe this? | |
update account set balance = balance + 20 where number = 2; |
|
| C) What do you observe? Why do you think you observe this? | |
commit; |
commit; |
select * from account; |
select * from account; |
D) Record the account balances reported in each shell. Do the account balances sum to $600? Which shell’s transfer affected the database?
E) Explain why the previous experiment’s schedule was serializable, but this experiment’s schedule was non-serializable.
Step 4: Permitted Non-Serializable Schedule Experiment
Next, you will test a sequence of operations that introduces an inconsistency into the database.
| Shell 1: Transfer $50 from account 2 to 1 | Shell 2: Transfer $20 from account 1 to 2 |
|---|---|
select * from account; |
|
| A) Record the balances you observe | |
update account set balance = balance + 20 where number = 2; |
|
update account set balance = balance - 20 where number = 1; |
|
commit; |
|
update account set balance = (balance from A) - 50 where number = 2; |
|
update account set balance = balance + 50 where number = 1; |
|
commit; |
|
select * from account; |
select * from account; |
B) Record the account balances reported in each shell. Do the account balances sum to $600?
C) Describe how this situation might occur if you wrote Python code to interact with the database.
Hint: The next step will explain why the balances don’t sum to $600.
Step 5: Isolation Levels Experiment
The anomaly you observed in the previous experiment was permitted by PostgreSQL’s default isolation level, “read committed.” Making the isolation level more strict will prevent this kind of anomaly, as you will see in this experiment.
First, type the following in either shell. This will restore the balances so they sum to $600 again.
update account set balance = 100.00 where number = 1;
update account set balance = 500.00 where number = 2;
commit;
Next, run the following commands in each shell. Notice that we start by ending any existing transactions with the ROLLBACK command. Then, we create a new transaction with the REPEATABLE READ isolation level using the BEGIN TRANSACTION command.
| Shell 1: Transfer $50 from account 2 to 1 | Shell 2: Transfer $20 from account 1 to 2 |
|---|---|
ROLLBACK; |
ROLLBACK; |
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
select * from account; |
|
| A) Record the balances you observe | |
update account set balance = balance + 20 where number = 2; |
|
update account set balance = balance - 20 where number = 1; |
|
commit; |
|
update account set balance = (balance from A) - 50 where number = 2; |
|
| B) What do you observe? Why do you think you observe this? | |
update account set balance = balance + 50 where number = 1; |
|
| C) What do you observe? Why do you think you observe this? | |
commit; |
|
| D) What do you observe? Why do you think you observe this? | |
select * from account; |
select * from account; |
E) Record the account balances reported in each shell. Do the account balances sum to $600? Which shell’s transfer affected the database?
F) Explain why this experiment differed from the previous experiment.
Submit
Submit your observations and explanations on Gradescope.
The assignment will be graded as part of your assignment grade.
Further Reading
Acknowledgements
Thanks to Gordon College’s Databases materials, which served as a basis for this assignment.