Pessimism, optimism, realism and Django database concurrency

Speaker 1: I was getting ready. Yep. And we're on. Uh welcome. I was. Um I brought uh a piece of paper so that in case I had to uh read your talk title yeah uh I would not uh say it wrongly by mistake. Um but pessimism, optimism realism and Django database concurrency

Speaker 2: Yeah. And those all names are somehow related.

Speaker 1: Yes.

Speaker 2: Okay.

Speaker 1: Please

Speaker 2: Okay, so my name is Ivers uh so I have been working in uh fintech uh for all my life and uh so Uh I spent a lot of time in uh working with payment cards and the main thing here is that it we use C<unk> and SQL and we were able to process hundreds and thousands of transactions per second and it was years ago and it was on regular relational database so nothing special or fancy like web scale databases with key value stores, document stores, or specialized databases like Tiger Beetle. And now I'm working for FinTech, which does uh all the main code in Python, so that makes it special and it also uses ORM.

Speaker 2: So now I'm kind of transferring my knowledge. from SQL to ORMs and that's why we have this talk and also my examples will follow come from the financial world So we have an account transfer. So we have source account, destination account, and then within the function we check if the source account has sufficient balance to perform the transfer, and then we apply the changes. So uh this is kind of a simplified version of the code so that I can build on top of it. But Most of it is fine. So no m uh no main no kind of major issues here. The only thing interesting thing

Speaker 2: is that Uh typically banks ki care about this uh balance check only it's if it's you spending your own money. If bank wants to collect the mortgage payment or the interest on your credit card they do not care and they will happily run in your account into negative balance. So but here we will keep this check. Now this is fine, but if I see that the code is executed from multiple threads, so that gets me thinking about concurrency. And uh turns out that there are kind of several problems with it. So the first and kind of major issue is uh this is called time of check to time of use. And this is happening because we check

Speaker 2: that the account has sufficient balance in one place, and Right after we perform this check, some other threads might execute and change the account balance. And by the time when we decide to change this account balance It might be there's insufficient balance at the time already. And then there's also the second issue, which is Less visible, so it's on those lines that increment and decrement the current balance. And if you look on the surface and you don't know what happens there , It's kind of invisible, uh but we have to remember that Python is an interpreted language. And if we look at the bytecode All these uh increments and decrements actually uh translate into multiple instructions.

Speaker 2: So we have one instruction that loads the current balance from the account, then we have a couple of instructions that uh kind of load the uh increment uh sorry this this one is subs subtract the amount and then this balance is stored back uh to the object. So these are uh so this is not an atomic operation and again some other threads might execute the same instructions in parallel and we might get uh kind of lost updates Uh so how to deal with it? I think that this is uh fairly uh uh I wouldn't say that it's easy and understood, but uh we know the problems and we know the tools.

Speaker 2: So for other languages that are better not multi-threading than Python There are multiple kinds of flocks, mutexes, futexes, and so on. So there are again mostly in other languages, there are different uh data structures with character characteristics like lock-free and uh thread safe or unsafe for using multiple threads and then there are atomic of operations typically in lower level languages and so on Uh but I mean even Python has locks, so we can solve this concurrency issue simply by having locks. So we will have to log the uh From account, mostly to ensure that it really has sufficient balance by the time we

Speaker 2: decrement it. And we also have to log the destination account so that we don't have these lost updates. And there's still a single issue here because we are taking two locks, there's a possibility of deadlock. If uh another thread does uh transferred in the opposite direction at the same time this code might deadlock so we would have to establish some kind of order how we uh choose a uh how we place locks on accounts. And uh but other than that this code is perfectly safe. But yeah, most most software isn't that uh simple and easy. In most cases, we store the data in the database, then we have several back-end

Speaker 2: processes. uh accessing and modifying data in the database and we have several frontends also doing a lot of things in parallel. And so we have to do the same code, but we do it in a database, in this case with Django So the main difference here is that these accounts are fetched from the database. So with this accounts. objects. get. However, at this point we somehow forget everything that we knew about multi-threading in the kind of regular programming language environment, but and we typically place a transaction on the whole code so because I mean databases work with transactions and that's something you have to do

