All Postgres Locks Explained | A Deep Dive

initially I wanted this episode to be about a Blog that notion has written and how they're starting to move to sharding and because of limitations in postgres uh freeze mechanisms but as I'm starting writing or talking about this article and so I was like you know I I have a little bit of a weakness when it comes to understanding uh postgres locking appears just so much that would conflict with each other when it comes to postgresp there are of course three level of logs table logs page and row level locks but then the table locks are is not really what do you think they are you know to me and mostly most dbas I can and I don't consider myself a pdba but if someone said hey acquire a table look that means it's a table look you can't do anything to that table but that's not true in postgres there are what I'm looking at six seven eight types of table locks so a table lock by a certain type I mean it just means that something happened to this table it doesn't mean you cannot edit this table you can absolutely do that it's just that of an indication that same thing with the raw level Lux which is easier to understand and Page logs which have we have zero knowledge about postgres that at least from the dock but that's what I wanted to do in this video I just want to go through the different types of logs confusing as they are name wise and just try to know what what operation has acquire what kind of lock if I do a vacuum full what can I do to my table concurrently if I do a vacuum normal the auto vacuum what can I do what can't I do if I do a trunk kit what happens if I do a normal insert can I do another uncertain table if I update a row can I update that same row okay these are the questions that I wanted to understand before I jump into taking out like a bigger project actual things that happen and try to kind of talk about something that I don't understand right that's what I wanted to do in this channel let's jump into it all right so looking at chapter 13 from the dog concurrency control and specifically chapter 13.3 explicit locking I don't know why they are called explicit locking because most of the things that we're going to talk about here are not really explicit it just happened implicitly as a side effect from you running certain operations and I suppose before we we jump into this we might need to Define what locks are locks are critical for concurrency and and ensuring Integrity when it comes to data right they are used in some form in the operating system if you talks data structures courses you will see that logs are talked about in certain in terms of semaphores or mutexes you know where a certain variable can be locked so that if multiple threads which is what concurrency is right or single user single thread you don't have this problem you don't need to acquire any locks technically so we acquire mutexes and locks to ensure correctness the only the types that are familiar and popular are shared logs if someone if someone is a read a value think of it like an object a row a document right they can acquire a shared log and if someone else acquired the same Sherlock you can acquire unlimited number of shared logs readers are called real drugs and then if someone want to update there must be no shared locks to update that why and the reason is because if I'm reading something I want to make sure no it doesn't change goal of shared locks right so that's that's the locks we know and exclusive locks of course will give you exclusive access now once you obtain an exclusive lock on that object or row nobody can change it why because nobody can acquire a shared lock and that's basically the two simplest type of locks which are also aggressive if you think about it right because if in real databases like when you read something you absolutely other transaction can change that thing right so real database don't really acquire access or shared locks and exclusive when you do normal operations they don't do that right they do other type of lighter weight locks and that's what we're gonna talk about here can you still acquire access shared logs and uh exclusive block yeah manually explicitly Say Hey I want I want to access shared lock on this I want to simulate this experience and you can absolutely do that with that out of the way let's start with this document all right so we'll talk about explicit locking here four types or actually more than then table locks things that happen at the table level and again it's not really locking the whole table don't think of it like this way like oh I'm locking the whole table nobody can do anything there are lighter weight things that happen at the table level that tells postgres things here row level locks things happen at the row level page level because databases work with Pages you can lock a certain page and again we don't have many information about that particular one Deadlocks things that happen when two transactions try to manage the same resource and they are waiting on each other you end up with a Deadlock advisory log that's a also called application locks level locks which is apps can create this concept of a mutex that is manually created by the app but those live in the database so other apps that try to acquire the same advisory lock will will wait and you can do cert so much cool things with those you can serialize as a different logical operation even if it's not really a transaction right let's say your application is is performing an operation but this operation is Eve is also a multiple transactions right so you need to protect the whole operation not just that transaction level because you have protection at the transaction level so you can operate at that level so let's say you want to prevent people from running this operation twice concurrently you can't just do that with normal transactions because your application your operation might acquire multiple transactions and and and at a whole it's one unit of work right so advisor lock all right let's let's get it started what's good SQL provides various lock mood to concurrent control access to the data to data and tables these modes can be used by for application controlled locking in situations where mvcc does not give the desired Behavior as I told you right because mvcc which is multiversion concussion control doesn't always give you what you want you need sometimes you need more and that control is given to you as well also most postgres commands automatically acquire logs of appropriate modes to ensure that reference tables are not dropped or modified in incompatible ways while the command executes and that's an interesting thing right so we so they're giving an example here for example trinket cannot safely be executed concurrently with other operations so it obtains an access exclusive lock on the table to enforce that acts exclusive as we're gonna see here is the highest level of locks it's a most aggressive one it's you do that when you actually want to change the layout of the whole thing right it's acquired by truncate because let's see how how do you do actually try and gate operation you might say oh delete all the table no no that's just so not performant because you know that's a trend K that is undoable what do you do is you literally create another table and point the pointer of this stable your name points to the empty table leave the whole thing that all tables just goes away that's how I would do it you know it's it's a it's an instant that's how you truncate your table and to do that you want to make sure that nobody actually reading that at all right and how do you know that through other type of logs that we're gonna talk about all right so let's get started with table level locks the list below shows the available lock modes and contexts in which they are used automatically by postgres again it's an automatic operation it's not really implicit so this chapter called explicit lock-ins a little bit confusing you can also acquire any of these logs explicitly with the lock command interesting I didn't know that so you can acquire any of these just say lock and simulate an access share simulate an access axis exclusive like a truncate like as if you run a trunk it huh that's very interesting probably not a good idea unless you know what you're doing right remember that all these locksmodes are table level locks even if the name contained the word draw which which is what confuses me here you might say something called row share it doesn't really mean it's it it's a raw lock it's actually a table lock that has happened to be called raw share the names of the lock modes are historical the to some extent the names reflect the type the typical usage of each lock mode but the semantics are all the same the only real difference between one lock mode and another is the set of lock modes which with which each conflicts that's what important what matters when we see a lock mode conflicts with another and that's where the blocking will happen when it comes to transaction two transactions cannot hold locks of conflicting modes on the same table at the same time so if transaction a is holding a certain lock right type and the transaction B want to hold a conflicting type right it will it will conflict and they will block each other however a transaction never conflicts with itself right for example if my acquire x a transaction my acquire an exclusive log and later acquire access share log on the same table that's fine as long as it's the same transaction that's fine like it's similar to how you write and you can read your own rights right and you can write your own right you can change the things you just changed that's fine but it's other transactions can Duty that to you so that's a very interesting thing and I want to start and instead of starting with these lock mode I want to start with the most aggressive one access exclusive this one conflicts with locks of all modes and you can manually acquire that using something called the lock command or it can be implicitly acquired by these operations this mode guarantees that the holder is the only transaction accessing the table in any way that's the most aggressive one right you can't read you can change you can do anything on this lock is acquired by another transaction example drop table if you're in the process of dropping table nobody can read it nobody can write to it if you're in the process of truncating which you must mean that you acquired an exclusive nobody can do anything to this table can read it can do anything that includes another transaction trying to try and create that same table right access exclusive kinda conflict with access exclusive right re-index if you want to re-index that whole table I want to make sure that I'm reading this and I don't want anything to change it right in re-indexing the table or even create index for that manner should you create that should acquire the same one because I don't want anyone changing the state of the table or insert the new stuff while I'm re-indexing because I can't miss stuff right because as I'm reading reading reading someone just changed something if someone just changed something then I'm not I'm not gonna pick it up and my index is gonna be out of uh State out of out of sync cluster if I want to Cluster my table such that on a certain column I want to reorder the Heap and the table on that column I can call this command and the moment you're starting shoveling things up in the table changing to pull location that will require you to essentially re reset the table IDs create new triple IDs and as a result not only you have to update all the indexes but also you have to change you have to make sure nobody's is screwing with that table in that process vacuum full vacuum is an operation that Mar removes the tuples that are not visible by anyone right it's dead nobody's needing that record anymore because even if it's like an older running transaction that still needs that for example it's a repeatable read that requires cons and VCC and needs that row no all these transactions are committed this row has been dead someone updated someone deleted it and that that that's done we have zero reason to keep that row if that's the case vacuum will just remove it freeing up space in that page for other tuples to go in that page that's the only thing that vacuum does vacuum fall is here's an example before we go to vacuum phone for normal vacuum if you have a page with all of it filled with dead tuples let's say not just page 10 pages to say you deleted a massive number of rows on your table if you just do a normal vacuum eventually it will Mark all these Pages as the rows are deleted so those pages are still there occupying space on the table but they are free to be inserted and you can insert stuff in those pages right that does not mean it will be reclaimed and sent back to a desk no you're still acquiring whatever how many pages times 8K which is that default page size right vacuum full actually does that vacuum full removes physically remove all these space you know compact the table rename things if if necessary if you have like Pages 0 to 10 and all are dead the whole page is already renamed I think right it's like okay from zero to ten it's like unused now and Pages 11 and forward are used no the whole thing will be just cleaned out right we started over from page zero and start moving data around and right so so it's shuffling the whole structure so it has to acquire access exclusive right to do all this thing refresh materialize View a material as a view is a view that is materialized as persisted on desk right and to make sure so because it's persisted and instead of actually on demand you query the database and give you the view materials viewers faster but they can go out of sync right so to refresh the materials review We want to make sure that while I'm refreshing while I'm copying data I make sure that nobody actually touches that original table so I don't want anybody to touch anything right so I'm gonna acquire that and remove it of course concurrently can be added to indexing to certain things that to allow other operations because you can currently do things like you can create index concurrently create index can can lock the table of course right would access exclusive I don't see it here but I'm pretty sure it does if free index is there why is create index not there it doesn't make sense right I would imagine like create index will also block so create index concurrently doesn't need to and we're gonna go with that now we understood the most aggressive let's understand the least aggressive which is access share right so X is exclusive and access share these are conflicting locks access share is acquired by selects normal select none like select for update just normal suck if you like do a select on a table right or if you select a row from that table the whole table is uh locked with this type of lock which is access share for that transaction so access Sherlock now we obtain it that means the moment anyone selects the trend select something right and it leaves that transaction open you cannot try and get the table you cannot delete the table you cannot drop it you cannot do anything like re-index or vacuum full this version will be blocked if someone is actually reading so that's very critical so vacuum Falls really not something you have to do but normal vacuum shouldn't really block that and that's what I want to understand truly as I as I dig deep into this let's continue so access share simple operations right roll share a row share is the select commands acquire a lock of this mode on all tables for on which one of the four update for no key update for share or for key share options specified in addition to access shared logs on any other tables that are referenced without any explicit four options so because we're reading like we're doing a select statement so we're acquiring a shared law a shared access access share but then we also acquiring a row share on the table so someone in the stable did a row share and I know the names are very confusing right which means someone did an actual select for updated so technically they didn't really changed the they didn't really edit anything but they're acquiring M4 update or for d for share or for uh for for key share these operations will acquire an explicit lock at the row level which we're gonna talk about right but also acquire a table level lock code row share so it gives indication this gives an intention to possible that something is happening on this table right raw exclusive so what what happened here raw exclusive raw exclusive what happens when you do that normal DML stuff like if you insert the row if you delete a row if you update a row the table is marked as raw exclusive for that transaction again or here we start at the table level so what happened with that those kind of conflict with other operations they kind of conflict with share and share or exclusive and exclusive and access exclusive we're gonna come to that like in a minute and we're gonna when we see the table so if you do a merge and insert an update this is acquired at the table level and and I will tell you what can and can't be done and of course access inclusive the moment you do that or select you can't re-index the table you can't vacuum right because that complex with anything but I'm interested in the in the interested in the small things here and that's what I'm really interesting okay share update exclusive or getting interested now share update exclusive right conflict with share update exclusive share share raw exclusive exclusive it's get it's getting ridiculous right now right this mode protects the table against concurrent schema changes and vacuum runs here is interesting this one share update exclusive is acquired with vacuum normal vacuum not full because we know vacuum fall re changes the whole structure right and acquires an access exclusive normal vacuum right acquires simple share update exclusive which is uh lightweight slightly lightweight what can you do with this guy right when you do that you acquire this share update exclusive and we'll see what can't we do with this right does it it it complex with share update exclusive which is itself so no two vacuums can run this at the same time it conflicts with share we don't know what share is yet we're going to come to that it comes with share raw exclusive shadow which we didn't explain yet it it talks with exclusive which we didn't talk about yet and then exclusive lock right this one's protectable against schema changes like if you're making a concurrent schema changes and vacuum runs this will protect the table against it so what things are acquires this if you do an analyze if you want to actually get that table statistics update that table statistic you acquire that so technically speaking so I can absolutely do uh a vacuum and continue to insert rows in the table because those guys do not conflict with each other we'll look at the table in a minute and understand this create index concurrently acquires that right so you can still create rows that's what really matters to me right I can create update and delete while create index concurrently I can do that right I can do creating statistics I can I can re-index concurrently and do all of that what is share now share conflicts with raw exclusive share update exclusive right raw exclusive is there is the one ah now this is actually interesting right share that's our shared lock okay table you can't really change that table right because the conflict will draw exclusive and raw exclusive is the one that acquire when you update and certain delete so that's a dangerous one so if you if your transaction acquires share like a create index credit index can't be run in the transaction I think not right or maybe it is yeah yeah it's this is postgres this is not um it's not Oracle so yeah create index will acquire share log on the table and when you do that you can do any updates or insert on or deletes your table and also it conflict with share raw exclusive which we talked about no we didn't talk about share raw exposure and it acquire and a conflicts also will share update exclusive which is acquired by vacuum so if you're creating an index you can't run a vacuum share raw exclusive conflicts with pretty much everything right this mode protects a table against concurrent data changes and is self-exclusive so that only one session can hold that down it's acquired by create trigger and other forms of alter table so I don't see it much often but it's a Creator how often you call Creator so I'm not really interested in this much exclusive what is exclusive this is acquired by refresh material view concurrently so some of these are really interested to me some of them are really interesting it's like vacuum is interesting to me and I want to know like now with this knowledge what conflicts with what and this is where we're looking at this beautiful table here and let's see if I can zoom in a little bit here I think that's fun so we have access share on the left side side we request raw share raw exclusive and of course we have to go back to understand what the heck are these right access cloth again this is the the the the the the hammer right and what is interesting is access share which is the read select and we have access Rocher which is Select for update and we have share update exclusive right share update exclusive is the is the vacuum yes share update explodes is the most important thing so I can definitely run a vacuum right while doing editing right and that's what really this box is the most important one to me the reason I'm asking that is because you want to be able to run vacuum so you can freeze transactions as you continue such that you can run as often as possible nobody will should technically block you when you run vacuum and that's the most important thing here right the only time vacuum will be blocked which is this guy is if you do a share update exclusive what the heck is that it's the same thing of course it doesn't make sense right like you you're it's like uh what else if you if you re-index concurrently for example right it's like okay uh my table my index is corrupt I just want to re-index it just throw everything away and just like green does everything right for some reason you want to do that that will that will prevent you from running vacuums which is dangerous you want to run vacuums as as often as possible so to get rid of to freeze transactions so you don't reach the the their transaction wrap around right and that's really a disaster if you do and also share what's share share was like created by index like yeah how often you create an index right I'm talking about just like the operation that just is so aggressive share raw exclusive what the heck is that share raw exclusive let's go back and read that yeah that's the create trigger one who cares an exclusive and access explosive so that's what that's the most important role to me really and as long as I can read I can write and Rocher what the heck is Roshan again Rocher is by select for update so I can also do select by update for update sorry and still run vacuums normally right I suppose the the selected rows can't be touched in that case right let's continue row level locks now we're moving up in the stack here to Raw level locks all right so now we talked about table locks it's time to talk about row level locks which is very interesting there are four types of row level locks these can be obtained explicitly you know you can actually say select for update select for no key updates select for share select four key share and we're gonna explain talk about them and read through the dark here understand what what the difference between them understand that it depends on the lock here some locks can block other Riders and other locals but doesn't necessarily block other readers and here's the most important thing here to to understand so these locks can be acquired as I said explicitly or also implicitly by update and delete delete are easy okay but updates on the interesting cases here because updates there are two types of locks that can be updated by update and give you that because like an update can happen to the row on a column that doesn't have an index or it can happen to a column that had an index and or can be referenced by primary keys and or I mean foreign key so that makes a difference on this updates So Soft updates if you will and hard updates that can cause ramifications so let's talk about four update which is the first one you do you can do select for update and this sucks for updates causes the rows retrieved by select statement to be locked as though for update as though for update as if you're actually updating it this prevents them from being locked modified or deleted by other transaction until the current transaction ends so this is like the hardest the most aggressive raw locks there right that is other transaction that attempts to update delete select for update select for Nick no key share select four key share anything you do if you have a row that is have been for updated you're done you can't do anything to all this stuff you can read it though right it doesn't block normal reading of that row will be blocked until the current transaction conversely select for update will wait for a current concurrent transaction that has run any of those commands on the same row so same thing right if you want to run a select for update and someone had done a delete on that row you can't do it right because a deleted row is it deleted on another transaction but might not have been committed yet but now on your transaction you want to read that deleted row you can't do it if you do select by update can't it will be blocked right and of course in repeatable read and serializable because they're using optimistic concurrency control this thing will be will effectively be blocked right and it will you'll get to get an error it says Hey serialization failed something changed from the moment you did this operation things has changed and I cannot I can no longer guarantee that that that same operation will be red you know in a snapshot anymore I can't do that and you're gonna get an error here's an important important part select for update lock mode is also acquired by delete if you do a normal delete on a row that is as if someone did a select 4 update on that role very critical to understand yeah so at that row level lock and also by an update that modifies the values of certain color not all columns right concurrently the set of columns considered for update are those that have unique index that can be used in a foreign key so that is the reference here so now with that said how do I differentiate between updates that update the key and updates that doesn't update the key there you go the name is clear here for Nokia update hey I'm doing an update but I'm not updating any Keys No index keys or anything like that so this this is identical to 4 update I'm doing a four no key update in this case I'm looking for out there but I'm not really updating any Keys except that look is acquired is this is so this is very similar to four update but it's weaker okay this lock will not block for select for key share for example because I know that the thing I'm doing is not really updating keys right so it's safe to share a key a few well this particular lock is acquired when you do a normal update if you update a column right implicitly like doing an update that particular lock will be acquired for no key update like as if you if you're updating like column B and B doesn't have an index that lock will be acquired on the row and that's a very soft lock so you can technically we'll see what conflicts with others here in a minute for share so behave similarly to four Nokia update except that as a this acquires a shared lock rather than an exclusive lock on the retrieved row right a shared lock blocks other transaction from performing update delete select for update select for no key update right you can even do that but it does not prevent select for share and select for Keisha because that's that's the definition of shared log so now if you actually want to do a shared log you do select for share and that row and you you can have a hundred transaction doing select four share that's fine it doesn't conflict with each other but only once you do that no one can actually update the row if you do that physically updating the row becomes impossible because you you just acquired a shared log here so this one the four key share is will block you from making deletes right but it will not block it will block you from making deletes we'll block you from making updates to columns that are indexed that has keys but it will not block you from making updates to columns that doesn't have keys so will not conflict with this for no key up there so let's go look at that at the table here so again I'm telling you this this is like a really head spinner so let's go through the concurrent locking for Roblox here all right let's take a look at the table here and see what conflicts with what so roll looks for update is the kind of most aggressive one where it kind of conflicts with everything you know it is acquired by deletes if you actually delete a row it is acquired right if you if you update a a row that has a primary or even a secondary key any key really right that's from what we read here it does not acquire when you do a normal update on a column that doesn't have an index which is very critical to understand here right you can do those kind of updates but they won't require that particular key lock right and it will conflict with all this stuff right so the second one is uh four key select for for no key update right so it's an update but it's not updating keys so uh this is a quad when you actually explicitly say that select or Noki update right or when you do an update on a column that doesn't have an index this this is what what gets uh uh uh locks and by doing that that kind of lock you will still still conflict with normal for update so like you cannot update a column that's been deleted right or you cannot uh update a column that has been updated with a key right that's critical to understand okay and but you can definitely update a column right that has been for has been read for key share right those have four key update right it doesn't it doesn't acquire for key share so what does what what four key shares doesn't seem like something will acquire that it goes from where we read nothing from postgres Aquarius except the user so if a user can actually select four key share you can technically update things that are not keys right that's what you can do right and then we're gonna go with another harder share log which is four share which basically will block any update to that you can still acquire many shared lock on that guy but you will you you want to you want to prevent delete and any kind of updates right but if you want to acquire this for keyshare you can do a delete updates but you you may want to allow uh updates to non-keys and in this case you can you you can acquire that I can't think of an example to be honest to do that but the most important thing to understand is just really just the delete example and update example the update example is the interesting one because it has two cases right can I read in all of these cases you can absolutely read the row even if you deleted or updated right that's that's fine right but if you want to simulate now the question is like how do I simulate the app an access log or a shared log versus exclusive log you can do that so you do a select for update and if you really want no one to read that your application must submit selects for share all the time so that they get blocked right because they will do that and now now you can play tricks with your application and you say all right I want to do a select for keyshare such that hey I'm really relaxed I I'll let you do delete I'll let you do updates but update to keys but if you're updating things that has nothing to do with indexes I'll let you do that you can you can go a little bit lower there then let's run through the page locks here page level locks in addition to tables and roll logs page level shared exclusive locks are used to control read write access to table pages in the shared buffer pool so the share buffer pool is where the polskas put their pages in so it's a shared memory between all the processes that postgres spins off and then you can it it basically puts shared and exclusive logs to this and absolutely there's no control here okay these locks are released immediately after a row is fetched or updated application developers normally need not to be concerned with this but they are mentioned for completeness here so this makes me think like what happened if I am updating or inserting or reading or we actually locking the page so this tells me that you no two transaction these two things are serialized right the page logs are being obtained to prevent two threads two process from updating the same page and understandably so right doesn't make sense to have like this is now we're at the OS level in memory right this is like basically a mu attacks so yeah so it makes sense to have page logs I'm just worried about yeah performance when it comes to concurrency of multiple processes updating the same pages like I've seen this in SQL server and I have no idea about postgres level of page locking there and uh okay assume I'm doing many inserts right to some table or multiple transactions right multiple clients let's say because each client gets a process and passwords like a back-end process so they those two might essentially perform the insert themselves and try to update the same page right and when you do that this will contend on the last page effectively because all of them were basically inserted the same tail page right that's where inserts go and I'm I'm just thinking what is the ramification of doing that on concurrently I think you you will feel it because at the end of the day a mutex must must be acquired like a lock must be acquired the page level otherwise you get corruption Deadlocks are interesting here I have the whole video about Deadlocks and they're talking about an example here where the locks can happen so especially especially if you're doing explicit locking like when you're controlling the transaction like all of a sudden you can easily get into a deadlock where this is needing this transaction needs this but this transaction is also needing something and they are waiting on each other for example let's read an example here if transaction one acquires an exclusive lock on table a and then tries to acquire an exclusive lock on table B while transaction 2 has already a exclusive lock on B and now wants an exclusive tab on a then neither of them can proceed note that deadlock can also occur as a result of raw level locks and thus they can occur even if the explicit locking is not used oh okay how I've seen it in SQL Server a lot but especially with certain custom applications right consider the case in which two concurrent transaction modifier table the first execution the First turns are going to execute update accounts set balance equal balance plus 100 where account number equal 100.111 so that's that doesn't update to the balance and I don't know if balance is actually what is balance balance is I don't know if it's indexed or not but let's assume it's not so it's a four no key right clock this acquires a row level lock on the row with a specified account number so that particular account number is now locked in the second transaction executes this updates it account to all right so someone just increased their account by 100 the other wanted to debit the account at the same time right so it it executed two queries in the same transaction one to increase account two two two two by 100 which is okay that are all locked but then that other transaction also decrements the account one one one by a hundred so that has to wait so that so far there's no edit lock here right the first update same successfully acquires a rollover log on the specific row so it succeeds updating however the second update statement finds that the row is attempted to update is already being locked so it waits for the transaction that acquires the log to complete transaction two is now waiting on transaction one to complete before it continues exercise execution so far no Deadlock then the first transaction a start deducting account number two right two two two two with a hundred now it wants to do that and now it's it detects that there is a lock on that row and get stuck so absolutely especially in this kind of scenario you can get into a Deadlock right transaction two already hold the lock so it waits for the transaction two to complete thus transaction one is blocked on transaction two and transaction two is blocked on transaction one added lock condition pause this will detect this and abort it immediately postgres will immediately detect this right it's called the deadlock graph so very careful especially when when you up when two transactions try to update the same rows in certain order so how do you solve this be consistent the problem this happened because we first updated Row one and then the second transaction updated row two first like if you flip this order then it would have second transaction wouldn't have even permitted it started to begin with right but you can't always control this that's the problem right in the application advisory Locker is probably its own topic to be honest but advisory luck uh advisor look are those locks that are acquired by the application so let's take a look here postgres provides a mean for creating logs that have application defined meaning as I said right these are called advisory lock because the system does not enforce their use right it is up to the application to use them correctly so hey Europe this application you create them you you deal with them right as a database if if someone tries to acquire this log I'll block it if it's already exist and advisory locks can be useful for locking strategies that are awkward for mvcc and and I'll personally run into this a lot in cases enough personally locked around into cases where I I had to use advisory lock right in SQL Server there called application Level logs and oracles calls them something else but yeah sometimes you have specific the application has certain semantics that it spans Beyond uh transactions really and you can't really rely on that right so for consistency use application Level lock all right guys so that was a a very long podcast discussing all the types of locking in uh postgres uh I learned a lot about this and I think I'll use this knowledge for you know two understand when someone says oh our vacuums has been blocked and we can't run it I want to call that out I just like what does that mean why would vacuum be blocked because now that I understand the vacuum as an operation what exactly does it block is it's it's truly important right because it tells you like what kind of workload people are running right because vacuum is not being blocked by anything there are specific operation that is blocked by it and understanding this level of details uh it makes me at least want to learn more alright guys thank you so much goodbye

Channel: Hussein Nasser

Views: 16,788

Keywords: hussein nasser, backend engineering

Id: URwmzTeuHdk

Length: 48min 22sec (2902 seconds)

Published: Sun Mar 19 2023