Let's consider that I have a stored procedure that inserts information about a new order submitted by a customer. And this stored procedure can be used concurrently by many different users.
My question is that " if I have those two options of isolation level (read committed, and serializable), which kind of isolation level should that stored procedure be processed" ?
I need a complete justification as I am stuck which is the best decision to use.
It will be very helpful to get an example for that read committed isolation level, in concurrent processings, won't corrupt the database if used, or it will corrupt the database if serializable isolation level used. Meaning that I need to understand by proving that one of them is the right decision.
Kind regards.
Let's consider that I have a stored procedure that inserts information about a new order submitted by a customer. And this stored procedure can be used concurrently by many different users.
Since you are doing only inserts, the transactions are non-conflicting in nature. So, you need read committed isolation level. This is the default isolation level.
From the documentation:
Read Committed Isolation Level
In the read committed isolation level, which is the default, every query executed by a transaction sees only data committed before the query—not the transaction—began. This level of isolation is appropriate for database environments in which few transactions are likely to conflict.
Serializable Isolation Level
In the serialization isolation level, a transaction sees only changes committed at the time the transaction—not the query—began and changes made by the transaction itself. A serializable transaction operates in an environment that makes it appear as if no other users were modifying data in the database.
Serializable isolation is suitable for environments:
With large databases and short transactions that update only a few rows
Where the chance that two concurrent transactions will modify the same rows is relatively low
Where relatively long-running transactions are primarily read only