-: Hi guys, just a quick video to tell you
what the differences are between connections
and transactions.
We visited these words many times
while going through this course,
but I just wanted a lecture where we actually
explain what the differences are in detail.
So a connection, is one thread of communication
between a client our, Python apps and the database server
and one connection is a single thread.
Therefore a connection can be either sending
or receiving data related to one query at a time
and what this means is
connections cannot handle two queries simultaneously
they can handle two queries one after another very quickly
but not at the exact same time.
A transaction though, is something else.
In PostgreSQL all queries run within a transaction
and the transaction has a life cycle
and the life cycle is that it starts,
it runs the queries it contains and it ends
and at the end as we know already,
they can be committed in which any changes
are saved to the database
or they can be rolled back,
in which case the changes that it produced
are not applied to the database.
Transactions are linear and synchronous.
So the queries run from top to bottom, one by one
and also similarly to this transactions within a connection
also run one by one,
so you can think of it
as somewhat a container based approach.
Connections contain transactions
and transactions contain queries
and in both cases they run one by one
so a transaction executes one query at a time,
a connection executes one transaction query at a time.
Remember that when we learned about acid,
transactions can't interact with the data
that each other produces until the transaction
has been saved or committed.
So if you need to review what acid means
and how this affects transactions,
please do check back on that lecture
because it's pretty important
on how transactions work in PostgreSQL.
How do you run query simultaneously then
if you can only run one query
at a time per transaction
and one transaction at a time per connection,
that means that it's impossible
to run queries simultaneously.
Now, any client can open multiple connections
to a database if they wanna have multiple threads
of communication open
and we've done this with our connection pooling
we had 10 connections open at a time
and we weren't using all of them at a time,
but you could, you could have for example,
one connection to handle writing new data to the database
and then potentially 10 connections to handle reading data.
Imagine, for example, Twitter with their millions of users,
they have millions of connections
or however many simultaneous users are using Twitter.
They have that many connections because
they need one per user in order to handle retrieving data
from the database for each person.
Remember, that how many connections
you wanna open requires careful thinking
and compromises as we saw in the connection pooling lecture.
Although Twitter has millions of daily active users,
there's a lot of seconds in one day
and most people are not permanently on Twitter.
They're also probably closing
and reusing connections all the time
when users go inactive in the website, for example.
So there's a lot of performance compromises
and things that you have to think about
when handling these connections.
All right thank you guys for joining me.
Thanks for watching this video.
I hope you've learned something
and I'll see you in the next one.