hey I'm Kjus today we're gonna be
talking about concurrency in Postgres so
as I said my name is J Jess
okay just mono her on pretty much all
social media get up Twitter etc I'm an
engineer over at segment which is a data
infrastructure company that makes it
really easy to get data into tools that
let you analyze it from your app or your
Salesforce account or many other
services so this talk is going to be
about concurrency in Postgres if you've
if you treat your database right now is
like a black box and don't really know
about how it handles concurrency or you
haven't written applications that handle
concurrency before then in this talk
first off you'll learn about how like
the world how your applications handle
concurrency and then about how Postgres
implements concurrency control we'll get
into the implementation details a bit
about like what stores etc and then
lastly we'll talk about how you should
really think about concurrency control
when you're dealing with Postgres as a
post course user that's the most
important thing so first off the big
question that we ask when we think about
different concurrency control mechanisms
performance guarantees of database it's
that era is like what our databases
value and to answer that we really need
to think about how we evaluate databases
so of course there's a there's there's
one line of thought you need to evaluate
database based on the cost is it a free
license like Postgres is it Oracle is it
ms sequel etc then there's also scope of
features like does your database support
this feature on indexes or something
else but then there's a couple other
points which are the most important in
my opinion when you're choosing between
technologies which are performance and
guarantees and and those pretty much go
hand in hand and that's what we're gonna
focus on whenever we talk about any of
the mechanisms in this talk that
Postgres uses so what do databases
guarantee so when we talk about
guarantees in databases this is
room for a lot of buzzwords there's
plenty of terms used to describe
different databases and their guarantees
some really give you a good idea and
some don't
for example some people say databases
are transactional some say that they're
acid compliant some say that their base
the opposite of this some distributed
stories are graded on something called a
cap theorem in this talk we'll start
reducing the ambiguity around the ones
that actually apply to Postgres because
there's a lot of terms out there to get
confused with so to start off with
Postgres is transactional so what this
means is that everything in Postgres
revolves around the idea of a
transaction and if you're unfamiliar a
transaction is really a unit of work
that's performed by a database like
Postgres and it's characterized by being
combined with something called acid
which stands for an atomic consistent
isolated and gibble so what does the
transaction look like if you're to think
of a transaction or you're writing a
simple application the textbook kind of
example is you have a series of actions
you want to take they're each their own
sequel statements and you want to make
sure that if one fails you don't leave
the database in a partial state so this
example on the screen basically has one
user in a bank paying another user $100
so in this case Alice is paying Bob $100
as a bank we want to record that this
transfer actually happened and we want
to deduct from one balance and add to
the another so this happens in three
separate sequel statements but to make
sure that you know if our database
restarts in between one we don't repeat
one of the actions or to make sure that
we actually complete all of them and
Alice doesn't go to the bank and
complain that $100 was taken out of her
account but Bob didn't receive any money
we put in a transaction and it's
Postgres his job to make sure that all
the steps complete or nothing happens at
all um so in post curse actually
everything is a transaction and that's
really important to think about when you
just log in to psql the the raffle and
started running sequel queries that are
doing write operations they have the
guarantees of a transaction as well
there's implicitly a beginning commit
this is known as auto commit in Postgres
and even when you think about
selects you have to think about them in
the context of transactions even if
you're reading because they do have the
guarantees that are associated with them
so what are those guarantees so first
off in acid we have atomicity so we need
to say that all transactions are atomic
what does it mean to be atomic basically
what it means to have an atomic
transaction is that the operation is all
or nothing and there's no partial
failures this is like the key reason
people think about transactions in the
first place so as an example if US bank
fails to add money into Bob's balance
should not deduct from alice's balance
this is pretty straightforward it's
something but sometimes take for granted
when just using these databases
transactions also guarantee consistency
this is a big point in Postgres it
basically means that you always you
always keep the database in a valid
state no matter what you're running if
you set some constraint in the schema
that's always going to be enforced even
if there's some failure in the database
the worst case is that your operation
fails and that's clear because it
doesn't tell you the database doesn't
tell you that it succeeded but it'll
never leave the database in an invalid
State next off.we transactions guarantee
isolation isolation is a really key term
and we're gonna keep looping back into
isolation as we talk about this more the
pure definition of isolation is is that
when you execute something concurrently
you get there a same result as when you
execute it serially but basically the
point of isolation is to allow you to
write queries and pretend that you're
the only one writing queries against
this database it's kind of similar to in
an operating system isolation between
processes lets you act like you're the
only process and you only see your
memory rather than other processes but
in reality something has to manage this
processes and there are different
guarantees that you can get like
different levels of isolation in
Postgres and unless you know what level
of isolation you're getting you may have
an unexpected result so we will talk
about that soon transactions also
guarantee durability so durability
basically means when you commit a
transaction it can't be rolled back
without notice like when it's committed
its committed if there's any failure the
database restarts etc your transaction
has been committed you won't be able to
query this database in a state where it
disappeared now if your disk fills or
something like that and you don't have
replicas then you're in a different
state but in Postgres mind it won't be
telling you that something is not there
after it's committed
so why handle concurrency altogether
so oftentimes if you're going from you
know like a serial workload to a
concurrent workload people think you
know databases implement these sort of
concurrency control mechanisms in order
to improve performance for example so at
first glance you know people may say
that if you take out concurrency control
from the equation you're going to get
better you're going to not have as good
performance but in reality concurrency
control is really a mechanism this is
obviously little clowny graph but
concurrency control is a mechanism that
is going to give you guarantees while
you're having concurrency if we just
allow concurrency in the database
without these kind of control mechanisms
then you're not going to get the
guarantees and sometimes these
concurrency control method mechanisms
actually deteriorate the performance
from just having anyone do anything but
then you don't have guarantees so
concurrency controls main the main
reason we have concurrency control is to
prevent race condition race conditions
so there are a couple race conditions
that I want to talk about before we talk
about how Postgres handles them and the
different features Postgres offers so
first off the the main race conditions
you can think of in a transactional
database is a dirty read so basically
what a dirty read is is if one
transaction is able to read uncommitted
work of another transaction that's
really bad of course because the point
of transaction is to always leave you
know have a series of step and leave the
database in a perfect state so Postgres
doesn't allow these but it's a common
race condition that can happen if
transactions are not formatted properly
another race condition that can happen
is a non repeatable read this is
possible in Postgres and so are the rest
of them that we'll cover here and if you
don't understand a transaction isolation
you may be vulnerable to this especially
if you're running with the defaults so a
non repeatable read is basically when
one transaction queries a row updates it
based on that may be queries it again
gets a different result basically if
your reads are not repeatable you know
that's possible under some transaction
isolation levels which we'll go into
more details but this is one of the race
conditions that your obligation may
accept or have to handle a phantom read
is basically when a transaction sees a
row that was it was first there and
makes some action upon it and then it
goes away
because another transaction deleted it
this is also possible to Postgres so we
need to understand when it's possible
and lastly is serialization anomalies
these are the hardest to understand but
it basically means that when you execute
things concurrently they're not the same
as when you execute them serially and
sometimes you're okay with this it just
depends on your application and what
it's doing but we'll talk about how to
know if you're okay and how do you
prevent it if you're not okay so if
you're writing an application that has
multiple threads and you're doing
concurrent actions on some shared memory
what's the classic way to to prevent
race conditions locks you just throw
some locks around all the shared memory
and you acquire a lock before you use it
so no one else can you can use that
shared memory at the same time and then
you're not vulnerable to race conditions
right so this is a pretty naive solution
but it's really easy to implement it's
not post-crisis main strategy of
concurrency control some of you may say
of course we can do a little better with
locking than what I showed in the first
example here where we just lock around
all axis we're gonna have something like
a readwrite lock where the readers take
a shared lock so you can read multiple
times you can read at the same time and
then the writers take an exclusive lock
so that you can't read and write at the
same time and you can't have two writers
at the same time this is also there but
we can still do better with high highly
optimized or high throughput concurrency
so let's look at how databases
and no concurrency so first of all it
really boils down to how your database
thinks so there's really two major kind
of lines of thought for a concurrency
control when implementing a database
there's a pessimistic concurrency
control which essentially locks
everything on access with a read/write
lock where readers get a shared lock
writers get an exclusive lock and
expects that race conditions are going
to happen so it locks everything to
prevent against this it thinks of race
conditions and it thinks of conflicts as
a normal case and then there's
optimistic concurrency control which
doesn't acquire locks for everything
lets you take actions but fails to
commit your actions and abort your
transaction if it finds that you're
actually doing something that's going to
result in a conflict or a race condition
and pessimistic concurrency control is
traditionally implemented through this
procedure called two-phase locking
it's pretty much the original
concurrency control model for databases
dates back to like the 70s so the
original databases that were
transactional we're using this protocol
boils down to readers taking a shared
lock writers taking exclusive lock locks
kind of build up through the transaction
in the growing phase is what we call it
and then at the end of the transaction
before you commit you start releasing
those locks in reverse order so that you
don't have race conditions where
multiple people are updating the same
thing or depending on a certain data now
this isn't really optimized for high
throughput because writers are gonna
block readers readers are gonna block
writers which isn't great in many
applications now time stamp ordering is
the second class of concurrency control
protocols and it's what we call a lock
free method of handling concurrency so
basically what that means is that in
time step ordering there has to be some
timestamp associated with every single
action and this time stamp is going to
be used to evaluate or detect conflicts
between two operations and if the
database determines based on this time
stamp that you know one operation should
be killed in favor of another it will do
so the kind of policies that it uses to
do
is definitely implementation-specific so
we're gonna get into how Postgres
implements time stamp ordering through
like a little sub protocol does that
make sense
but the key thing about any time stamp
or during protocol is that you have to
make sure that the time stamp is
monotonically increasing and what this
means is it it never goes backwards it
always goes up if at any point the the
time stamp that you're using which can
be a number it can be a real time sim
can be whatever goes backwards you're
going to have problems and your your
guarantees aren't gonna hold up in your
concurrency control strategy we'll talk
about how Postgres ensures this so how
does Postgres handle concurrency
Postgres implements something called
MVCC does anyone know what MVC C stands
for okay multi-version concurrency
control so that's a mouthful to say so
and what we mostly just say MVCC for the
rest of this talk but basically what it
means is that in order to handle
concurrency Postgres will make multiple
versions a case nap shots of your data
for every write transaction and this
works in a way that reading and ever
blocks writing and writing never blocks
reading this is kind of the golden rule
that everyone will tell you if you're
using MVC C don't worry your readers
won't block key writers and vice versa
it's kind of why MVCC is popular today
and like the most popular concurrency
control mechanism in the last decade for
sure so now we'll talk about this how
this actually works because it sounds a
little magical at first when you're
coming from a two PL or two phase
locking background so let's imagine at
time equals 0 so at epoch we insert a
record and we just insert one record
into a table it's a topple with two
values basically in your mind we just
have one round the database and what
MVCC does is it stores one row with some
additional context perversion so
initially this additional context is is
a tuple it's a begin timestamp and an
end timestamp the begin timestamp is
basically when was this row inserted or
what was the number that's associated
with this insert operation and the end
timestamp is when is this road like
expired when is in our element
more so initially this is going to be
something to represent the value of
infinity the end times dam because
you've inserted one row it's not expired
by any means you haven't updated and you
haven't deleted it etc and the begin
time sim is gonna be when you started in
this case we started at T equals zero so
zero here so now imagine at T equals two
we actually go and update this record
and say there's a user Bob in a game and
we set his scored five from two so what
we see is that MVCC adds another row
into the internal representation of the
datastore where basically we have the
begin timestamp of this row and n
timestamp of this row and a pointer to
this actual data and what happens is the
end timestamp of the first row is
atomically changed from infinity to 2
because that's when the next transaction
started at timestamp equals two and the
end time stamp of the niro is infinity
so now imagine that at T equals three we
go into the database and we try to read
the row so what happens going to read it
we have one row which is valid from zero
to two and Row one row that's valid from
two to infinity so if we select that T
equals three the first rows not gonna be
valid because our timestamp that we're
using our snapshot for this transaction
is a greater than the end timestamp of
the first row so we're not going to read
that one instead we'll be reading the
next one because it hasn't expired yet
and it began before our transaction so
it's visible to us so at T equals three
if we issue a select we will see the
second row which is really the update so
now what happens when I yeah so we'll
see the second row because it was
updated so now imagine at T equals four
we try to delete the row that we've
inserted and now updated basically what
happens when we delete the data doesn't
actually go away instead the end
timestamp of the second row which was
previously infinity is changed from
infinity to 4 because at t equals 4 we
issued the delete if we read again now
at t equals 5
neither of the rows are valid because
five is greater than the end timestamp
of both of them so we can't see anything
back from the database but if there's an
old transaction sitting open and it's
running a statement that was blocked for
whatever reason at T equals one is when
it started then we'll actually still see
the first row and this is kind of how
Postgres allows for high concurrency
basically every transaction or every
statement depending on your isolation
level which we'll get into you gets this
snapshot level associated with it and
that's a number and if that number is
within the bounds of a different row
version then it gets to see that but if
it's not it doesn't get to see it so
this allows us to not just have an
entire lock over the database whenever
we're doing your write operation so how
does Postgres actually model the version
chain internally so what we just saw is
like how MVCC has implemented a
high-level but let's look into like how
Postgres actually implements it so
instead of seeing begin and end
timestamp for every row you for every
version of every row you basically have
an X min and an X Max in Postgres and X
stands for transaction to kind of drop
the T there and really it's a
transaction ID so instead of so X min
really stands for the lower bound of the
transaction IDs that are allowed to see
this row and x max stands for the upper
bound of transaction IDs that are
allowed to see this real and instead of
a timestamp the transaction ID is a
monotonically increasing number
internally it's not exactly a sequence
like you would get when you run create
sequence but effectively it is the same
it's a 32-bit number so in integer just
clarify and it always gets incremented
and goes up so let's take a look under
the hood so in this example I create a
simple table with one field in it I
which is an integer I insert some
arbitrary row into it and I select it
out and when I in my select statement
you'll see that I select for xmin xmax
and then all the columns that are
available in the schema to the asterisk
and what we get back is actually x-min
and x-max so these are kind of like
hidden feels that Postgres is keeping
with your row version but it only shows
you the latest version so first I do is
select after the insert
I see that the X min is 557 the x max is
0 so 0 here is basically Postgres is
representation of infinity and we'll
talk about why shortly but then what
after I run an update on this row
through Postgres just through the repple
and i actually ran this and just copy
pasted it and i set the value to 2
instead of 1 which was initially
inserted and i select again i see that
the x-men's gone to 58 so basically 558
was the transaction ID at that update
and this row is also not expired it's x
max equals 0 and we'll talk about that
now
so basically every transaction row every
version row has a an x max which is
initially set to 0 and then if somebody
want if a transaction wants to expire
that x max it uses a protocol like
compare and swaps you may be familiar
with the processor instruction compare
and swap basically you have and you want
to change a value a variable to a to a
new value but when you're changing it
you want to make sure you do it in a way
that you're not changing something you
didn't expect so you tell this function
I want to change the value from 2 to 3
and if it looks and the value is 3
already or than the value is 4 it won't
change it back to 3 it'll only only
change it to 3 if the values 2 if you
ask it to compare and swap 2 to 3 so
Postgres really when you're trying to
expire a certain version each
transaction issues that compare and swap
from 0 to the expiry time so that if
another transaction tries to expire
something before another transaction one
of the compare and swaps will fail
because the value has been changed in
the meantime
so this is how Postgres kind of handles
race conditions on the expiry part which
is especially important if you have
multiple cores available for your
database so caste is also used to
implement like user space mutexes and
tons of things in the operating system
so this is pretty much an overview on
how MVCC works but we didn't cover one
really important part which is vacuums
so who here has heard of vacuums in the
context of Postgres ok great
so a lot of people
seems like roughly 50% a lot of people
have heard of vacuums not everyone knows
exactly what they should do most people
who've been administrating Postgres
should know that they need to run them
regularly though Leica Dyson a vacuum is
very powerful has many options and we
need to know what the difference between
them are to use them effectively unless
we go to Postgres defaults which we'll
talk about soon so what happens if we
just take out two vacuums from the
equation of our Postgres server which is
in production well if you look online
there's a picture called auto vacuum and
you'll recommend you'll see some people
recommend to even turn it off or tweak
it or something like that and Stack
Overflow posts let's hear what would be
bad or why would we be bad if you remove
vacuums altogether so really there are
two possibilities when you remove
vacuums they're gonna run out of disk
space or you're gonna run into a really
weird stuff after you run a number of
quarries and we'll explain both of those
now so first off in MPC C we should we
show that when you delete when you
update etc all you're doing is inserting
more and more rows into this version
chain that's assembled by Postgres
behind the scenes so what happens to
those old versions like if there are a
ton of open transactions and we need to
know if anyone's using it before we
clear a row this process is not
happening each time you commit a
transaction it basically happens in a
routine batch process called vacuums and
at a high level the process of a vacuum
is rather simple but it's it's an it's
necessitated by MVCC uses the
concurrency control mechanism so
basically what a vacuum does is it goes
and looks okay what's the lowest open
transaction ID in your database here
look at the lowest x ID now delete all
the versions where that lowest x ID is
above the x max of those version rows
because all those versions are not
visible to the oldest transaction your
database so they're no longer relevant
the vacuum sweeps them up as reclaims a
ton of disk space and if you don't
vacuum if you take it out of the
equation then you're basically going to
keep everything if you keep everything
you're probably gonna run out this space
but something else can actually happen
if you keep everything and this isn't
quite as expected
so basically theoretically how many
transactions does anyone know roughly
how many transactions we can have open
at a given time
it's just that should be a question
moral hacia so X lady is an innate right
we need the minus 1 there but yes we can
have 2 to 33 minus 1 transactions open a
time this is theoretically of course
most likely you'll run out of memory if
you try to do this because work ma'am
there's other constraints theoretically
this is the limit but it does kind of
batter what happens when you keep one
transaction around like say X ID equals
2 and this transactions never closing
its up for days maybe months and though
4 million transaction IDs sounds pretty
high when you have an incrementing
number let's say it's open for months
just sitting there as an idle connection
you have no sorts of timeouts in your
system eventually if we hit transaction
ID 4 million because we need to keep
incrementing the number will officer a
pre-owned at 0 and then we'll get
transaction ID 0 again but if you have
transaction ID say 2 or 12 or any number
less than 4 trillion
open still the vacuum won't be able to
clear the rows older so a you're gonna
have lobbed it space if you don't run
out of disk space in time you're going
to have really weird situations when you
have transaction IDs that are not
monotonically increasing because they
wrap around at 0 and you have things
above 0 already so if your ex IDs wrap
around you're in a soup I've seen
numerous post mortems online can look
them up for pretty big companies another
fault like sentry Joyent that have just
had outages due to transaction IDs
wrapping around so basically I have a
certain point
Postgres will start to try to kick in
vacuums at that point you need to pretty
much you have two choices you accept
data loss because something's weird is
gonna happen when you wrap around your
transaction IDs since the whole idea of
concurrency is lost the whole idea of
mvcc's guarantees is lost since it
relies on monotonically increasing
numbers
or you have to stop the world kill all
your open transactions let the vacuum
finish and get back up and that's
basically what happened in both of these
cases in the century Android and the
post-mortems are online so if you don't
have like another cluster to failover to
or something like that you're pretty
much in a soup and you have to you take
a step back except outage or data loss
I've actually seen this error message
but luckily is in staging basically if
you open up P sequel at this time when
you have really high X I DS and run any
query you will get warnings over and
over saying that your XID is going too
high and goes higher you're gonna hit
wrap arounds we're trying to run vacuums
it's blocked on out of other things
please call your transactions Montanez
words of course but we'll see something
for that message and at that point you
need to take action if you run a
Postgres server there's another talk on
monitoring but this is very important to
monitor because if your vacuums are not
running in a timely fashion you're
definitely gonna run into a problem
because of it so Postgres 9 as a feature
called Auto vacuum you should use it if
you can if you don't know about or if
you've been running Postgres for a long
time you haven't been worrying about
vacuums you're likely using it because
it's on by default basically it uses
table statistics that are periodically
updated to know when it has a certain
number of dead rows and when it should
start cleaning up this works well for
most all use cases but if you're afraid
of locks there's a number of flags that
we can tweak on it to do different
things have different aggressive days
etc which is really depending on your
workload for example if you have like an
update heavy workload depending on how
slow you're ok with things being you may
not want to run vacuums aggressively or
maybe you want to run them in the night
or something like that
when you don't have users up so just
like a house
if you don't vacuum routinely things
will pile up garbage will pile up in
this case and you're gonna regret it but
just as power so powerful as a normal
Dyson vacuums we have the auto vacuum in
post curse 9 and you should really use
it if you can so we talked a lot about
MVCC and how it allows for highly
concurrent workloads which is one of
like the huge advantages of post
you've got caught talking about
concurrency but it comes with a lot of
complexity for sure because it's a lot
harder to reason about mentally then say
the two phase locking approach where you
just acquire locks everyone else waits
your release the locks everyone else
proceeds so the performance gain is
worth it but in databases we care about
performance and guarantees so let's talk
about what kind of concurrency control
guarantees we get with MBC see what more
we have to think about and what's still
upheld so the golden question that
everyone asks if they're you know
managing a database or writing an
application that has a heavy concurrent
workload is how do I prevent race
conditions most people think or many
users of databases especially people who
are new to them think that if you use
transactions you saw of race conditions
you want to do something in a isolated
view of the database open that
transaction run some queries no one
interferes right well with MVCC this
isn't really always the case with a lock
free concurrency control mechanism it
really depends um out of the box things
may not really work as you expect if you
think that all race conditions are
solved by transactions even at the
database level on your application level
then you may have some race conditions
right now in production so it really
depends on your transaction isolation
level which is something that we
mentioned a few times earlier and now
we're gonna go into what it really means
so yeah if you're unfamiliar with this
term
you're probably subject to various
conditions already com due to the
defaults and these race conditions may
or not matter in your apps in any many
apps they don't matter the defaults are
pretty same
so in sequel just like in the ANSI
sequel spec we have four levels of
transaction isolation
there's read uncommitted there's read
committed it's a repeatable read and
serializable will go into each of these
but to start off Postgres does not allow
read uncommitted basically read
uncommitted allows for dirty reads just
means you read everything if you're
using that it probably shouldn't have
transactions in the beginning excuses
not allowed this but by anti sequel
specification
I thought I may cover it just to clarify
that there's no chance of dirty recent
uh stress so what does read committed
the lowest and default isolation level
and Postgres really guarantee us so
basically what read committed means is
when you open a transaction each very
statement of the transaction so if you
have three statements they each get a
certain snapshot of the database they
each get to see a certain snapshot of
the database and that's now a chart that
they see is based on the start time of
those those statements so you don't have
like one X ID for the whole transaction
if you're using read committed every
statement gets to see the most recently
committed data so this allows for like a
variety of race conditions and anomalies
when you're you're using dated data
especially in the highly concurrent
application rate so a since every single
statement your transaction get to see
the latest view of your database you can
have non-repeatable reads which means
one transaction may produce a result one
statement in transaction may reproduce
the results based on data that was no
longer there in the third statement and
that may or may not be a problem again
for your application we'll talk about
some ways it could be problems another
way is another problem as phantom reads
you can have you can be reading data and
one statement in transaction it could be
deleted later in the transaction and the
last way is just serialization anomalies
and this one's pretty tricky to
understand so we'll go over in its own
host slide so the next level of
transaction isolation and post graphs is
called repeatable reads and basically
what repeatable breed's does is it
extends upon read committed and it says
okay most pieces how most people think
about transactions if they they haven't
actually looked too much into buskers
basically when you open a transaction
you get a certain state of the database
a certain snapshot which correlates to
certain minimum turns that transaction
ID and all statements in the transaction
see data after that point see data from
from that point in before and this still
allows for one type of anomaly which is
more complicated and really depends on
your application but it removes the the
possibility of things like phantom reads
or non-repeatable reads like we
discussed
read committed so with read repeatable
reads you can read the same thing twice
and you'll get the same result well
there still is an anomaly so basically
imagine me at this scenario and this is
a textbook example in Postgres Docs and
a second quadrant blog post if I recall
I'm and many other places but imagine we
have a bank that needs to verify that
the sum of all your accounts balances is
over zero so basically we have and now
imagine that we have two transactions
that run in parallel so if we just
needed to verify that one account
balance is over zero we can set it come
straight in the database and we can say
this value needs to be greater than zero
but when we have two it's not that
simple
so we have to make sure that our
application is not vulnerable to race
conditions especially for something like
a bank so basically here transaction one
reads both accounts values and says okay
I want I it's gonna be above zero if I
remove 100 from account one and then
transaction two married both accounts
values and say okay it's gonna be above
zero if I remove from account two now
since transaction one and two are
updating two completely separate rows
they there's no conflict when Postgres
looks at it so there's one transactions
reading two rows updating one of them
other transactions reading two rows up
being the other one those rights don't
conflict with each other because they're
completely separate rows but in your
application you actually have a
guarantee that you need to uphold which
is that the sum of all account balances
is over zero so Postgres will update
both rows and you can end up in a state
where the sum of both balances is zero
because it's not enforced by a
constraint or anything like that so
there are a variety of ways to handle
this case
hey you can solve it by explicitly
locking right you can lock the row but
if you're gonna lock the row by yourself
you're not really getting the benefit of
the databases concurrency control
mechanisms because it's supposed to
provide like a good abstraction right
you can also like enforced in your
application you you just make sure that
you don't update both the count balances
in at one time and maybe do this outside
Postgres maybe inside Postgres with some
sort of write dependency but the easiest
way by far is to enable
the serializable transaction isolation
level which is the highest level
available Postgres and the sequence back
actually and it's a one above read
repeat repeatable reads basically
transaction the transaction isolation
level of serializable is deterministic
when you execute transactions serially
and concurrently are guaranteed to get
the same result
it never sacrifices anomalies their
isolations for the chance of some sort
of anomaly that we talked about earlier
like serialization anomalies like in the
example of this Bank um but it does come
with the downside and that's why it's
not the default basically what happens
is when you turn on the serializable
isolation level Postgres will start
looking at the pattern of your queries
in the transaction so one transaction
reads two rows and then writes to row
another transaction reads two rows and
rights to a different row it's gonna
know those to read two rows before they
wrote if the state of that one of those
rows a dreads Rach read change in
transaction one and transaction to you
is looking at the rows those rows before
writing then maybe there's a write
dependency there may be what it read
affects what it writes so if two things
do that Postgres will have to abort one
of them so this means that sometimes you
may have transactions aborted even when
it doesn't matter in your application
like maybe you don't really care about
that race condition of your application
but because you're in serializable in
the serializable isolation level you're
gonna have them aborted just because
Postgres thinks there may be a
dependency after all it doesn't know all
the rules in your database this isn't
very declarative that makes sense but if
you have a problem like this you're
probably better off going the
serializable isolation level and just
retrying when there are aborts rather
than risking anomalies because it's much
harder to track it the other way so the
reason it's not default just to clarify
is because of these frequent
serialization failures whenever you have
a dependency violated and another thing
you really have to make sure whenever
you use serializable isolation level is
because of these frequent aborts
you should never consider your data good
until it's committed now you should
really never do this anytime but if
we're using serializable it's a common
case it's not an edge case it's not like
upon failure your transaction will be
abhorred
like your transaction can be aborted
during normal concurrency um so without
understanding transaction isolation you
can fly by ninety part 95% of the time
like mostly won't have any issues but
that 1% of the time that you do have a
race condition it's gonna be very
confusing just like this argument
imagine they're two developers one is
saying that this is possible one's not
it's very hard to say it because it's
not deterministic so if you have to
think twice about what transaction
isolation level to use and and or if you
have to think twice about whether
serializable anomalies matter in my case
and you're dealing with something very
crucial it's like a bank for example you
serializable and just be safe this is
actually a reason that a lot of banks
just use GPL or two-phase locking
but serializable is obviously a little
better than two-phase locking because
you don't lock all the time so you can
get a higher concurrency throughput here
but you do have that downswing sorry so
in summary of transaction isolation
levels it's a quick chart to help you
understand what isolation levels have
what issues so Reed committed the lowest
level three levels of isolation errors
this is usually fine but just don't
assume that transactions are the magical
solution to all race conditions it's a
very common beginner mistake repeatable
reads gets rid of like almost all race
conditions out most applications have
but if you're having really serious ones
which are right dependencies that you
can't express the database then you
serializable so MVCC and isolation are
the most important parts of concurrency
in Postgres by far if you don't
understand those two you're vulnerable
to to many many issues in the long run
but there are some other things in
Postgres that are good to understand if
you're administering Postgres especially
in a database which has high concurrency
and high throughput as you're scaling
buskers so we'll discuss those at a
little high level so first off we've
been acting like postcards doesn't have
locks this whole time though MVCC itself
is a lock free concurrency control
protocol there are locks in Postgres
they're light locks that are taken for
various operations under the hood
but Postgres basically hides this locks
it doesn't make them like the forefront
of your concurrency control like a TPL
american to pilot mechanism but they're
still important to understand if you
face some very odd issues so you don't
need to be an expert in the taxonomy of
locks basically different locks have
different levels different operations
take different locks this is something
that you can pretty much view
documentation for pretty regularly if
you face any issue that seems like
something's waiting too long on another
thing but you should know where to look
so there's basically two places to look
if you're running at you a case like
this first off is a PG stat activity
it's a a view a system in a system table
that's in post stress just comes with
that you query it you can see all the
current inquiries and one of the fields
is waiting that'll be a boolean if your
query is waiting on a some sort of LOC
dependency it'll be true there if not
it'll be false so that's the first thing
to see is my issue related to locks or
things just slow if your issue is
related flocks start digging into PG
locks which is like your best friend
that's on call you just need them
sometimes they're very easy to reach
it's just a system view that it comes
with Postgres and it can express the
lock dependencies between all walks in
your system there's a little cumbersome
to query there's great quarries you can
copy/paste in the wiki for this for very
common operations so one thing that you
need to keep in mind when you're dealing
with locking in general is the presence
of something called a deadlock so
whenever you have walking you have the
possibility of deadlock that's
inevitable the question is like how do
you tackle that deadlock how do you make
sure it doesn't stop the world right so
what is a deadlock so imagine we have
transaction one transaction to you
transaction one wants to update object
one then object to transaction two wants
the object update object to an object
one so first transaction we want to
update object one next transaction to
object updates object two and then they
try to update the ones that the other
one just updated so at this point
they're both waiting on each other
transaction two is waiting on the lock
waiting on transaction once lock to
release and vice versa so without any
deadlock protection we're gonna wait
forever
since both grace is an optimistic
concurrency control protocol and it
assumes this is not the normal place and
resolving deadlocks is slower is is the
slower path it's not the faster path
like in a tu piel mechanism where you
just don't have that locks but you just
make it everything else slow so how it
works this Postgres really makes a it
augur a statement waits on a lock for
over a second and that time period is
configurable Postgres will generate a
graph of walk dependencies between each
other and if it finds a cycle it will
say okay there's a deadlock here
I'll kill one of them and the other will
proceed then you retry a transaction or
something like that in your application
but it waits for a second before doing
this because there's a performance
penalty for just doing it all the time
so post ego students that since
deadlocks are not like the normal case
will prioritize your normal application
case and then just check for deadlocks
after so that's why there's the
one-second delay a pessimistic
concurrency control protocol we just
make sure deadlocks don't happen but
impact performance of everything
regardless of the application has
deadlock so not so another trick that
you can use which isn't related to dead
locks or locks in general but well it
could be depending on your operation but
basically is on conflict keyword that
was introduced in Postgres nine I
believe so if you use post Chris eight
and you can't use this one but basically
it means that when you do an insert
operation you can say on conflict a
variety of operations you can do nothing
you can update the value that's inside
the database etc you can use this to
emulate absurd operations but another
good use case for it is just not having
aborted transactions due to you maybe
some sort of at least once delivery in
your pipeline so imagine you have a
queue that has at least ones guarantees
you read a message from the queue you
make a pretty post-arrest query if that
idea appears once and you're trying to
insert it then at least you won't have
to handle errors like a board
transaction or sorry like you want up to
handle conflict there or something like
that which is harder to decipher by
in this case post Chris will just
succeed because the values already there
if he is do nothing so this is pretty
useful when you have at least once
deliveries but it's also useful for
upsets yeah and it's really useful for
batch operations because say you have
since every operation in Postgres or any
transactional database needs to be
atomic like I do they're fully succeeds
or fully fails if you're inserting like
I don't know a thousand records at once
you don't want to have to retry nine
hundred of the records and decipher
which one had a conflict just because
one of them has a conflict you want to
just be able to tell Postgres okay if
one of them has a conflict and I don't
care about inserting that one ignore it
it's a lot easier so that's all for this
talk thank you very much we do have five
minutes so I'll be open for questions if
you're interested in talking more about
postcards concurrency or anything for
that matter come talk to me afterwards I
also work at a startup called segment
which is a data infrastructure company
and if you're interested in that also
reach out any questions it's a read
committed so it's the lowest transaction
isolation level that post-course offers
again it does have like a variety of
anomalies that you can face it's
important to understand these anomalies
well applications won't want to be
vulnerable to them just do you do there
we're good but if you are vulnerable to
them and you started seeing the effects
of them without understand the cause
just trust me they're really hard to you
decipher what's going on so any other
questions
Oh actually could you go up and ask the
question there sorry forgot to ask that
before yeah it's recording so I'm an RTS
user mm-hmm so am I my in a little
monitoring my transaction IDs are going
up and up and up and up when did those
reset and I guess I need to be concerned
about it by the time it hits the around
2 billion so fortunately in this like
the limit here but you should be
concerned if they're going up in general
and they're not ever flashing back to
zero yeah
um or a low number I mean I'll see them
exactly at zero sorry
yours can have something yet not not
zero but if they're continuously going
up that is worrisome over a long period
of time that means that you probably
have something either either you have
something running that's just not being
killed or like some open transaction
that's just running for a long time and
you should look in PG side activity to
see if you have that or you have you're
just not vacuuming at all like backings
off I thought it's a 2 billion sorry 6
million is good sorry I you said 2
billion if you have low throughput in
general you may not hit this for very
very long time especially if you just
loaded a bunch of data in Postgres are
just reading you're not gonna hit this
threshold for a long time but do you be
concerned if you do hit the threshold
and and go above it for a very long time
and the vacuum is just running and it's
waiting because even if it fires up auto
vacuum and you're doing something like
dropping an index and it's blocked on
something else you can render situations
where you need to make sure you kill
those other transactions that are
blocking the process otherwise you'll
hit wraparound in other issues cool
thanks
okay we're all out of time here but feel
free to ask me for any questions
afterwards
[Applause]