WEBVTT 1 00:00:00.320 --> 00:00:02.170 Hi guys, just a quick video to tell you 2 00:00:02.170 --> 00:00:04.240 what the differences are between connections 3 00:00:04.240 --> 00:00:05.520 and transactions. 4 00:00:05.520 --> 00:00:08.220 We visited these words many times 5 00:00:08.220 --> 00:00:09.920 while going through this course, 6 00:00:09.920 --> 00:00:11.976 but I just wanted a lecture where we actually 7 00:00:11.976 --> 00:00:15.730 explain what the differences are in detail. 8 00:00:15.730 --> 00:00:19.680 So a connection, is one thread of communication 9 00:00:19.680 --> 00:00:23.710 between a client our, Python apps and the database server 10 00:00:24.650 --> 00:00:27.650 and one connection is a single thread. 11 00:00:27.650 --> 00:00:31.260 Therefore a connection can be either sending 12 00:00:31.260 --> 00:00:35.980 or receiving data related to one query at a time 13 00:00:35.980 --> 00:00:36.813 and what this means is 14 00:00:36.813 --> 00:00:40.190 connections cannot handle two queries simultaneously 15 00:00:40.190 --> 00:00:43.310 they can handle two queries one after another very quickly 16 00:00:43.310 --> 00:00:45.673 but not at the exact same time. 17 00:00:47.150 --> 00:00:50.220 A transaction though, is something else. 18 00:00:50.220 --> 00:00:53.820 In PostgreSQL all queries run within a transaction 19 00:00:53.820 --> 00:00:55.500 and the transaction has a life cycle 20 00:00:55.500 --> 00:00:57.530 and the life cycle is that it starts, 21 00:00:57.530 --> 00:01:00.248 it runs the queries it contains and it ends 22 00:01:00.248 --> 00:01:02.470 and at the end as we know already, 23 00:01:02.470 --> 00:01:04.900 they can be committed in which any changes 24 00:01:04.900 --> 00:01:06.450 are saved to the database 25 00:01:06.450 --> 00:01:07.930 or they can be rolled back, 26 00:01:07.930 --> 00:01:10.340 in which case the changes that it produced 27 00:01:10.340 --> 00:01:12.583 are not applied to the database. 28 00:01:13.670 --> 00:01:17.030 Transactions are linear and synchronous. 29 00:01:17.030 --> 00:01:20.160 So the queries run from top to bottom, one by one 30 00:01:21.390 --> 00:01:25.090 and also similarly to this transactions within a connection 31 00:01:25.090 --> 00:01:27.090 also run one by one, 32 00:01:27.090 --> 00:01:28.000 so you can think of it 33 00:01:28.000 --> 00:01:30.750 as somewhat a container based approach. 34 00:01:30.750 --> 00:01:33.090 Connections contain transactions 35 00:01:33.090 --> 00:01:35.440 and transactions contain queries 36 00:01:35.440 --> 00:01:37.750 and in both cases they run one by one 37 00:01:37.750 --> 00:01:40.350 so a transaction executes one query at a time, 38 00:01:40.350 --> 00:01:44.020 a connection executes one transaction query at a time. 39 00:01:44.020 --> 00:01:46.510 Remember that when we learned about acid, 40 00:01:46.510 --> 00:01:49.580 transactions can't interact with the data 41 00:01:49.580 --> 00:01:52.210 that each other produces until the transaction 42 00:01:52.210 --> 00:01:54.310 has been saved or committed. 43 00:01:54.310 --> 00:01:57.370 So if you need to review what acid means 44 00:01:57.370 --> 00:01:59.220 and how this affects transactions, 45 00:01:59.220 --> 00:02:00.550 please do check back on that lecture 46 00:02:00.550 --> 00:02:01.760 because it's pretty important 47 00:02:01.760 --> 00:02:04.160 on how transactions work in PostgreSQL. 48 00:02:04.160 --> 00:02:06.550 How do you run query simultaneously then 49 00:02:06.550 --> 00:02:08.550 if you can only run one query 50 00:02:08.550 --> 00:02:10.220 at a time per transaction 51 00:02:10.220 --> 00:02:12.760 and one transaction at a time per connection, 52 00:02:12.760 --> 00:02:13.720 that means that it's impossible 53 00:02:13.720 --> 00:02:14.811 to run queries simultaneously. 54 00:02:14.811 --> 00:02:18.410 Now, any client can open multiple connections 55 00:02:18.410 --> 00:02:20.720 to a database if they wanna have multiple threads 56 00:02:20.720 --> 00:02:21.810 of communication open 57 00:02:21.810 --> 00:02:23.710 and we've done this with our connection pooling 58 00:02:23.710 --> 00:02:26.330 we had 10 connections open at a time 59 00:02:26.330 --> 00:02:28.130 and we weren't using all of them at a time, 60 00:02:28.130 --> 00:02:30.860 but you could, you could have for example, 61 00:02:30.860 --> 00:02:34.030 one connection to handle writing new data to the database 62 00:02:34.030 --> 00:02:37.230 and then potentially 10 connections to handle reading data. 63 00:02:37.230 --> 00:02:41.660 Imagine, for example, Twitter with their millions of users, 64 00:02:41.660 --> 00:02:43.790 they have millions of connections 65 00:02:43.790 --> 00:02:47.220 or however many simultaneous users are using Twitter. 66 00:02:47.220 --> 00:02:48.930 They have that many connections because 67 00:02:48.930 --> 00:02:52.830 they need one per user in order to handle retrieving data 68 00:02:52.830 --> 00:02:55.430 from the database for each person. 69 00:02:55.430 --> 00:02:57.070 Remember, that how many connections 70 00:02:57.070 --> 00:02:58.900 you wanna open requires careful thinking 71 00:02:58.900 --> 00:03:02.930 and compromises as we saw in the connection pooling lecture. 72 00:03:02.930 --> 00:03:06.240 Although Twitter has millions of daily active users, 73 00:03:06.240 --> 00:03:07.810 there's a lot of seconds in one day 74 00:03:07.810 --> 00:03:10.250 and most people are not permanently on Twitter. 75 00:03:10.250 --> 00:03:11.820 They're also probably closing 76 00:03:11.820 --> 00:03:13.420 and reusing connections all the time 77 00:03:13.420 --> 00:03:16.260 when users go inactive in the website, for example. 78 00:03:16.260 --> 00:03:18.960 So there's a lot of performance compromises 79 00:03:18.960 --> 00:03:20.450 and things that you have to think about 80 00:03:20.450 --> 00:03:22.430 when handling these connections. 81 00:03:22.430 --> 00:03:23.880 All right thank you guys for joining me. 82 00:03:23.880 --> 00:03:25.010 Thanks for watching this video. 83 00:03:25.010 --> 00:03:26.100 I hope you've learned something 84 00:03:26.100 --> 00:03:27.750 and I'll see you in the next one.