Scenario: A save function first reads the total number of rows from a table in the db. Based on the number of rows, a unique input value is calculated. This value is included in the row that is then inserted into the same table.
Problem: Two instances of the same object executes the save function at the same time. Here "same time" is the amount of time that it takes the application to read from and insert a row into the db. So, for example,
User A clicks save button >> Reads that there are 3 rows in the table >> Calculates f(3) as a unique input value for the new row >> Prepares the new row to insert into the table ...
User B clicks save button >> (since User A has not inserted the new row) Reads that there are 3 rows in the table >> Calculates f(3) which is NOT a unique value (ERROR!) >> ...
How do I rectify this situation? I am not able to alter the db transaction logic since an abstracted ORM layer is being used unfortunately. I have full control over the code however. Any suggestion will be very helpful.
I have anready tried putting a lock() around the save function, but its not helping somehow.
I ended up leveraging the DB generated ID (auto-increment) to do my calculations. Since, rowcount and ID were out of sync, adjusted the new custom id by adding an integer to the new sequence to match the old sequence.