Speaker 2: but then we kind of skip over this uh time time of check to time of use issues and so on And this is uh happening probably more than you imagine. So there is one A pretty loud and uh published case of this crypto exchange that basically forgot to do the proper locking around this time of check to time of use. So and the attackers by simply transferring the same amount back and forth between several accounts, they were they forced the kind of accounts into negative balance and they generated more money and so on. And uh then they finally withdrew the money and uh exchange had to go bankrupt.

Speaker 2: And uh So this is the published case. So there are attempts that are happening, attempts like that happening all the time. So this is kind of typical attack vector for financial sites. I have witnessed uh something like that happening on the software I was I was working on. Luckily for me so nothing bad happened so but it was just generating a lot of transfers and so on. And uh there's another paper that was published in 2015. It was mostly related to Ruby on Rails, but it also looked at the Django and Java ORMs. And so one interesting thing is that the software is fine until you introduce concurrency.

Speaker 2: So this is why I I'm giving this talk to kind of show how to make it correct even uh with concurrency. And the other thing I took away from that is that databases offer a lot of features like isolation levels But we do not use them. So the typical example is that if we have a query set that lazily loads related objects, So by the time we get to lazily load the related object, it might have been already deleted or uh somehow modified that it's it shouldn't uh uh be fetched now uh and this can then easily be solved by simply switching uh

Speaker 2: some to uh kind of read-only transaction level isolation instead of read committed which is the default. So we are not using uh the full capacity of the database so and that causes a lot of problems. But I'm still this talk is showing how to solve those issues So, how to solve these kind of concurrence issues with the database? Uh so many years ago when I was Looking into this, so there were two main topics on the internet, so one was pessimistic locking, but most articles talked about how pessimistic locking is unfit for stateless web applications, so therefore you should use optimistic locking which is wonderful and it solves all your problems. Another pattern I see in these days is that a lot of people kind of

Speaker 2: just don't think what is happening at the database level and instead they decide to just put Redis for locking on top of it. So but I won't I hope to show you how to deal with this without redis. And first let's go into pessimistic locking what it is. So this is Probably if you read the Django documentation, it should be pretty familiar. So there's select for update, which places a lock on the table record. And then with this locked record you can check that it has sufficient balance, you can uh change the balance of the accounts and then you can save them. All of them in a single transaction, and so this is correct. So here again we have the issue of

Speaker 2: kind of potential for deadlocks, and we again have to come up with a way how uh how to order accounts so it's uh the same across all functions that modify them. And then so this was Even the name pessimistic sounds kind of a bit bad. So what is this optimistic concurrency then? So optimistic concurrency So first of it it works by introducing some kind of a version field. If you work uh with the version control systems, then you kind of Now that Git also has the tracks kind of these changes and it can detect that something has been modified.

Speaker 2: So this version field can either be numeric or timestamp. And this is the most common, so you put something in there that is incrementing over the time. Uh I have read about approaches that use checksum, but these checksums seem to introduce another ABA problem, which I'm not covering here. And the I the whole idea of that is when we save the record we ensure that the row has not been changed since We last read it from the database. And if it has been changed, then we do a retry, we read the record again, we apply our changes and then try to s uh store the record again. Then what makes Django special in this approach is that Django doesn't support this out of the box.

Speaker 2: So there are third-party libraries that implement this. There are several of them. When I went through them, one interesting uh sentence was it was that it this package avoids database level locking, which was for me as a database guy, it was intriguing so I decided to look into is it really possible and uh but I will not use any of those packages I want to show what these packages do under the hood And they do it in different ways by patching some younger internals or uh doing some changes in the middleware and so on. But on the surface, so optimistic locking. So one of the ways how it can be implemented is by using the same select for

