About the talk
Postgres uses locks at every level in order to support concurrent operations. These include spinlocks, lightweight locks, and regular (heavyweight) locks. Each of these lock types has different properties and applications, but they all have a direct impact on performance and scalability. Understanding how these locks are used and how they interact can help to understand how your server will perform with an increase in the number of concurrent clients.
This talk will cover the various types of locks in Postgres, discuss their scope and behaviour, and look at how they can be used and monitored at the SQL level. We will talk about the different levels of locks that internal and application code can acquire, and how they can conflict, and when and how this can lead to deadlocks. We will also briefly discuss some special considerations around locking when streaming replication is in use.
Good morning again. If you were to find somebody without a background in computers and ask them what they think a lock is and why a database might need one chances are, they'll think that it's a security feature and as a security feature in the physical world, locks are pretty familiar. Ubiquitous and anyone who saw an object, like the one in the photographs are good. Pretty much just pick it up and use it to lock up something without thinking about it
much. They might perhaps encounter something a little less familiar, but given a minute or so you look at that thing. You notice that it has achieved that look a bit of old-fashioned, but you pretty much know. You can put them in some way. Turn it something inside happens and your dog gets locked. Or you might encounter something even less familiar where the key doesn't even look like a normal key and you might stumble around the bit to get it to open or closed, but that's all it takes even if you have
a lock. So complicated that you can't understand how it works, even given the diagram with its internal, you don't really care. You put in the key and you can use it to lock stuff up. Again, in the physical world, there are many different kinds of locks, they're good for different things and some of them are bad for other applications summer simple summer complex zeros, ones and new ones. The only really interesting thing about them is that they basically all the same, the
details might vary, but you use them to lock stuff up. Unfortunately, back in the world of computers, what we call, the locks are nothing like physical locks. They're not security mechanism. There's something there to make it, isn't we use to synchronize the operation of multiple processes which is obviously something very relevant to a database engine. Like both Chris and relevant to anything that wants multiple processes for performance. They are fundamentally a mechanism to allow you to control. How many processes are working on something? At the same time.
Typically you use a lock to ensure that, no matter how many processes are running, the account interfere with each other. So typically use it to do something some operation one at a time. looks are quite difficult to think about, because As the program has for the last several decades. We're used to thinking of computers as executing everything seriously, and they being One processor, and, you know, one random access memory and your code runs and dust off. And there's no nothing to interfere really, but the
movie have to deal with a high performance situations where you needs a different processes, doing different things and everything running. At the same, time to the extent that you have to change your outlook to think about how not to let these processes into fear and other database engine developer that's the constant challenge. Really People with some interesting physics might be amused by this diagram. It's one of my favourites for the experiments in physics and it's called Maxwell's demon. And
it's the little green fellow up there who's sorting hot and cold molecules and line one of them at the time through the gate. Unfortunately, it's hard to think of Physical world and allergies for computer locks because that operate the way locks do. And it's it's very easy to stretch the analogy too far. It might help to think of traffic, I live in a village in Africa and our roads are pretty tenuous at the best of times when there are landslides and things, it
often happens that you're waiting for several minutes or hours, maybe for a landslide to be cleared and you get this narrow Corridor on the edge of the road. where one car at a time can pass, if it's a very long landslides, they will be people with a flag at either end were signaling to each other these days, calling each other on the phone and the traffic gets synchronized in such a way that one car passes in in One Direction or the stream of cars and then they stop and then let gas through from the other side.
Same thing with the bridges that have limited capacity or accident site, construction zones into on. I want I want speak anymore in terms of these physical and it looks to Locks because they aren't helpful. You can you can draw similarities. But if he wants to think about locking in postgres, you just have to look at what they what they do. Desert. Classic example, in every textbook of a table that stores your bank balance and multiple processes trying to determine 10 increments of
balance at the same time and if you don't get it right to process is my dream, the same value and then change them into different ways and suddenly you have a balance that doesn't reflect the changes that you actually wanted to make. That's the reason why we need a locking engine and goes and databases in particular at every level. The thing with locks is that, if you have one in the craziest Rupert by definition, you have many processes running at the same time and you are slowing them down. You're making some of them or making all of them to have to wait for one process to
finish doing something before they can go ahead and do their work. So it is by definition, something that reduces performance and that's a team we will keep seeing in how to operate and how we deal with them in those Chris. The reason why we need to slow down to form into the coast because we can get correct results. Otherwise as in the textbook example, of course it's faster to do things wrong. I want to speak about locks in general before getting down to specifics about locks in 1st grade. One of the things that
one has to think about with long is how long the process holds one in principle, you could have a process that stops up acquires the lock does, all its work boots and releases the lock. If you have looking like that then only one or two processes that the time can make progress because it might be 1 or 3 or whatever, but you are. Blocking your processes for long longer. And the less you do that, the better performance you can get If you take it to the extreme of holding a lock, that excludes everyone and everything
for the entire lifetime of your process, then your running processes one-by-one, whether they're all whether they all exist at the same time or not. So that's typically one tries to hold locks for only exactly as long as they are needed, because if you hold them for longer than you're blocking something that could you potentially blocking something, that could be making progress, as you hold the lock. At the same time. It's the it's not so simple to just take away, all your big locks and replace them with the extremely short-lived Lock & locks, that
are very specific recording, find rainlox, because the morlocks you have, the more complicated it is to reason about them. The more potential that is for mistakes and interactions that you didn't expect. Contention for finally, headed to find LaGrange locks at various levels. So it's not just that you split up your logs and all your problems are solved. There are constant. they need constant attention to correct implementation and to ensure that the lifetime and scope are both as specific as needed and no more. another aspect of
locks in general, is, How strong they are. And this is this is somewhere where real world analogy start to break down in posters in particular, because of the Mvcc model that we use for data. It's possible to support multiple readers so you can have several processes selecting from the same table and they don't interfere with each other. They don't need to wait for each other or anyting. They just acquire a shedlock and do whatever they want. But if you're writing to the table, then you take an exclusive love which blocks, which blocks all activity for the duration of your, right
and then release it, and people can start using the table again, and processes can start using the table again. Much of the reason we can do things like this in postgres is because we store historical real versions. So updates and things are not all contending for the same, physical set of bites. On your table storage. We create new rule versions, and Thought processes with different different visibility of Troopers will actually be operating on different parts of the of the disc. If you attended talk yesterday, there was a lot more
detail about that. I won't go into it much more. So to summarize. The scope of a lock should be as specific as possible. So as to avoid, so, as to reduce contention, and the lifetime of a lock, should be no longer than necessary, against reduce contention, and the strength of the lock should be conference to what you are actually doing with it, what you mean to exclude and no more. So readers shouldn't try to acquire right off because it would be pointless and block things that didn't need to be blocked. and generally, as
Postgres developer. I need to try to do all of these things in the back end so that you get the best performance possible. So don't we? Look at the Lock types in postgres. I'm not sure if the different colors I really visible, but this talk is focused mostly on regular lock there. Also, something called advisory lock switch. Postgres makes available to applications to use. And then there are several other locking mechanisms which are used internally by post press. I have a
few slice about those if time permits. I might go into a bit more detail about that. Poor old memory barriers gets struck off because they're not, not really locks. A couple of things to start with the b g back and feeds function is something I used quite often to identify which process is holding will. Look in this case, I just started psql. And that value to 30693 is the process ID of the backend that I'm connected to an any locks that are acquired during the execution of crazed and things will be attributed to the
second. Right. It's not psql which is holding the lot with the server on behalf of the Pint and second, I have a table collects. Its contents are not really important but it's Friday is 16395 and Baton other. Number that will be showing up in Grady output once in awhile. This is an apologize for the very limited carry out the time showing. It's because he lost his really white table with many columns and I tried to fit as much as possible in a readable way onto the slide anyway. So this is
a query that I'm running in psql in the musician with nothing else connected to the database, but you already have Do Locks even see that they both wanted. They are both held by the same back and that I'm connected to to 30693. And to understand what these are. We actually have to look at, look at more columns from PG locks. This is everything in the PG, locks table. This is documented in the post press documentation, if you look up peachy locks there's a detailed explanation of each of these fields. I won't go into detail about all of them
but we can split them up, broadly like this. So who is holding? The lock is reflected in the virtual transaction and PID columns. So PID is across the process ID of the back in the virtual transaction is not shown here because it's big The Loctite mode and granted which are seen here. Locked up. His relation Moses, access Sherlock, and Grant. It is true. I go into detail about those that reflect, what kind of lock the process is holding And finally database relation page two people go to Lakeside, a transaction ID and so on
are The Columns of PG locks, which tell you what the lock is being held on. So depending on which lock the back and has acquired, these columns will have different combinations of value example, if the lock is on the relation or table in the database than you can expect or ID values for the database, in relation to be filled in, but if it's not a relation lock, if it's a lock on something, entirely different, we look at examples. Then those values, my
venal, and If if you go back to the office here, we know that our back end is now holding two different locks one is a relation lock. We don't really know what that means yet, and one is a virtual x i d lock which I'm not sure if even after so many years of using both Chris. I'm not convinced, I understand it but we look at it anyway. So These locks, which are in PV locks, at least the ones that we've seen so far, are the same locks the post-credits, the same
kind of locks, the poster has acquired during its normal operation off, Locks that you have to ask for address for you just run queries and boost revenues. Which locks it needs and goes ahead and ask for them. There is a lock table. Come on. By which you can explicitly asked for the same kind of locks if you do. So then the locks are holding May conflict with the other postgres processes. So you might if somebody is already holding a lock on a table and you
try to lock the table you're back and we'll just wait for the lock to be early so that it can be so that it can acquire it. So you can have no way to not block and so on. The other interface to these locks that are that is available to users is Select for update, which is a statement to many of us may have encountered and that takes a different. It's It's the choirs row level looks rather than table, level locks which is what love table does on. The point of this light was just that we have access to the same, same locks, the posters uses internally. So these commands
are useful to play around with in BBQ El sessions and Luca pitti locked out to, to see what happens to the output. Hear the mood column access Sherlock an exclusive lock corresponds to the lock modes. For the lock. There are several modes available. The ones listed here are just the weakest and the strongest respectively access share is a lock mode. That doesn't but isn't blocked by any other doesn't block anything except access to exclusive so it's the lock.
The select queries will acquire on the table as it runs and any number of processes can lock the table with this mode and not trouble. Select not block, select only, if another session Wants to acquire an access exclusive lock will processes that hold an ax of Sherlock be blocked. So for example if you are trying to drop the table then you have to wait until all the sessions that are selecting from it have Release the lock. Okay, one more thing. I forgot to mention here. There is no unlocked able to mount any luck that you acquire is released at the end
of your transaction or the end of the session. and, That's all I have to say about that at the end, please. Lock modes that are available in both breasts for in between Nexus 7, axis exclusive, they are locks that are held by different classes of ddl, commands in Florence. But Fundamentally. There's nothing different about these kinds of locks, it's just the barrel rules about What conflict with which other lock mode. So we look at a few examples. But for example, shallow in the
middle there with create index, do not I'm not blocked by select the monster examples. These are, they believe in luck that we've looked at so far, and they're also row level. Which I mentioned, you can acquire through selectmen's, Obispo, specifies, by itself, for example, to do for in checks and info on. These are the full moons. And again, in this case, it's in decreasing strength, sorry about that. So going back to our psql session. Here's one session where I started transaction and the back end is 2%
196 and here's another session with a different number. And if I, if I look at what locks there are already just with an open transaction. No other. Do you see one access Sherlock, which is the lock that select acquired? On relation 11645, which is granted. And it's held by 170, which is my second session as it happened. Does anyone want to guess which relation this might be Okay. Yeah, it is busy lot because of this crazy that's actually running ended who's out with me looking at. So this is obviously a lock required by
this back and because of the Prairie Run Abby's to, I split up the outfit to fit in this light. So each back and holds One exclusive lock on a virtual xid which is, which is granted. And then I seem to have forgotten to include the virtual exciting but this is just something that you have to be familiar with because any transaction that runs will hold an exclusive lock on its own virtual. Xid this so many of the entries and be lost. If you have many transactions running will be will follow this pattern will be they'll have a lock type of virtual x x. I d
the mode will be exclusive lock and it'll be grounded for the process. That's running that transaction. Reason for having these locks will become clear in the next example. No in one session, I run an update, and my ex table has has a problem which has some integer value. So I just double them and in the other notables about running an explicit transactions because I ran begin, MBA student rights to my updates, runs and complete. And in the other section, I have run electronics for update. So I'm trying to acquire for
updates roloc with in the second session. The problem here is that the first session has already acquired locks on the table to update it and we can see her again. Severely compressed output, it's ordered by pid. So the first four locks are held by one session. The one that did they update the last four locks are held by the session where I ran to look for a bit big. Yellow thing in the middle is a lock that is implanted. So what happens is that the second session or
posters realizes the second session needs to wait for the first transaction to complete before it can acquire the locks with me so it's trying to acquire a share lock on the transaction ID that The other transaction already has a lock on which is the, which is this sort of luck that we looked at before? So sure if I commit the update transaction, The select from update, which was hanging on till now, suddenly Retreat return to Rose. And if you look at PG locks again, The transaction that Randy
update and which has now committed. Does not have its head fall off before, hear the full of, it doesn't really matter what they are, but if his release them and now to 47196 has a, a row Sherlock on, on the relation, 16395 is the ID of the table X. So busy locks is really useful view in the process catalog. It has all of the information required to figure out. What's your back ends are waiting for if they're waiting for love. It's tempting to write a
giant join against the rocks to find out which station is blocked by which other session. And if you spend very little time searching for certain, you will find several versions. But the problem with these crazies, is that the actual conflict between the various available lock mode on not, they're not straightforward, it. Isn't that the Lord conflict with each other and in freezing weather or something like that? Some operations block others and some operations
don't. And there is the constant effort to Block list and block only when necessary. So this is actually something we make improvements regularly, as anyone who attended Simon talk about scheme, a great yesterday. I will have Hurt. In postgres 9.5 and above. There is a function called busy blocking pins, which knows all this stuff and which is much better than trying to roll your own query. So if you have A situation where one back end is waiting for lock which means that granted will be with the F,
then you can just select from PC blocking pins for that for that back end. And it'll give you a list of back ends that Actually holds the lobster bisque back and he's waiting for this case. To 45562 is being blocked by a lock that is held by to 45547. And that look looks like this. In this case, it's Sherlock on the transaction, but it's waiting for and this transaction is running. So while it is still running, that is before I tell you about or committed, this other session will be blocked by it.
Okay, so here's an example of Okay, pretend that I stepped slightly backwards in time. I didn't commit my update transaction and those locks are still help if I try to drop the table by the looks of it. Waits to acquire a lock fails because I'm not committing my transaction and because I have sex the love timer to three seconds. After 3 seconds, it cancels the drop table statement because it isn't able to acquire the locks that needs within the stated timers. And this is the lock that it's trying to acquire. It's an access exclusive lock which
you may recall, is the strongest rock type. It is blocked by any luck held anywhere else in the system on that relation, the relation of ghosts, our table X, And similarly other ddl statement, steak different different modes of lock on the object that you asked them to operate on the bumper. If we create an index on our table, it actually works while I'm still holding the deluxe on the table because it needs only a Sherlock to scan the table. As we saw in the table is locked. Most of which is not exhausted by the way that does ddl. Statement suggests
examples of what requires that love more than another complete list of the Creighton. Next completes, but drop index needs an access exclusively and it's canceled because I'm holding that locking it. Couldn't be quiet in 3 seconds or whatever. The Bee Gees activity. Catholic table will also give you some insight into beckons that are waiting for each other. So in in recent version of the post where is the weight events type and weight event columns will tell
you when I'm back and is waiting for a lot. So this will correspond to unlock request in PG locks, that is not grounded. So looking at locks were not, granted MPG locks is interesting. When you have Performance problems, for example, because the loss of time, a lot of times. Performance problems that show up when you have many, concurrent lines might be due to them trying to acquire the same locks and slowing down to a crawl because only a few processes can acquire to make progress at one time. The combination of PG locks and be static. Tivity with the
PG blocking its function. Can tell you a lot about what kind of locks are held on your system while it's running. And what kind of locks are being waited for the most and which takes the longest time to plant I think this flight should have been one thing earlier but created Texas, just one example. Any other BP Elkmont you run on the table will need some varying lock on the table or the Alexa, whatever you're operating on, and all of that will be reflected in PG locks as the clearance.
Okay. So how am I doing? But I'm okay. I have some time. Advisory locks are another trough of lock in postgres, the difference between them and regular locks is to fold 1. Posters does not use them. You can as an application developer acquire advisory locks and do whatever you want with them. They will not interfere with any luck. The post-race itself, requires a hose during normal operation but they do show up in the box which is useful to figure out if they're all looking problems in your application that you need to analyze.
And the other difference is that be interface provided the interface by which you interact with advisory. Locks is a bunch of functions that you call Richmond. Look at what you look in. Advisory looking is just a 64-bit integer in one form or another. They are not database object because we don't want these locks to conflict with anything cuz dresses doing. So it's just numbers. You can lock whatever number you want, and what that means is up to your application, Advisory locks.
Maybe a transaction. So you acquire an advisory lock in a transaction and it will be released when your transaction exit, which is just like was good love though. They may be session logs where you acquired the lock, but it will survive the end of your transaction and will be released only when your session in when you close the connection to the server. And again like those dreadlocks, they can be acquired in other exclusive mode where only one process can use them or shed
mode where multiple processes can hold the lock simultaneously. But not if anyone has the exclusive lock already. So this is what the interface for session level looks. Looks like the PG advisory lock function will acquire a lock on the key. But you provided this particular function requires an exclusive lock with session lifetime. In this case, there's also a PG advisory unlock function, which you can call explicitly to release the lock before you closed your session
or whether you close it or no. But with transaction log, there's no explicit unlocking. They work the same way as opposed to regular locks, do you acquire the lock with this function? It's still an exclusive lock and when your transaction commits robots, it's released. So this this mechanism actually, it is very convenient if you need to looking to coordinate between multiple processes in your application but you want to do. So in a way that does not affect for stress and so all of this
will be invisible to any ddl operations and stuff. So it won't cause any conflicts Rather than exclusive locks. As I said you can acquire shed locks with either session. No transaction lifetime. They're just like just like the other locks except you use a different function to acquire them and they can flick with exclusive locked. So just like You can't drop the table while the sessions are selecting from it, you can't hold an exclusive lock while there are active Sherlock's.
On the same key. So 42, in this case, Also. As I mentioned, that doesn't know weight keywords that you can provide to La Table in order to not wait for the lock. If it isn't immediately available. Advisory locks have an equivalent called PG, try advisory lock into on which book, does the same way? It's just that they don't wait. If the lock is unavailable, they just returned true. If they could acquire the lock and false otherwise. So your application can
try to acquire lock and if it fails rather than sleeping, if you can go on to do something else, it's just a convenience. as I said, Advisory locks will also show up in PG. Locks type will be advisory the mode will be, whatever you acquired. And the key that you're looking will be stored in these Columns of Pidgeot, which we hadn't hasn't seen any values for before. But it's just underscores that what you're looking is not stable or a road or something like that. It's, it's a
user-defined value. What it means is completely up to you. Advisory locking. Doesn't it it well, actually, with any sort of looking, it's easy to Have two sessions that acquired locks the conflict with each other. In this case, supposed sessions and B are running together, one acquires a lock on 115, the Lowcountry and both of them succeed and then a price to acquire Lowcountry and Beatrice to acquire lock, and one, neither succeeds because the other station is already holding the block, so they can be will either just go to sleep or be
unable to make any progress because of the fact that B will not release the lock on 3, so I cannot progress and vice-versa. So basically both of them are blocked by each other. In this particular case, because we using advisory lot was God doesn't care. If we just do whatever and leave your two sessions blocks forever, if that's what they're inclined to do. But the problem is not with advisory locks alone. The problem applies to any kind of the kind of locks that you acquire and post, it does actually have Something called the deadlock detector which is
which specifically tries to address this problem where one session holds lock that is required by another session. And that's the ocean is waiting for a lost. Debt is held by someone else. It doesn't have to be just two sessions. It can be a complex cycle. You might have deadlock between a group of six sessions, it's the kind of problem that tends to show up only under high concurrency. And when it happens, if may be quite hard to track down because of the number of processes involved in the copious,
you have to examine to find out what's going on. The general solution to this problem is to acquire locks in some kind of predictable order. So if A and B in my other examples, had booked a to acquire Logan, one first, and then three, this problem wouldn't have existed because it would have required. Let's say a would have required the lock on one first, you would just be waiting. It would successfully acquired Lowcountry going to do something, and presumably
exists, releasing the lock, and then we could start doing whatever it wants to do. And the problem would go away inside postgres. This is a factor in that is followed consistently in, in order to avoid just this problem, but this problem me know. So there's two possible sources of the problem, they might be a bug in postgres. That causes it to lock something in an unpredictable order and create a deadlock with much. More likely is that your application is doing a series of operations bat
because of the deadlock. So it has nothing to do with it but the way the application uses the data, it's creating a situation where you're just waiting forever on the dead Love detector, which is quite expensive to run. But it detects at situations and it will Abort waiting transactions in order to let other transactions progress. This is obviously not an ideal situation, but when the deadlock detectives involved and find the deadlock, this is Tim with otherwise not make any progress at
all. Those sessions are blocked forever and I can't do anything but quit so it's a last-ditch result. There are three configuration parameters that are especially relevant to the subject we discussed. So far, lock timeout, is something we already saw which controls the amount of time. We wait to acquire. Look up. Timer is a similar timeouts that allows us to set. How often did lock the deadlock detector with rum and low block way to something that will create login trees. When
When the deadlock timer requires spending there are I have a few more slides about some slightly, Arcane topics. Is anyone interested in carrying on, or should I stop here? It's fine. If anyone is interested, please find me after the presentation. And I will be happy to go to this for you in a summary. Look at these locks learn to understand what it says and it will tell you a lot about what's going on in your day today. Any questions? Hello of the new space. If I explicitly 4:00, or you let the experts
decide which documents the difference between these two. Blocking, the only reason I brought up explicit locking is because it gives you a way to find this to acquire these locks and look at what's in BC looks. So most of the time you just run your selector update or whatever or your ddl statement and leave it to go straight to decide what loves to apply. Is it guidance for security and faster? Anyway, No, it doesn't located on floor, that's what you want because it's for Craigslist.
It was a question here earlier. Another naive question out on, on one of your slides, you had list of phds. And so was just wondering if practically I ranted about version of postgresql and then attach the debacle to that. Can it reverse to data such as. Mitchell extract that information? Could you swing those columns, interesting data structures will be inside memory and so you don't really want to tamper with them. When there are other processes, running information is free and you can look at it in a debugger.
Buy this talk
Buy this video
With ConferenceCast.tv, you get access to our library of the world's best conference talks.