Can two transactions occur at the same time? Let's say you have transactions A and B, each of which will perform a read to get the max value of some column then a write to insert a new row with that max+1. Is it possible that A performs a read to get the max, then B performs a read before A writes, causing both transactions to write the same value to the column?
Doing this with isolation level set to read uncommitted to false seems to prevent duplicates, but I can't wrap my head around why.
Can two transactions occur at the same time?
Yes, that is quite possible and in fact it is required for all the RDBMS to support that feature out of the box to speed up things. Think about an application accessed by Thousands of users simultaneously, if everything goes in sequence the users may have to wait day in order to get the response.
Let's say you have transactions A and B, each of which will perform a read to get the max value of some column then a write to insert a new row with that max+1. Is it possible that A performs a read to get the max, then B performs a read before A writes, causing both transactions to write the same value to the column?
If A & B are happening into two different sessions, its quite possible user case.
Doing this with isolation level set to read uncommitted to false seems to prevent duplicates, but I can't wrap my head around why?
I think, your requirement to get next increment number with isolation block is quite common, and here you need to instruct database to do a mutual exclusive read operation for writing operation has to happen, you could instruct the database to do it, by setting isolation, or may be 'temporary isolation' level should solve your.
If gettting next number is only problem and you don't have other constrained then
My Sql AUTO_INCREMENT would be best suited answer for you.
But it seems, you have asked this question specifically, means, you may have constrained.
Refer my similar questions and answer.
Your solution should be something like below.
begin;
select last_number from TABLE1 ... FOR UPDATE;
Read the result in App.
update TABLE1 set last_number=last_number+1 where ...;
commit;