Speaker 2: update. But the main difference If you can spot it is that we do the balance check first, and if it's fine, then we perform this locking of the record. And we lock the record of the account only if there's this only if it uh in the database it has the same version as we have in our code. And so if it succeeds then it's fine, it's locked and we can perform the updates. And along with this updating the balance, we also have to increase the version number. So If it's a new numeric one, then we have to increment it and then we can save it. So this is one way which uh looks on the surface.

Speaker 2: similar to pessimistic locking, but then there's the other way which is considered kind of better. And this one, uh the idea is quite similar, but instead it uses the uh update function of the query set. So what it does, it again it filters the account if it has the Exact same version as we have in our application, like in memory of the application, and then it performs this update of the balance and it also increases the version and so on. If the update fails, so it didn't find the record with the same version number, then it returns that zero rows were updated and we retry the whole process again.

Speaker 2: So this is fine at it and maybe it's even faster, but the problem is that uh this uh update it doesn't trigger any jungle signals. So if you have some logic or third party packages let's say for audit logging that uh rely on post save signals then this is not working and the thing I I don't like A bit is that on one hand we kind of have these ORM objects, but here we are working on these individual fields. And after we perform the update The balance for our account, so for these models we the balance is out of date, so we would have to refresh it from the database again or Perform some calculation here.

Speaker 2: But I want to go into the claim that it avoids database level locking. And so let's see. So I have two sessions. In one of them I do this optimistic locking on the account. So it succeeds. Then I try to do the same on in the second session and it stops there so no output is given. It means that it's trying to acquire some lock that has been already acquired. And if I do a commit, then yeah this uh second session optimistic locking uh fails to fails because it returns zero rows updated Which is kind of expected, but the a bit surprising fact is that there was still some locking happening.

Speaker 2: let's try to look at it from that point of view. So we have pessimistic locking in one session. We try to do optimistic locking in another. So this is again it's blocked. So what if we do the other way around? So we have optimistic locking and when then we try to do pessimistic locking. Again, it's still blocked. So they are fighting for the same locks. So where's the big difference? So the big difference is in reality is just uh at the time when we So in pessimistic locking we first take locks and then we pr we perform all the business logic. In optimistic locking, we can perform business logic before that and then we check uh perform the these concurrency checks only when we store the result.

Speaker 2: And it gives uh if the business logic is really huge, there's some benefit in it. And also the thing is that this pessimistic locking typically requires a stateful application. So here we are doing this. in a single function that's fine but if we have a website which shows for example your account details you can't lock it before you return the HTML and then uh kind of save everything whenever the user decides to push the save button. So uh pessimistic locking doesn't really work in kind of these stateless web applications. So, but the main takeaway is that so there's always some locking, and instead of this optimistic, pessimistic in my head, I always

Speaker 2: think about explicit locking and implicit. So explicit locking in what is when I request the row to be locked. So you can do that with select for update. There's also several parameters for select for update, so I can request select for update and it with no weight and it will either return a locked rock row immediately or it will not return anything but it will return immediately so it's kind of like try lock uh that we have for mutixes. And there's also select for update which with skip locked which skips all the records that have been already locked. And this is very useful if you try to implement some task using using the data.

Speaker 2: And then there's simplicity locking, which is performed by the database itself and it's done to ensure these all these asset guarantees. and so that your data doesn't get corrupted. So update always blocks and uh so update always takes a lock and always blocks so there shouldn't be any surprise. The same is for delete also uh kind of pretty obvious. What's interesting it is that this create or save uh with force incept equals true it also uh takes locks and it might block and the condition there is that the row you are inserting uh that might cause unique constraint violation so I will show that in a minute but there's also the query set when we s simply query the data

Speaker 2: for some databases under certain conditions even that can be blocked by update operations So but that's usually it's a rare situation and we don't think about it. But uh the blocking on create and taking locks and so on. So how does it happen? So let's say I want to create account with ID 42. So in what's one session that I'm doing that, I'm trying to do that the same in the second session, and it stops there, so it's waiting for a lock. And when I commit the first one I get this uh unique constraint violation. What's interesting uh when I do a rollback, uh actually after waiting about a bit this uh uh

