1
00:00:00,060 --> 00:00:06,930
‫Welcome to another Q&A session for the month of February twenty twenty two.

2
00:00:07,650 --> 00:00:15,390
‫Today we have another interesting question that would like to answer in details and comes from Yash

3
00:00:15,570 --> 00:00:16,230
‫Dixit.

4
00:00:16,560 --> 00:00:23,130
‫And it's on the database connection point lecture when, if you don't know, guys don't connection bombings.

5
00:00:23,130 --> 00:00:32,670
‫The idea of having a pool of a number of connections to the database and have the back and pick a connection,

6
00:00:33,330 --> 00:00:33,760
‫right?

7
00:00:33,780 --> 00:00:36,900
‫Execute the sequel and then release that connection back to the pool.

8
00:00:37,830 --> 00:00:42,410
‫So Yash is asking, why do we need this many connections?

9
00:00:42,420 --> 00:00:45,140
‫Why can't we use just one connection, right?

10
00:00:45,150 --> 00:00:47,580
‫And then execute all our queries to this connection?

11
00:00:47,700 --> 00:00:47,940
‫All right.

12
00:00:48,360 --> 00:00:49,110
‫That's really the question.

13
00:00:49,740 --> 00:00:54,270
‫What if I use one connection and keep it there in the database as a class, I can reuse that connection,

14
00:00:54,280 --> 00:00:54,540
‫right?

15
00:00:54,590 --> 00:00:55,140
‫Is this matt?

16
00:00:55,740 --> 00:00:58,980
‫The answer is yes, it is really bad.

17
00:00:59,190 --> 00:01:02,850
‫So the first reason is it's concurrency.

18
00:01:03,120 --> 00:01:05,520
‫You're using one resource on your back end.

19
00:01:05,970 --> 00:01:06,150
‫Right?

20
00:01:06,210 --> 00:01:07,500
‫One database connection.

21
00:01:08,370 --> 00:01:13,650
‫And you have multiple queries fighting over this single resource.

22
00:01:13,650 --> 00:01:23,040
‫So the operating system needs to handle the locks and the latches on this DCP connection.

23
00:01:23,200 --> 00:01:25,290
‫And so you have a lot of clients.

24
00:01:25,290 --> 00:01:30,990
‫And when I say client here, especially if this is a web server, that's really bad because you have

25
00:01:30,990 --> 00:01:34,020
‫many, many users executing get requests.

26
00:01:34,320 --> 00:01:41,430
‫And if all of these are competing on this TCP connection, then you'll have blocking at that level.

27
00:01:41,760 --> 00:01:45,030
‫So that is one reason, but it's not that bad.

28
00:01:45,660 --> 00:01:50,100
‫The really bad thing is we're using TCP here.

29
00:01:50,190 --> 00:01:50,970
‫So what does that mean?

30
00:01:51,810 --> 00:01:56,020
‫It means TCP is not a request response system, guys.

31
00:01:56,550 --> 00:02:04,110
‫It's it's a bi directional protocol means if you want to send a query, right, what do you need to

32
00:02:04,110 --> 00:02:04,230
‫do?

33
00:02:04,260 --> 00:02:09,910
‫You can use it to package your sequel to a bunch of strings and then put it in there TCP segment.

34
00:02:10,140 --> 00:02:10,410
‫Right?

35
00:02:10,470 --> 00:02:13,090
‫If you're interested, I talk about this stuff in my in your next course.

36
00:02:13,110 --> 00:02:16,530
‫Check that out right here to enjoy next RSA and also dot com.

37
00:02:16,980 --> 00:02:24,330
‫Learn more and you package this and you're going to get one TCP segment or more based on them to you,

38
00:02:24,330 --> 00:02:26,430
‫the maximum transmission unit and all that stuff.

39
00:02:26,430 --> 00:02:32,640
‫So you'll end up with too many segments and you're going to ship those segments right to the back end

40
00:02:32,640 --> 00:02:33,450
‫to the database.

41
00:02:33,450 --> 00:02:38,440
‫In this case, which is the server and the server will read these segments.

42
00:02:38,460 --> 00:02:39,990
‫Don't, don't, don't, don't, don't.

43
00:02:40,140 --> 00:02:44,400
‫And then add the application layer will understand, Oh, I just got a query.

44
00:02:44,820 --> 00:02:45,720
‫Well, let's wait.

45
00:02:46,230 --> 00:02:47,400
‫And the less execute this way.

46
00:02:48,720 --> 00:02:49,050
‫And.

47
00:02:50,630 --> 00:02:57,170
‫Let's say you sent another queen in the same connection, again, DCP segments will do the data and

48
00:02:57,170 --> 00:03:03,190
‫the database will receive the another sets of DCP segment and you receive query number two.

49
00:03:03,230 --> 00:03:05,510
‫And so let's see what the current number two.

50
00:03:06,260 --> 00:03:12,620
‫So now let's say that query number two was faster than and one is just happens to be faster.

51
00:03:13,280 --> 00:03:14,270
‫It's executed faster.

52
00:03:14,470 --> 00:03:20,990
‫Quentin Bolt took longer, so query number two will execute, get the result and will be shipped to

53
00:03:20,990 --> 00:03:21,740
‫the client.

54
00:03:23,540 --> 00:03:29,630
‫How will the client know that this response is actually a belong to query number two?

55
00:03:30,290 --> 00:03:35,270
‫There is no tagging in TCP unless the protocol self tags itself.

