‫Welcome to another Q&A session for the month of February twenty twenty two.
‫Today we have another interesting question that would like to answer in details and comes from Yash
‫Dixit.
‫And it's on the database connection point lecture when, if you don't know, guys don't connection bombings.
‫The idea of having a pool of a number of connections to the database and have the back and pick a connection,
‫right?
‫Execute the sequel and then release that connection back to the pool.
‫So Yash is asking, why do we need this many connections?
‫Why can't we use just one connection, right?
‫And then execute all our queries to this connection?
‫All right.
‫That's really the question.
‫What if I use one connection and keep it there in the database as a class, I can reuse that connection,
‫right?
‫Is this matt?
‫The answer is yes, it is really bad.
‫So the first reason is it's concurrency.
‫You're using one resource on your back end.
‫Right?
‫One database connection.
‫And you have multiple queries fighting over this single resource.
‫So the operating system needs to handle the locks and the latches on this DCP connection.
‫And so you have a lot of clients.
‫And when I say client here, especially if this is a web server, that's really bad because you have
‫many, many users executing get requests.
‫And if all of these are competing on this TCP connection, then you'll have blocking at that level.
‫So that is one reason, but it's not that bad.
‫The really bad thing is we're using TCP here.
‫So what does that mean?
‫It means TCP is not a request response system, guys.
‫It's it's a bi directional protocol means if you want to send a query, right, what do you need to
‫do?
‫You can use it to package your sequel to a bunch of strings and then put it in there TCP segment.
‫Right?
‫If you're interested, I talk about this stuff in my in your next course.
‫Check that out right here to enjoy next RSA and also dot com.
‫Learn more and you package this and you're going to get one TCP segment or more based on them to you,
‫the maximum transmission unit and all that stuff.
‫So you'll end up with too many segments and you're going to ship those segments right to the back end
‫to the database.
‫In this case, which is the server and the server will read these segments.
‫Don't, don't, don't, don't, don't.
‫And then add the application layer will understand, Oh, I just got a query.
‫Well, let's wait.
‫And the less execute this way.
‫And.
‫Let's say you sent another queen in the same connection, again, DCP segments will do the data and
‫the database will receive the another sets of DCP segment and you receive query number two.
‫And so let's see what the current number two.
‫So now let's say that query number two was faster than and one is just happens to be faster.
‫It's executed faster.
‫Quentin Bolt took longer, so query number two will execute, get the result and will be shipped to
‫the client.
‫How will the client know that this response is actually a belong to query number two?
‫There is no tagging in TCP unless the protocol self tags itself.
‫You are basically playing well, fire, so all of a sudden.
‫What this translate to is if someone actually execute a query user1, they and the user to execute the
‫same different queries almost at the same time user one might get the result of user two and user two
‫might be the result of user one that is really bad.
‫You really need to avoid that, if you know.
‫And you are guaranteed that the protocol, which the database uses, guarantees that all the client
‫will actually understand that oh, this the response that comes back to me is actually the response
‫is tagged with with with the query I.D. or something like that.
‫And as a result, if you know, if you trust that, go ahead.
‫If you don't, then you are at the mercy of just really ordering problems.
‫This is the same problem with it.
‫Should you be one one which will solve what should be to right was the idea of streams.
‫Each stream is basically a tag if you think about it.
‫That tags the query that you send, and the response is tagged with the same stream so that we know
‫that, oh, this query belongs to this stream and this response belongs to this stream, so we know
‫which one is which.
‫And as a result?
‫Yeah.
‫So as an idea, you either do pooling connection pooling, which we have today, you can especially
‫establish 30 connections.
‫And when you want to execute query, you need to execute one query for each GCP connection.
‫You cannot execute, too.
‫You can try.
‫But you cannot guarantee the results.
‫Right?
‫Again, unless you know, the protocol pulls Ghost Protocol, Oracle or SQL Server the users, they
‫can guarantee this ordering.
‫They're sure.
‫But what have happened if you have proxies in the middle, right?
‫All bets are off.
‫So avoid using it.
‫Stay safe, as they say.
‫Just do a pool of connections.
‫Pick one.
‫Execute a query.
‫Get back the results.
‫And the net said That's the best solution to do a pool of connections.
‫And when you want to execute query, you pick a connection from the pool, you reserve it.
‫So no one else can actually pick it.
‫You execute the query.
‫And then once you're done, you turn the response.
‫You got the response on that connection.
‫You return it back to the pool, right?
‫If you don't return it, then you're going to run out of connections, obviously.
‫So you treat it as resources, so you not only you, you you spread the resources right on multiple
‫connections, but you also effectively solve the problem of out of order responses.
‫And it's it's you got more concurrency as a result.
‫The better solution, in my opinion, is to implement may be quick.
‫The database level, but which I don't know any database that actually does that quake will have the
‫ability to have streams on a single connection ran.
‫This way you can tag a query with the stream and then will no response come back?
‫We know that or that response that sets of TCP segments or click segments in this case belong to this
‫particular response.
‫All right, guys.
‫That's it for me today because in the next one to enjoy the course.