Speaker 2: creation succeeds and why this is important because i've seen uh some URL shorteners and also peop people typically do something like that when they generate unique invoice numbers numbers, unique sequential invoice numbers and so on. And of course the code works but there's this contention part that's usually goes unnoticed and the only impression you get is oh well the database is slow at generating unique invoice numbers but in reality it's because of this uh blocking on the insert So it's everywhere we can't avoid it, but what can we do about it? Uh So when we talk about programming languages and uh

Speaker 2: well Python is kind of notoriously bad because it has this group global interpreter lock and it's not exactly great at multi-threading but for other languages typical tips is that you increase the uh the locks so you instead of having a single big lock you put locks on different places so that threads that don't access the same piece of critical memory can somehow progress in parallel instead of all all of them waiting on a single option And then the second tip is that you hold these locks for short for as short time of time as possible. So in a database, uh for most databases we get a fixed granularity. So we uh we have a lock per row.

Speaker 2: Uh you can artificially split this uh your models into several models and Uh in that way you can increase the granularity, but that's not so common. And then the question is uh how do you release the locks in the database? And I like this sentence from this is uh Oracle documentation. So what it says is that one thing what it says is that this insert update delete uh take exactly the same same locks as select for update. So this is kind of what my initial investigation demonstrated. And then these locks exist until the transaction either commits and rolls back. So uh we also saw that. So the tip for database is to simply to reduce the time between doing an update and doing commit.

Speaker 2: So this is uh Very simple idea and to the point that it sounds dumb so you work faster by reducing this time. But we have to keep in mind that we are working with a database and it's not just the application code. So if we were would be writing an application code, then we code like this. So would kind of seem perfectly logical. So we check the balance. Then we perform the updates and in the end we create this journal entry here that like recording the fact that the transfer happened. But uh so if we are writing two log files, then this seems logical. But we are working with databases and databases

Speaker 2: have this uh property of isolation. So either all work that has been uh done in the transaction is visible or none of it is visible. So we can simply rearrange our lines. So we can create journal entry before And if we later decide that we have to uh try again, it will disappear. So we simply by reordering that we decrease the time for how long we hold the rock locks. And then there's a lot of blah blah blah. So one of the things is again it's a bit stupid, so you can't get faster faster by having faster CPUs, network, and storage. And uh this is one thing uh we have to remember in these modern days where where we scale most of the things horizontally by simply adding new machines

Speaker 2: and our AWS clusters. There are some problems when you access a single database or you work on this single account or accessing the same record from multiple bases. There are problems that do not scale horizontally. You have to scale vertically and sometimes it really it's better just to get a faster CPU and faster network. than to add extra machine. Another interesting thing is that even if you have a kind of sing single model, single table, not all rows of in that table are created similar uh created the same so there can be personal count with which gets like maybe two five updates per day and then there are bank accounts which get millions of updates per day

Speaker 2: The same for social networks and so on. And how to deal there is that typically you uh The most contended rows you try to update last. So first you update rows which do not expect any concurrent updates and then you update those that are highly contended. But there's one more topic about this uh optimistic locking. So we were doing this by using update and then we were incrementing the value. version number. And if you look at the SQL produced, it goes something like this that you update and you set version and also the balance to a specific new value.

Speaker 2: However, when I was doing this optimistic locking years ago with SQL, this is what we typically did. So we had version and we simply increment version plus one. And this was done mostly kind of out of laziness so that we don't have to pass parameters all around. And this is what we can do also in Django by using these f expressions. So we say that version field equals to whatever the current version is plus one. But if we if you remember something from the first slides, there was a uh problem with Python that it's not atomic. And you kind of could imagine that database might have similar issues that it has to load the current value of the field, it has to increment it, and then to store it back.

