 Okay, so hi, let me begin with the quick story Imagine that you are in the situation in the that you are waiting in shop queue and the first person in that queue suddenly realized that he forgot the wallet and He need to go to the parking to grab this wallet and to back to the shop to pay for the for his goodies and Even if the shop assistant could serve the net next person She realized that she has open transaction and because that open transaction she's not able to proceed with the next person to save the time and Imagine that this issue of the open transaction is also occur in Python in the sequel alchemy so For now no matter what this function does What if I will tell you if we will use sequel alchemy to Get the resources from the databases this function can handle around 30 requests per second but if we will Change the sequel alchemy to databases now the same code 99% of this of the previous code now can handle around Okay Every time this okay, I need to switch Okay, sorry for that so now the databases the same code but with the databases can handle 170 requests per second such nice improvement, right and This is the best part with some knowledge of the sequel alchemy and little and speed improvement The first the code from the proof from the first example also can handle 170 requests per Second so sounds interesting. So let's deep dive into asynchronous sequel alchemy We'll mostly talk about transactions and connections. My name is Daniel Bezoski and let me quickly introduce myself So I have been working with Python for six years now Currently I'm working at Mirami software in Poland But that's not all I'm also a mentor at Mirami starter This is our initiative when we teach Python from scratch and on the top of that I also I'm also our organizer of a local IT meetup called meet tech It's really nice in context of my small hometown that we have place to rather the people to share their knowledge and Go through the Latest trends in the industry. So let's jump into the code Let's start with the example. We call this implicit transaction example And we'll go line by line line to see what is happening here so first we will create an async engine to connect to the database then we need session factory and It's followed by Simple function to just return this session This will be used as a dependency injection. It's important to note that We are using sequel alchemy in context of fast API. So we need to initialize the fast API Up and for the sake of the clarity we will omit this code for from subsequent Slides size it's remain the same So finally we have an async function That handles HTTP get method dysfunction retrieve and return the node from the database based on provide provided ID So let's get deeper into the Details on the left side. We have our code on the right hand side We have logs that our application will generate So these logs are essential for us to understand what is happening In context of sequel alchemy and how sequel alchemy manage the session through the Dependency injection so now we we execute the the scope and go live by line to see what the logs Subdication will produce. So now we are on the third line and no logs appear. So that means that Starting the session creating the session which is does by dependency injection because our function already has access to the database, right? Doesn't it's not the same as? Acquiring the connection so far no connection is acquired, which is good, right? moving forward Yeah, moving forward we are now on the pipeline and we can observe that some logs appear so First the connection is really acquired after that transactions Transaction begins implicitly and after that we can see our select statement After the exiting the function we notice that next set of logs appear from these logs we can see that we return the response first Following that sequel alchemy perform a rollback and then connection is released So let's stop for a moment and pay attention for the few important things here So firstly sequel alchemy automatically start the transaction Which means that the connections and transactions are closely linked Secondly, we may observe that even for a simple select statement Sequel alchemy proceed with the rollback. So it could be surprising right because even for For a select we can notice the rollback is here. It could be tricky and This rollback is because we need to close the transaction by closing the transaction the connection returns back to the connection pools pool and lastly we notice that we return response first and then we release the Connection which not might be so efficient. So let's deal deeper into this aspect so Here we have simplified request lifecycle in fast IP fast IP I IP I We will focus on the interesting steps for us because it's not this presentation this presentation is not about fast IP I itself So let's move So here is the first one when the request comes moving forward We have our dependency injection with which start the transaction Moving forward. We have request execution when the transaction is Begin and the connection is actually acquired and what this is the place This is the step where the request is actually executed, right? So moving forward few steps more on the ninth step we return the response and on the last 10 step the Connection is the transaction is closed connection is released and the session is closed Unimportant observation here is that we are really wasting our resources which which in this context is connection to the database, right because We need this connection only on the fourth step because this is the place when we execute the Request and one thing on the 10th step we have middleware provided by fast API which closes which ensured that it that Dependency injection that the object injected by dependency injection is closed. That's why on the 10th step is Session is closed, right? Okay, so Now we know That we are wasting resources Let's see how we can solve this issue So this is the old code. Let's some make some Modifications as you can see one Notable change is that we've wrapped our code with the database begin statement Which means that we start session SQL alchemy session in explicit way and once again, we will execute this function and go like line by line and see what logs it produce So now we are on the fourth line and again no locks Are produced at this moment that means that and tearing into the context manager and Explicitly starting the session is it's not the same as acquiring the connection. So moving forward Yeah, the same as previously on the database execute the first locks appear and So far nothing changed the transaction is started in the connection is acquired the transaction is started implicitly and Followed by our select statement and now up on the Exit context manager the next set of logs becomes visible. We notice a commit happening followed by release the connection here we can see The change compared to the previous example instead of rollback. We have commit statement and Moving forward now when we exit the function The locks show us that we return the Response so in this example, we release the connection first and then return the response This approach It's look like improvement, right? Okay, so let's compare the locks from these two Examples as I mentioned earlier we have significant Improvement here once we can see on the right-hand side. We release the connection first followed by returning the Response furthermore is It is worth noting that in both example We can still observe occurrence or rollback or commit this behavior is inherent to sequel alchemy management of transactions and connections and To gain deeper understanding how our solution works Let's see how it fits in our Simplified request lifecycle So this is our initial solution the First example right that we know that we Acquiring the connection on the fourth step and release it on the last ten step and Now this is how it looks with the possible solution. We can clearly We know don't I will fix it in the moment Yeah, and now we see that we Really acquired and release the connection on the fourth step and this is what we want because on only on the fourth step We really need the connection, right? So Here's So to summarize what we have learned so far we understand the close relationship between transactions and connection sequel sequel alchemy handles and time operations Behind the scenes and It's important to be aware what is happening because there are a lot things happen implicitly We have also discovered that even for Simple select statement That transaction is started implicitly and through the mode to release the connection We need to close the transaction which which explain why we may occur the Rollback or commit even if we don't Execute it explicitly so let's dive into more practical example using the knowledge that we Gain that area We'll break down this example into smaller pieces so in this example we set pool size to five which means that our application have five available connection to the database and This scenario involves handling HTTP post request and this is how This is what is going here. So first we validate node ID and then we need to get to our database for this For external ID based on this node ID this this external ID is needed to make call to external API and Now to make some node synchronization with our database That's why we need to call to this external API and it's important to note that this call take Approximately five second and This five second is only for the testing purpose Okay, and once once we receive the response from the external API we update our note We've provided data by external service Finally, we returned the response. So At first glance this function make look Just okay. All IO blocking Functions are awaited So nothing you can set you can say that nothing wrong will happen here, right? So normal async function, so let's test it Now let's analyze the benchmark to asset the Situation first we will run the benchmark to measure the response time of of this request and as you can see with the five concurrent request and The pool size to remind you the pool size is equal to five and For the five concurrent request it took approximately five seconds Which is okay because the call to the external API takes five seconds. So This align with our expectations, right? so now let's make the same benchmark but for the 10 concurrent request and Something happened here, right? because the first group of the the first five Request take five second each and the subsequent request and the next group Take ten seconds. So this doubling response time means something and we need to we need to discover what Happening here and what is the our bottleneck? so let's analyze the Situation by breaking it down visually in this animation will on the left side. We have our Request with the two colors that align to the benchmark from the previous slide Yeah, this request are divided into two groups blue and pink in the middle we have our Connection pool with with the five available Connections and on the right hand side. We have Either pink or blue arrow pointing to a specific line of code as the request is being handled This color of a given arrow is associated With corresponding with requests on the left at the bottom. We have timeline so Let's start and we will start on the Nine line of the code where the database execute operation occurs at this point We notice that blue group of requests occupies all available Connections the connections are indicated as green indicating that These requests are performing database operations After that Blue group make five concurrent Apicals to external service once the Five seconds have passed the blue group receive the response from the external API and proceed with the Database update operation It's important to know that during this time the pool for the pink group is still waiting on the nine line as the connections are unavailable Once again, the blue group receive the response from the external API and the connections to green to perform database update operation on after the commit the connections are released back to the connection pool and Blue group is ready to read return the response and Now the Pink group acquired this connection to retrieve the external ID Needed for making cold to external API the connections to green during this database operation and now Pink group will wait five seconds for the response from the External API after the five second interval when the response from the external API is received We can see that pink group proceed with the data database operation after the commit the connections are release back to the connection pool and The pink group is ready to return the response So from this analysis we can Identify the bottleneck we keep connection even if we don't need them In this example, we make cold to external API that take around five seconds during this time The blue group occupies all available connections this prevents pink group from getting this connections for themselves Initially, it might not be obvious that the number of available Connection could be a bottleneck Now we know that The bottleneck is causing our connections to be kept for too long-size. We are Unnecessarily wait for the external API cold to finish this high. This has great impact of our web IPI As we do not release the connection as soon as we could Simply increasing the number of the connection will be a temporary Temporary solution and wouldn't address the root cause To tackle this problem. We need to modify our code that will better manage them connections As you can see we have wrapped every database execute with Database begin this will ensure that the connection is released after the execute is called now Let's revisit our visual is our visualization and observe the change that we have made So yeah, this is the visual is a visualization from the previous slide, but with the our Modifications and we'll go again line by line and see what's happening to you so once again we pause on the Database execute and the blue groups grab all available connection and proceed with the database operation and Now blue group exit the context manager the connections are Released the pink group take advantage of this and Acquires the connection to perform their select operation mean why the blue group make an external API call and Now the both groups are making Call to the external API and it's important to note that not much time elapsed Right and both groups Now we're just waiting for the response from the external API After the five second mark we can see that both groups Yeah We can observe that both groups Stopped on the database execute and the blue groups grab the connections to perform the database operations Following the commit and exit the context manager the blue group is ready to return the response Now the pink group can proceed with the database operation size. The connections are available once again and now after the Comet the connections are released and pink group is ready to return the response in Just five seconds. So by implementing these changes. We have greatly improved the performance and eliminated the bottleneck caused by ex-house at connections Let's see how it's Much in our benchmarks that we see previously. This is the old benchmark, right? and Yeah, now the both groups need five seconds even for the 10 concurrently request with the five Available connection with the pool size is equal to To five now the ball groups needs five around five seconds to complete their task so What do programmers love the most the diagrams? That's right So here we have two Diagrams That are more detailed visualization visual visual representation of where Resources are being wasted unfortunately due to tame constraint. We won't be able to delve them into further However, who for those who are interested in diagrams here is the QR code You can scan and see those diagrams Okay, I Have bonus for you which is the Other library that Handles connection to the database which is encode Encode databases Library that the cold databases by the encode Let's see How it fits in in our code So this is the code from the previous example that we already know It has issue of the connection management management management Let's modify it using databases as you can notice just only one thing change here only the part how we initialize the connection engine to Database because Databases understand SQL alchemy dialect so The body of our function and how we Construct the query is the same we are using SQL alchemy, but The main change is how we connect to to the database using databases To give you more context. This is how whole cold looks like with their connections and Let's see We're more practical benchmarks now We will run the benchmarks with 1,000 concurrent users Our application will have 10 available connections. We will set pull size to 10 and We will run our application on one worker So this is the first example which can handle only 30 requests per second and This is the code with our modification that where we improve the Connection management and now it can handle 170 requests per second and what about databases you are already know from the first slide It also can handle 170 requests per second and the code looks like looks like Yeah from the previous example right without this Without opening this context manager because the databases doesn't Do not open implicitly Transaction which contributes to the higher performance as we observe So for the summary for those who are familiar with Zen of Python, you should know that explicit is better than implicit Explicit management of the connection give us great improvement and We should know That we should release the connection as soon as is as is possible. We don't need the connection We should prompt the release it because the connection is passed by a reference. So this means that we do not Terminate and create new connection to the database But the connections are there we just passing the reference Thank you Thank you Damian Great talk we have a time for one maybe two questions. Please use the microphone there Yeah, hi, thanks for thanks for talk How does it work then with rollbacks if I have two different connections? I close one of them along the way. Can I still roll back everything I did in one API call? No We by default we don't want to have our Database statement to be atomic right we don't need to wrap in transactions So I'm sorry to your question. No, you are not able to do this because when you close that transaction That means that you did already everything that is related with this With this operation right If you need to do something atomically you need to wrap everything with one Transaction right to do not lose The data right? Thank you. If you have more questions you can find the Damian outside Ordering discord. Thank you again Damian. Great. Oh, thanks