56
00:03:36,710 --> 00:03:41,150
‫You are basically playing well, fire, so all of a sudden.

57
00:03:42,230 --> 00:03:49,700
‫What this translate to is if someone actually execute a query user1, they and the user to execute the

58
00:03:49,700 --> 00:03:56,660
‫same different queries almost at the same time user one might get the result of user two and user two

59
00:03:56,660 --> 00:04:01,100
‫might be the result of user one that is really bad.

60
00:04:01,490 --> 00:04:05,090
‫You really need to avoid that, if you know.

61
00:04:06,120 --> 00:04:13,320
‫And you are guaranteed that the protocol, which the database uses, guarantees that all the client

62
00:04:13,320 --> 00:04:18,720
‫will actually understand that oh, this the response that comes back to me is actually the response

63
00:04:18,720 --> 00:04:25,030
‫is tagged with with with the query I.D. or something like that.

64
00:04:25,080 --> 00:04:28,680
‫And as a result, if you know, if you trust that, go ahead.

65
00:04:29,190 --> 00:04:34,590
‫If you don't, then you are at the mercy of just really ordering problems.

66
00:04:34,920 --> 00:04:36,030
‫This is the same problem with it.

67
00:04:36,050 --> 00:04:42,120
‫Should you be one one which will solve what should be to right was the idea of streams.

68
00:04:42,480 --> 00:04:45,300
‫Each stream is basically a tag if you think about it.

69
00:04:45,630 --> 00:04:51,690
‫That tags the query that you send, and the response is tagged with the same stream so that we know

70
00:04:51,690 --> 00:04:57,300
‫that, oh, this query belongs to this stream and this response belongs to this stream, so we know

71
00:04:57,300 --> 00:04:58,430
‫which one is which.

72
00:04:58,470 --> 00:04:59,310
‫And as a result?

73
00:04:59,520 --> 00:04:59,810
‫Yeah.

74
00:05:00,210 --> 00:05:05,190
‫So as an idea, you either do pooling connection pooling, which we have today, you can especially

75
00:05:05,400 --> 00:05:06,840
‫establish 30 connections.

76
00:05:07,200 --> 00:05:13,230
‫And when you want to execute query, you need to execute one query for each GCP connection.

77
00:05:13,620 --> 00:05:15,180
‫You cannot execute, too.

78
00:05:15,840 --> 00:05:16,920
‫You can try.

79
00:05:17,780 --> 00:05:19,790
‫But you cannot guarantee the results.

80
00:05:20,230 --> 00:05:20,390
‫Right?

81
00:05:20,660 --> 00:05:25,610
‫Again, unless you know, the protocol pulls Ghost Protocol, Oracle or SQL Server the users, they

82
00:05:25,610 --> 00:05:27,230
‫can guarantee this ordering.

83
00:05:28,160 --> 00:05:28,670
‫They're sure.

84
00:05:29,180 --> 00:05:32,450
‫But what have happened if you have proxies in the middle, right?

85
00:05:33,290 --> 00:05:34,190
‫All bets are off.

86
00:05:34,310 --> 00:05:35,990
‫So avoid using it.

87
00:05:36,680 --> 00:05:38,510
‫Stay safe, as they say.

88
00:05:38,750 --> 00:05:39,920
‫Just do a pool of connections.

89
00:05:40,700 --> 00:05:41,150
‫Pick one.

90
00:05:42,410 --> 00:05:43,370
‫Execute a query.

91
00:05:43,640 --> 00:05:44,420
‫Get back the results.

92
00:05:44,420 --> 00:05:48,230
‫And the net said That's the best solution to do a pool of connections.

93
00:05:48,230 --> 00:05:54,230
‫And when you want to execute query, you pick a connection from the pool, you reserve it.

94
00:05:54,500 --> 00:05:56,840
‫So no one else can actually pick it.

95
00:05:56,840 --> 00:05:58,010
‫You execute the query.

96
00:05:58,250 --> 00:06:00,470
‫And then once you're done, you turn the response.

97
00:06:00,470 --> 00:06:02,300
‫You got the response on that connection.

98
00:06:02,720 --> 00:06:04,860
‫You return it back to the pool, right?

99
00:06:04,880 --> 00:06:07,520
‫If you don't return it, then you're going to run out of connections, obviously.

100
00:06:07,940 --> 00:06:14,660
‫So you treat it as resources, so you not only you, you you spread the resources right on multiple

101
00:06:14,660 --> 00:06:21,470
‫connections, but you also effectively solve the problem of out of order responses.

102
00:06:22,370 --> 00:06:26,600
‫And it's it's you got more concurrency as a result.

103
00:06:27,290 --> 00:06:31,220
‫The better solution, in my opinion, is to implement may be quick.

104
00:06:31,580 --> 00:06:37,010
‫The database level, but which I don't know any database that actually does that quake will have the

105
00:06:37,010 --> 00:06:40,430
‫ability to have streams on a single connection ran.

106
00:06:40,730 --> 00:06:46,570
‫This way you can tag a query with the stream and then will no response come back?

107
00:06:46,580 --> 00:06:53,810
‫We know that or that response that sets of TCP segments or click segments in this case belong to this

108
00:06:53,810 --> 00:06:55,340
‫particular response.

109
00:06:55,730 --> 00:06:56,150
‫All right, guys.

110
00:06:56,360 --> 00:06:59,450
‫That's it for me today because in the next one to enjoy the course.