Instructor: Hi Guys, and Welcome back.
In this video we're going to talk about
the very exciting topic of Locking in Postgres.
Locking is a very important thing
and something that can be pretty tricky to understand.
And also debug if you encounter a locking problem
in your database later on.
So hopefully this lecture helps.
So what does locking mean first of all?
Well imagine when you go to the gym,
you put a padlock on your locker
to prevent anybody else from accessing your locker.
Same thing here,
but Postgres is locking access to data
instead of your belongings.
And when Postgres does this,
we call it "acquiring a lock."
I'm going to see this phrase a few times
throughout this presentation.
There's different types of locking in Postgres.
The first one is table-level locking,
where Postgres acquires a lock on a table
to limit access from other connections
and transactions into that table.
There's also row-level locking,
where it limits access to individual rows.
And there's also advisory locking,
where it suggests that
you don't access the row or the table,
but it doesn't actually limit anything.
Advisory locking is mainly used by clients
like ourselves rather than by the database itself.
Let's take a look at table-level locking, first of all,
since it's the major type of locking
transactions acquire this lock while working on a table.
And there's different types of lock.
Because there are different types of lock,
two transactions can potentially have different locks
on the same table at the same time,
if they don't conflict with each other,
because locks can conflict with each other
in different ways.
So if one transaction tries to acquire a lock,
but another transaction already has a conflicting lock,
then the first one has to wait
until the second one releases the lock.
This is how it goes.
These are the Postgres conflicting lock modes.
Made this diagram for you guys.
So I'll have it as a downloadable resource
in the resources section of this lecture.
You've got the current lock mode,
so transaction one comes first,
nobody has locked to the table.
So transaction one comes around and acquires a lock.
Locks get acquired for all sorts of reasons,
whenever you want to update a table
or insert something in it
or delete from it,
all sorts of different transactions
acquire different types of Lock,
for example, so that somebody can't come in
and delete a row while you're updating it.
So that's why the locks exist first of all,
and transaction one comes around acquires a lock.
And after it's done that it's currently processing the row,
and transaction two, then tries to acquire a lock.
So now I'm gonna show you guys
what are the conflicting blocks.
So let's say the transaction one comes around
and gets the access exclusive lock,
this is one type of lock that exists in Postgres.
Well, if transaction one has access exclusive,
and then transaction two comes around
and tries to get another lock or acquire a lock,
that is called access share.
Then it can't do that,
because access exclusive is basically the most powerful lock
or the most exclusive lock.
And therefore the transaction two simply has to wait.
Why might transaction one get access exclusive to the table?
Well for any number of reasons,
and we've got those reasons detailed
in the official documentation,
which I'm linking in the resources section of this lecture.
Similarly, we've also got row share, for example,
and if transaction two tries to get row share
into this table,
it's also gonna have to wait.
Since access exclusive is the most exclusive type of lock
transaction two always has to wait
if it wants to acquire a lock.
And there you have all the different types of lock
that are available in Postgres
access share, row share, row exclusive,
share update exclusive, share, share row exclusive,
exclusive and access exclusive.
Try saying that quickly.
And so now let's look at how these interact.
Let's say now that transaction one
instead of access exclusive
tries to get the exclusive lock.
As long as transaction two
only wants access share,
which is the least exclusive type of lock,
then this will be fine.
So what this means is
if transaction one gets the exclusive lock,
transaction two can get the access share lock,
but none of the other ones.
If transaction one got the share row exclusive lock
transaction two can get access share and row share.
This table here is a good thing to remember.
Not in a way that you have to memorise it.
But that these locks which there's a few of them
do interact in different ways
and not always what you expect.
And but it's important to know that
depending on the type of lock
that your transaction is trying to acquire,
you might encounter some problems here or there.
If you have a problem with locking in your tables
that can be really difficult to debug.
But when two transactions are conflicting,
Postgres is pretty good at resolving the conflict,
usually by making one transaction Wait,
sometimes if there's a deadlock.
We'll come To what that is in a moment,
by cancelling one of the transactions.
You can also manually lock a table if you want,
you can do that with a lock command,
and you can do this in a transaction
and then at the end of the transaction,
the lock will be released.
I would not recommend manual locking
as it can produce deadlocks.
But this is how you would do it.
If you do want to you just say lock table.
And then with in keyword we say the mode
or the type of lock that we want.
Similar to table locking, we've got row-level locking,
and we can lock individual rows
locking rows is only for writing.
So to transactions can always read from a locked row,
but they locked through may not be writable.
Row-level locking also happens automatically at times.
For example, if you try to update or delete specific rows,
those will be row locked
so that two updates can't happen at the same time.
Also, you can delete the row
while it's being updated, et cetera.
You can do row-level locking manually as well,
but again, highly discouraged.
You can just say select star from users, for example,
where email is bluh.
For update, the for update here is the lock.
So we use the select statement for locking.
This gets the for update lock.
And now here comes the big bullet point.
transactions that attempt to update, delete,
select for update, select for no key update,
select for share, or select for key share
will be blocked.
And then they have to wait.
So these different types of row-level locks
operate in the same way that table-level locks do.
There's fewer types,
but they still can conflict with each other.
And essentially what happens is
when you use a row-level lock like this,
and then other transactions can't interact with the row
in different ways.
Depending on the lock that they want.
This for updates manual row-level lock
is often used so that the current transaction
can update the row multiple times
without fear of another transaction
acquiring a conflicting lock.
So here what you would do is
you would select star from users for updates.
And that would lock all the rows
where the email is row for example dot comm
nobody can then go in and update or delete this row.
So you can be safe knowing
that you can update this row many times
because nobody else can come in and change it.
But again, this is pretty niche stuff,
so you don't have to worry about it all that often.
Sometimes it does become important though.
Let's have a look at deadlocks.
Deadlocks are pretty simple.
You can have transaction one
that wants to have a lock that transaction two needs.
And transaction two has a lock that transaction one needs.
For example here let's see we've got transaction one
with access exclusive on table one.
And then you've got transaction two
with access exclusive on table two.
This is fine, they don't conflict at all
because they're using different tables no problem.
But then within the same transaction
transaction one now once access exclusive on table two
and transaction two once access exclusive on table one.
So now transaction one is waiting on two.
And two was waiting on one
and you've got yourself a deadlock.
What would happen here
is Postgres would cancel one of the transactions.
And so you would have to be paying attention to that,
so that you can rerun the query to the transaction.
If this happens.
Again, it doesn't happen very often.
And this is one of the biggest reasons
why you should not normally do manual locking.
Because when you start doing manual locking,
especially in a large and complicated database,
it can become quite dangerous.
For more information, we've got the E-book
and we've got a bunch of references in the E-book
in case you're interested in learning more.
But I would recommend just bookmarking it
in case you need to learn more about locking later on.
Postgres locking can bite you later on
without you expecting it.
So it's important that you know this exists,
but you don't have to memorise all the modes
and how they operate and all that stuff.
All right, thank you guys for watching.
Thanks for joining me in this video.
I hope you find it interesting
and you've learned something
and I'll see you in the next one.