Best practice for handling concurrency in API and DB to optimize the communication [closed]

I'm a junior DE, learning about RESTful APIs, and intent to use FastAPI for practice, to connect my local (but even if it's an app or web I would have the same question) to a SQL database (DB) server. I noticed two concurrency situations, one in API, another in DB. They're both unrelated to parallelism which is another subject. I need to know how to choose best method in each so the DB server and local/web/app cooperate within the most optimized design. I'm new to both topics so please correct me if my understanding needed amendment. 1. API In FastAPI, one can use async and await so Python knows which lines take time (because they are operations like sending request to server that take time) so it will only collect the result of line after it's finished. This allows it do other tasks while occasionally checking if await task is finished. Therefore, we can either use or not use async to take advantage of concurrency. If we don't, we will have a sequential design wherein every request needs to be finished before other requests or tasks are started. 2. DB In SQL DB, we again have concurrency on queries when querying the same object (e.g., a table). So we can choose different methods to handle concurrency, two of which I know are "Transaction Isolation" and "Snapshot Isolation". Snapshot isolation: Uses row versioning by backing up affected rows in tempdb and only provide committed version of data in a new query. But it's optimistic because it assumes there won't be two updates at the same or close time, so it's for systems that write a little and read a lot. Transaction isolation: we have following levels of putting locks: Read uncommitted Read committed. Repeatable read. Serializable The last one is the most stringent one, it only allows one query at a time. To recap, in API and particularly in FastAPI we have async and await method to handle concurrency. In SQL DB we have two methods of transaction isolation (4 levels so 4 choices) and snapshot isolation to handle concurrency. Which one is best to choose in API and in DB so they communicate the fastest with least issues, depending on which scenario? Thank you in advance for sharing your knowledge about this.

Mar 21, 2025 - 14:50

I'm a junior DE, learning about RESTful APIs, and intent to use FastAPI for practice, to connect my local (but even if it's an app or web I would have the same question) to a SQL database (DB) server.

I noticed two concurrency situations, one in API, another in DB. They're both unrelated to parallelism which is another subject. I need to know how to choose best method in each so the DB server and local/web/app cooperate within the most optimized design.

I'm new to both topics so please correct me if my understanding needed amendment.

1. API

In FastAPI, one can use async and await so Python knows which lines take time (because they are operations like sending request to server that take time) so it will only collect the result of line after it's finished. This allows it do other tasks while occasionally checking if await task is finished. Therefore, we can either use or not use async to take advantage of concurrency. If we don't, we will have a sequential design wherein every request needs to be finished before other requests or tasks are started.

2. DB

In SQL DB, we again have concurrency on queries when querying the same object (e.g., a table). So we can choose different methods to handle concurrency, two of which I know are "Transaction Isolation" and "Snapshot Isolation".

Snapshot isolation: Uses row versioning by backing up affected rows in tempdb and only provide committed version of data in a new query. But it's optimistic because it assumes there won't be two updates at the same or close time, so it's for systems that write a little and read a lot.

Transaction isolation: we have following levels of putting locks:

Read uncommitted
Read committed.
Repeatable read.
Serializable

The last one is the most stringent one, it only allows one query at a time.

To recap, in API and particularly in FastAPI we have async and await method to handle concurrency. In SQL DB we have two methods of transaction isolation (4 levels so 4 choices) and snapshot isolation to handle concurrency. Which one is best to choose in API and in DB so they communicate the fastest with least issues, depending on which scenario?

Thank you in advance for sharing your knowledge about this.