WEBVTT 1 00:00:00.320 --> 00:00:01.830 Hi Guys, and Welcome back. 2 00:00:01.830 --> 00:00:03.680 In this video we're going to talk about 3 00:00:03.680 --> 00:00:07.740 the very exciting topic of Locking in Postgres. 4 00:00:07.740 --> 00:00:10.250 Locking is a very important thing 5 00:00:10.250 --> 00:00:13.500 and something that can be pretty tricky to understand. 6 00:00:13.500 --> 00:00:16.820 And also debug if you encounter a locking problem 7 00:00:16.820 --> 00:00:18.260 in your database later on. 8 00:00:18.260 --> 00:00:19.820 So hopefully this lecture helps. 9 00:00:19.820 --> 00:00:22.430 So what does locking mean first of all? 10 00:00:22.430 --> 00:00:23.590 Well imagine when you go to the gym, 11 00:00:23.590 --> 00:00:24.830 you put a padlock on your locker 12 00:00:24.830 --> 00:00:27.320 to prevent anybody else from accessing your locker. 13 00:00:27.320 --> 00:00:28.153 Same thing here, 14 00:00:28.153 --> 00:00:30.600 but Postgres is locking access to data 15 00:00:30.600 --> 00:00:32.750 instead of your belongings. 16 00:00:32.750 --> 00:00:34.060 And when Postgres does this, 17 00:00:34.060 --> 00:00:36.070 we call it "acquiring a lock." 18 00:00:36.070 --> 00:00:37.650 I'm going to see this phrase a few times 19 00:00:37.650 --> 00:00:39.880 throughout this presentation. 20 00:00:39.880 --> 00:00:42.270 There's different types of locking in Postgres. 21 00:00:42.270 --> 00:00:44.690 The first one is table-level locking, 22 00:00:44.690 --> 00:00:47.280 where Postgres acquires a lock on a table 23 00:00:47.280 --> 00:00:49.410 to limit access from other connections 24 00:00:49.410 --> 00:00:52.200 and transactions into that table. 25 00:00:52.200 --> 00:00:53.840 There's also row-level locking, 26 00:00:53.840 --> 00:00:56.220 where it limits access to individual rows. 27 00:00:56.220 --> 00:00:58.220 And there's also advisory locking, 28 00:00:58.220 --> 00:00:59.860 where it suggests that 29 00:00:59.860 --> 00:01:02.670 you don't access the row or the table, 30 00:01:02.670 --> 00:01:04.600 but it doesn't actually limit anything. 31 00:01:04.600 --> 00:01:07.730 Advisory locking is mainly used by clients 32 00:01:07.730 --> 00:01:11.360 like ourselves rather than by the database itself. 33 00:01:11.360 --> 00:01:13.960 Let's take a look at table-level locking, first of all, 34 00:01:13.960 --> 00:01:16.300 since it's the major type of locking 35 00:01:16.300 --> 00:01:19.990 transactions acquire this lock while working on a table. 36 00:01:19.990 --> 00:01:22.430 And there's different types of lock. 37 00:01:22.430 --> 00:01:23.940 Because there are different types of lock, 38 00:01:23.940 --> 00:01:27.090 two transactions can potentially have different locks 39 00:01:27.090 --> 00:01:29.700 on the same table at the same time, 40 00:01:29.700 --> 00:01:31.940 if they don't conflict with each other, 41 00:01:31.940 --> 00:01:34.040 because locks can conflict with each other 42 00:01:34.040 --> 00:01:36.130 in different ways. 43 00:01:36.130 --> 00:01:38.750 So if one transaction tries to acquire a lock, 44 00:01:38.750 --> 00:01:41.850 but another transaction already has a conflicting lock, 45 00:01:41.850 --> 00:01:43.740 then the first one has to wait 46 00:01:43.740 --> 00:01:47.150 until the second one releases the lock. 47 00:01:47.150 --> 00:01:49.140 This is how it goes. 48 00:01:49.140 --> 00:01:52.520 These are the Postgres conflicting lock modes. 49 00:01:52.520 --> 00:01:53.560 Made this diagram for you guys. 50 00:01:53.560 --> 00:01:55.350 So I'll have it as a downloadable resource 51 00:01:55.350 --> 00:01:57.750 in the resources section of this lecture. 52 00:01:57.750 --> 00:01:59.830 You've got the current lock mode, 53 00:01:59.830 --> 00:02:01.890 so transaction one comes first, 54 00:02:01.890 --> 00:02:03.520 nobody has locked to the table. 55 00:02:03.520 --> 00:02:06.480 So transaction one comes around and acquires a lock. 56 00:02:06.480 --> 00:02:08.640 Locks get acquired for all sorts of reasons, 57 00:02:08.640 --> 00:02:10.830 whenever you want to update a table 58 00:02:10.830 --> 00:02:11.860 or insert something in it 59 00:02:11.860 --> 00:02:13.140 or delete from it, 60 00:02:13.140 --> 00:02:14.840 all sorts of different transactions 61 00:02:14.840 --> 00:02:16.750 acquire different types of Lock, 62 00:02:16.750 --> 00:02:19.590 for example, so that somebody can't come in 63 00:02:19.590 --> 00:02:22.070 and delete a row while you're updating it. 64 00:02:22.070 --> 00:02:24.420 So that's why the locks exist first of all, 65 00:02:24.420 --> 00:02:27.670 and transaction one comes around acquires a lock. 66 00:02:27.670 --> 00:02:31.300 And after it's done that it's currently processing the row, 67 00:02:31.300 --> 00:02:34.730 and transaction two, then tries to acquire a lock. 68 00:02:34.730 --> 00:02:36.530 So now I'm gonna show you guys 69 00:02:36.530 --> 00:02:39.020 what are the conflicting blocks. 70 00:02:39.020 --> 00:02:41.510 So let's say the transaction one comes around 71 00:02:41.510 --> 00:02:44.390 and gets the access exclusive lock, 72 00:02:44.390 --> 00:02:46.963 this is one type of lock that exists in Postgres. 73 00:02:48.160 --> 00:02:51.330 Well, if transaction one has access exclusive, 74 00:02:51.330 --> 00:02:52.700 and then transaction two comes around 75 00:02:52.700 --> 00:02:55.450 and tries to get another lock or acquire a lock, 76 00:02:55.450 --> 00:02:57.760 that is called access share. 77 00:02:57.760 --> 00:02:59.420 Then it can't do that, 78 00:02:59.420 --> 00:03:02.810 because access exclusive is basically the most powerful lock 79 00:03:02.810 --> 00:03:04.280 or the most exclusive lock. 80 00:03:04.280 --> 00:03:07.690 And therefore the transaction two simply has to wait. 81 00:03:07.690 --> 00:03:12.690 Why might transaction one get access exclusive to the table? 82 00:03:12.833 --> 00:03:13.990 Well for any number of reasons, 83 00:03:13.990 --> 00:03:16.110 and we've got those reasons detailed 84 00:03:16.110 --> 00:03:17.320 in the official documentation, 85 00:03:17.320 --> 00:03:21.070 which I'm linking in the resources section of this lecture. 86 00:03:21.070 --> 00:03:24.730 Similarly, we've also got row share, for example, 87 00:03:24.730 --> 00:03:27.300 and if transaction two tries to get row share 88 00:03:27.300 --> 00:03:28.430 into this table, 89 00:03:28.430 --> 00:03:29.973 it's also gonna have to wait. 90 00:03:30.960 --> 00:03:34.520 Since access exclusive is the most exclusive type of lock 91 00:03:34.520 --> 00:03:36.270 transaction two always has to wait 92 00:03:36.270 --> 00:03:39.040 if it wants to acquire a lock. 93 00:03:39.040 --> 00:03:41.770 And there you have all the different types of lock 94 00:03:41.770 --> 00:03:43.150 that are available in Postgres 95 00:03:43.150 --> 00:03:45.700 access share, row share, row exclusive, 96 00:03:45.700 --> 00:03:48.510 share update exclusive, share, share row exclusive, 97 00:03:48.510 --> 00:03:50.820 exclusive and access exclusive. 98 00:03:50.820 --> 00:03:52.490 Try saying that quickly. 99 00:03:52.490 --> 00:03:55.260 And so now let's look at how these interact. 100 00:03:55.260 --> 00:03:56.810 Let's say now that transaction one 101 00:03:56.810 --> 00:03:58.760 instead of access exclusive 102 00:03:58.760 --> 00:04:01.050 tries to get the exclusive lock. 103 00:04:01.050 --> 00:04:02.760 As long as transaction two 104 00:04:02.760 --> 00:04:04.570 only wants access share, 105 00:04:04.570 --> 00:04:07.620 which is the least exclusive type of lock, 106 00:04:07.620 --> 00:04:09.610 then this will be fine. 107 00:04:09.610 --> 00:04:10.650 So what this means is 108 00:04:10.650 --> 00:04:13.010 if transaction one gets the exclusive lock, 109 00:04:13.010 --> 00:04:15.610 transaction two can get the access share lock, 110 00:04:15.610 --> 00:04:17.340 but none of the other ones. 111 00:04:17.340 --> 00:04:20.910 If transaction one got the share row exclusive lock 112 00:04:20.910 --> 00:04:23.650 transaction two can get access share and row share. 113 00:04:23.650 --> 00:04:26.470 This table here is a good thing to remember. 114 00:04:26.470 --> 00:04:28.770 Not in a way that you have to memorise it. 115 00:04:28.770 --> 00:04:31.900 But that these locks which there's a few of them 116 00:04:31.900 --> 00:04:33.670 do interact in different ways 117 00:04:33.670 --> 00:04:35.990 and not always what you expect. 118 00:04:35.990 --> 00:04:37.980 And but it's important to know that 119 00:04:37.980 --> 00:04:39.520 depending on the type of lock 120 00:04:39.520 --> 00:04:41.860 that your transaction is trying to acquire, 121 00:04:41.860 --> 00:04:45.640 you might encounter some problems here or there. 122 00:04:45.640 --> 00:04:47.760 If you have a problem with locking in your tables 123 00:04:47.760 --> 00:04:49.840 that can be really difficult to debug. 124 00:04:49.840 --> 00:04:52.820 But when two transactions are conflicting, 125 00:04:52.820 --> 00:04:55.600 Postgres is pretty good at resolving the conflict, 126 00:04:55.600 --> 00:04:57.690 usually by making one transaction Wait, 127 00:04:57.690 --> 00:04:59.660 sometimes if there's a deadlock. 128 00:04:59.660 --> 00:05:01.300 We'll come To what that is in a moment, 129 00:05:01.300 --> 00:05:04.300 by cancelling one of the transactions. 130 00:05:04.300 --> 00:05:08.370 You can also manually lock a table if you want, 131 00:05:08.370 --> 00:05:10.130 you can do that with a lock command, 132 00:05:10.130 --> 00:05:11.540 and you can do this in a transaction 133 00:05:11.540 --> 00:05:12.920 and then at the end of the transaction, 134 00:05:12.920 --> 00:05:15.240 the lock will be released. 135 00:05:15.240 --> 00:05:17.700 I would not recommend manual locking 136 00:05:17.700 --> 00:05:19.580 as it can produce deadlocks. 137 00:05:19.580 --> 00:05:20.730 But this is how you would do it. 138 00:05:20.730 --> 00:05:23.570 If you do want to you just say lock table. 139 00:05:23.570 --> 00:05:27.330 And then with in keyword we say the mode 140 00:05:27.330 --> 00:05:29.390 or the type of lock that we want. 141 00:05:29.390 --> 00:05:32.710 Similar to table locking, we've got row-level locking, 142 00:05:32.710 --> 00:05:34.880 and we can lock individual rows 143 00:05:34.880 --> 00:05:37.150 locking rows is only for writing. 144 00:05:37.150 --> 00:05:41.350 So to transactions can always read from a locked row, 145 00:05:41.350 --> 00:05:44.010 but they locked through may not be writable. 146 00:05:44.010 --> 00:05:46.210 Row-level locking also happens automatically at times. 147 00:05:46.210 --> 00:05:49.550 For example, if you try to update or delete specific rows, 148 00:05:49.550 --> 00:05:51.560 those will be row locked 149 00:05:51.560 --> 00:05:53.950 so that two updates can't happen at the same time. 150 00:05:53.950 --> 00:05:55.710 Also, you can delete the row 151 00:05:55.710 --> 00:05:58.110 while it's being updated, et cetera. 152 00:05:58.110 --> 00:06:00.450 You can do row-level locking manually as well, 153 00:06:00.450 --> 00:06:02.210 but again, highly discouraged. 154 00:06:02.210 --> 00:06:04.410 You can just say select star from users, for example, 155 00:06:04.410 --> 00:06:06.130 where email is bluh. 156 00:06:06.130 --> 00:06:09.670 For update, the for update here is the lock. 157 00:06:09.670 --> 00:06:11.860 So we use the select statement for locking. 158 00:06:11.860 --> 00:06:14.130 This gets the for update lock. 159 00:06:14.130 --> 00:06:16.550 And now here comes the big bullet point. 160 00:06:16.550 --> 00:06:19.010 transactions that attempt to update, delete, 161 00:06:19.010 --> 00:06:21.910 select for update, select for no key update, 162 00:06:21.910 --> 00:06:24.750 select for share, or select for key share 163 00:06:24.750 --> 00:06:26.110 will be blocked. 164 00:06:26.110 --> 00:06:27.840 And then they have to wait. 165 00:06:27.840 --> 00:06:30.530 So these different types of row-level locks 166 00:06:30.530 --> 00:06:33.830 operate in the same way that table-level locks do. 167 00:06:33.830 --> 00:06:35.460 There's fewer types, 168 00:06:35.460 --> 00:06:37.370 but they still can conflict with each other. 169 00:06:37.370 --> 00:06:38.750 And essentially what happens is 170 00:06:38.750 --> 00:06:42.780 when you use a row-level lock like this, 171 00:06:42.780 --> 00:06:45.450 and then other transactions can't interact with the row 172 00:06:45.450 --> 00:06:46.460 in different ways. 173 00:06:46.460 --> 00:06:48.010 Depending on the lock that they want. 174 00:06:48.010 --> 00:06:51.180 This for updates manual row-level lock 175 00:06:51.180 --> 00:06:53.720 is often used so that the current transaction 176 00:06:53.720 --> 00:06:55.570 can update the row multiple times 177 00:06:55.570 --> 00:06:57.350 without fear of another transaction 178 00:06:57.350 --> 00:06:59.030 acquiring a conflicting lock. 179 00:06:59.030 --> 00:07:00.000 So here what you would do is 180 00:07:00.000 --> 00:07:02.970 you would select star from users for updates. 181 00:07:02.970 --> 00:07:05.030 And that would lock all the rows 182 00:07:05.030 --> 00:07:07.950 where the email is row for example dot comm 183 00:07:07.950 --> 00:07:12.420 nobody can then go in and update or delete this row. 184 00:07:12.420 --> 00:07:14.600 So you can be safe knowing 185 00:07:14.600 --> 00:07:16.520 that you can update this row many times 186 00:07:16.520 --> 00:07:18.870 because nobody else can come in and change it. 187 00:07:18.870 --> 00:07:20.910 But again, this is pretty niche stuff, 188 00:07:20.910 --> 00:07:23.160 so you don't have to worry about it all that often. 189 00:07:23.160 --> 00:07:25.210 Sometimes it does become important though. 190 00:07:25.210 --> 00:07:26.510 Let's have a look at deadlocks. 191 00:07:26.510 --> 00:07:28.150 Deadlocks are pretty simple. 192 00:07:28.150 --> 00:07:29.860 You can have transaction one 193 00:07:29.860 --> 00:07:32.750 that wants to have a lock that transaction two needs. 194 00:07:32.750 --> 00:07:36.080 And transaction two has a lock that transaction one needs. 195 00:07:36.080 --> 00:07:39.030 For example here let's see we've got transaction one 196 00:07:39.030 --> 00:07:41.490 with access exclusive on table one. 197 00:07:41.490 --> 00:07:42.810 And then you've got transaction two 198 00:07:42.810 --> 00:07:45.120 with access exclusive on table two. 199 00:07:45.120 --> 00:07:47.070 This is fine, they don't conflict at all 200 00:07:47.070 --> 00:07:49.260 because they're using different tables no problem. 201 00:07:49.260 --> 00:07:51.210 But then within the same transaction 202 00:07:51.210 --> 00:07:55.430 transaction one now once access exclusive on table two 203 00:07:55.430 --> 00:07:58.180 and transaction two once access exclusive on table one. 204 00:07:58.180 --> 00:08:00.120 So now transaction one is waiting on two. 205 00:08:00.120 --> 00:08:01.520 And two was waiting on one 206 00:08:01.520 --> 00:08:04.050 and you've got yourself a deadlock. 207 00:08:04.050 --> 00:08:04.940 What would happen here 208 00:08:04.940 --> 00:08:07.640 is Postgres would cancel one of the transactions. 209 00:08:07.640 --> 00:08:10.110 And so you would have to be paying attention to that, 210 00:08:10.110 --> 00:08:12.680 so that you can rerun the query to the transaction. 211 00:08:12.680 --> 00:08:13.520 If this happens. 212 00:08:13.520 --> 00:08:15.080 Again, it doesn't happen very often. 213 00:08:15.080 --> 00:08:16.700 And this is one of the biggest reasons 214 00:08:16.700 --> 00:08:19.950 why you should not normally do manual locking. 215 00:08:19.950 --> 00:08:21.550 Because when you start doing manual locking, 216 00:08:21.550 --> 00:08:24.080 especially in a large and complicated database, 217 00:08:24.080 --> 00:08:26.360 it can become quite dangerous. 218 00:08:26.360 --> 00:08:28.910 For more information, we've got the E-book 219 00:08:28.910 --> 00:08:30.950 and we've got a bunch of references in the E-book 220 00:08:30.950 --> 00:08:32.980 in case you're interested in learning more. 221 00:08:32.980 --> 00:08:34.860 But I would recommend just bookmarking it 222 00:08:34.860 --> 00:08:38.450 in case you need to learn more about locking later on. 223 00:08:38.450 --> 00:08:41.210 Postgres locking can bite you later on 224 00:08:41.210 --> 00:08:42.690 without you expecting it. 225 00:08:42.690 --> 00:08:45.080 So it's important that you know this exists, 226 00:08:45.080 --> 00:08:47.130 but you don't have to memorise all the modes 227 00:08:47.130 --> 00:08:49.910 and how they operate and all that stuff. 228 00:08:49.910 --> 00:08:51.130 All right, thank you guys for watching. 229 00:08:51.130 --> 00:08:52.600 Thanks for joining me in this video. 230 00:08:52.600 --> 00:08:53.670 I hope you find it interesting 231 00:08:53.670 --> 00:08:54.580 and you've learned something 232 00:08:54.580 --> 00:08:56.230 and I'll see you in the next one.