Understanding query locking is key to using postgres concurrently. So let’s look at an example to learn more about how locking works and how to see what’s going on within your database.
Playing in the sandbox
To play around with locks let’s first create a sandbox.
1 2 3 4 5 6 7 8 9 10 |
|
Now let’s open two postgres consoles to play in the sandbox. For clarification let’s name them Alice and Bob. You can SET the console prompt with:
1
|
|
Now Alice takes a look for what kind of toys there are.
1 2 3 4 5 6 7 8 9 |
|
Note we introduce the statement with BEGIN
to open the transaction explicitly and keep it open until we commit or rollback.
If Bob does the same he would get the same immediate output.
1 2 3 4 5 6 7 8 9 |
|
That is the two SELECT
statements do not interfere and can safely run concurrent without blocking each other. Well that’s what we expect from a reliable high performance database.
pg_lock
Nevertheless our transactions are still open. Let’s take a look on a third console, let’s call it Eve, what kind of locks are acquired.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
|
Each active lock is stored in pg_catalogs.pg_lock view. To make sure we only see locks on the sandbox
database we join pg_catalog.pg_database and filter by
datname=’sandbox’ OR datname IS NULL`.
To not see locks from our own query we also filter out entries from our own backend_pid. NOT pid = pg_backend_pid();
The relation column is typecast into regclass
to make it human readable.
Let’s read the fifth row:
1 2 3 |
|
The relation
toys
is locked with AccessShareLock
by virtual transaction 1/282
from pid 45263
and the lock is granted. That is the transaction got the lock. Everything is fine Bob and Alice are happy as they can both concurrently see what toys are available.
Note that each transaction also hold an `ExclusiveLock on its own virtualxid that is their virtual transaction ID.
Now Alice decides to grab the car:
1 2 |
|
As you can see she got it successfully. Let’s take a look at what happens to the locks:
transactionid
1 2 3 4 5 6 7 8 9 10 11 12 |
|
Two more locks are added. Alice got a RowExclusiveLock
on toys
that is the car is now to here. There is also a real transactionid added for Alice on which Alice holds `ExclusiveLock. For every transaction that potentially changes the state of the database a transactionid is added.
MVCC
But as the transaction is not committed yet Bob still sees the old data.
1 2 3 4 5 6 7 |
|
As it is not clear whether Alice will commit or rollback her transaction Bob sees the state of the table as it was before Alice started her transaction. Postgres uses MVCC - Multi Version Concurrency Control to make sure that each transaction always sees a consistent state of the database.
Blocking Queries
Let’s say Bob also wants the car (a pattern that you can see every day in sandboxes):
1
|
|
You realize nothing really happens because Bob has to wait until Alice finishes her transaction.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
|
Now a transactionid for Bob is added and Bob is asking to get a ShareLock
on Alice’s transactionid. The typical “mom I wan’t to play with the car too” pattern. As both locks conflict with each other Bob’s request is not granted and he needs to wait until Alice releases her ExclusiveLock
on her transactionid by finishing her transaction.
pg_stats_activity
There is another interesting pg_catalog view called pg_stat_activity which shows you what kind of queries are going on:
1 2 3 4 5 6 7 |
|
Here we see that Alice’s query is waiting for the transaction to commit while Bob’s query is active and waiting.
Now let’s combine the two to see which query is waiting for whom.
1 2 3 4 5 6 7 8 9 10 11 12 13 |
|
Here we can clearly see which query is blocked by which statement.
Now if Alice decided to rollback (or commit) her transaction her ExclusiveLock
would release and Bob would get the ShareLock
on her transactionid. If Bob now commits his transaction, the row would get UPDATEd depending on whether Alice commited or rolled back.
1 2 |
|
1 2 3 4 5 6 7 8 9 |
|
Of course if Bob and Alice would have decided to take different toys no such blocking situation would have appeared.
Explicit Locking
Another typical sandbox pattern is that some kid wants to take it all without really needing it.
Let’s say Alice decides to take the access exclusive.
1 2 3 4 |
|
Now Bob has to wait although Alice doesn’t really grab any toy.
1 2 |
|
And our lock table would look like this.
1 2 3 4 5 6 7 8 9 10 11 12 |
|
As Alice is holding AccessExclusiveLock without (yet) changing the database state she has no transactionid
and as Bob didn’t get the RowExclusiveLock
on the toys table he also doesn’t have any real transactionid
. Thus our above query to see blocking statements won’t help (as we join on `transactionid).
1 2 3 4 5 6 7 8 9 10 11 |
|
So for Eve it would look like everything is fine. While
1 2 3 4 5 6 |
|
clearly shows that there is a query waiting. Note we added the difference between now()
and query_start
to see for how long the query is waiting.
So let’s take another look:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
|
Here we can see again whats really going on. By joining the relation on which the lock is hold.
Now Alice has been told that it’s not nice to take an explicit lock without really needing it. So she commits her transaction without doing anything and Bob can get a toy.
1 2 |
|
1 2 |
|
Note Bob’s transaction is still open. If we take a look at the locks table we see:
1 2 3 4 5 6 7 8 9 10 11 12 |
|
So only after Bob got the RowExclusiveLock
a real transactionid
for his transaction was added. So Bob is fine and commits.
1 2 |
|
RowExclusiveLock
As Alice is undecided which toy to take and not allowed to take explicit locks she takes another approach.
1 2 3 4 5 6 7 8 |
|
That is “I want to see all toys and maybe I’ll take one but as I’m undecided I don’t want any other to take one until I made my decision.” (Another typical sandbox pattern)
Bob who has decided to take the shovel won’t get it and needs to wait:
1
|
|
While Eve sees the following situation:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
|
Bob who clearly wants to change the database state got an transactionid 24273810
and again has to wait for Alice to get ShareLock
on her transactionid 24273809
.
Join Locks and Activities
So this time we need our first approach to join locks and activities.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
|
So let’s combine both to be 100% sure:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
|
As Eve can’t remember this long query she decides to put that into a view;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
|
Note we added the waiting_duration
to see for how long the query was blocked, and added the current_database()
function to automatically filter by the database the view belongs to.
Now Eve can simply fire:
1 2 3 4 5 |
|
and is always able to see what the kids are up to. Now Eve decides to get a Latte Macchiato and read the postgres manual on explicit locking to get an idea of the different lock types and their conflict modes.