Speaker 2: To the files. So there it 's not atomic operations, so how does it work? But the interesting fact is that if you run code like this, if you run hundred processes like that, uh the result in the database will be correct and there's no optimistic or pessimistic locking around this. So why does it happen? Uh there's uh There's a special way how SQL update works. So first it filters row accord according to this WERC clause or dot filter that we have in Django, then it locks all the rows, and only when it has locked all the rows, then it evaluates whatever we had in this set clause, and only then it evaluates this version equals version plus one.

Speaker 2: So uh effectively it's kind of equivalent to this Python code, so where we first take a lock on the account and then we increment it. And uh this kind of got me thinking years ago. And uh we can also actually skip the pessimistic and lo optimistic locking at all completely. So what we are really interested in is not that the balance of the account is exactly the same as we have in memory. We are only interested that the account has sufficient balance. So we can write down that in a filter clause that balance is greater or equal to the amount we want to subtract.

Speaker 2: And then in the update statement, we can do say that okay, the if the account really has sufficient balance, then we do this balance, whatever the current balance is, minus amount. And if this succeeds, then we have the correct value in the database. And for uh For the destination account, we do not have to filter on the sufficient balance at all. We can simply increment whatever value is there in the database to Yeah, we can increment the value. Which brings me back to the point I mentioned previously. So this is again we are working with individual fields, so this is not very ORM-like So why are we using ORMs

Speaker 2: at all? But there is a nice trick you can do. So databases like Postgres, then the SQLite, because SQLite followed the example of Postgres and Oracle and some other databases support this. Update statement with returning star. And so what it means is that it performs the update and it returns the updated rows. And by using this objects. raw, which we typically use to execute raw select statements. We can also use the same to execute raw update statement and we receive the data back. We receive actually the uh

Speaker 2: the complete ORM object with the values after the update. So this is a nice trick. This one, this is my baby. So the the ticket itself was created uh three years ago so nothing uh there but I submitted a patch that actually does this in kind of 4RM friendly way without ever writing uh raw SQL and it's kind of I really like it because I use that a lot in back in the C<unk> and SQL days. So I really like it and I hope it that within three years it will be merged to Django. Okay. Well and the last thing I want to talk about is the bulk operations.

Speaker 2: Uh give me two minutes. So bulk operations. So databases are supposed to work on sets and we typically use this When we query the database. And in this PEP, so it's the specification for DBI interface, there's this execute many, which idea is that it takes the SQL statement and It uh takes a list of a sequence of parameters, so and it should execute them all in a kind of single round trip to the database. Unfortunately, I know that only Oracle implements it correctly But to avoid this, Djangog has these bulk create and bulk update that employs some SQL tricks to make it work. So bulk update is limited. Just for the lack of the time. So you can do hack this bulk update approach

Speaker 2: by kind of filtering the rows. So for from account I'm interested it in this account only if it has sufficient balance then I take the to account and then I perform conditional update. If I have the role which is from account then I subtract the balance. If I have got the to account then I Some the balance. And this is goes in a single round trip, so it's a win-win. And the main takeaways. So database is always locking and the way how to deal it with to simply accept it and write code keeping that in mind. So I showed a couple of ways how to do it One of the things please do not use Redis for locking, do it in the database. And yeah, and we I kind of showed this

Speaker 2: realistic, optimistic, and pessimistic way. So uh By default, try to do this realistic way so without any uh pessimistic or optimistic locking, just perform relative updates. Which is nice. Then if you need some ordering or there are some other features you want to reach then use optimistic and as a backup always fall back to pessimistic. So that's all. Thank you

Speaker 1: Thank you. I'm sorry.

Speaker 2: You can catch me.

Speaker 1: Yes, I know there's also some fintech people in the crowd. Uh so I would uh imagine they would like to have a chat about this. This looks like really good tips. Um I'll have to say a little bit about the launch. Um maybe you're all wondering how that's gonna happen. happen um it's not gonna happen in this room it's gonna happen downstairs um there's a really nice uh cafe there and catering for us um they're called uh in danish