How can I do a read-create transaction with Npgsql?
In an ASP.NET Core 6 website which uses EF Core (Npgsql) I need to do the following scenario.
There is a table which holds users' data of some type. Each row of this table (per user) has a calculated column which uses the previous row's value. This opens up the race condition problem. I need to read the latest row (for a user), then create the new row and then insert it into DB. And during this time I need an assurance that the created row is the last row and no two rows are created at the same time.
How can I achieve this with EF Core?
I have read about Postgresql's table locks which are not supported in EF and slow down the app anyway (not recommended). Another thing that I read about is transactions, but every example I've seen uses it for updating or creating multiple rows, I don't know if this scenario can be handled with transactions.
One simple way to model this, is to have a composite key consisting of the user ID and a user row ID, which the application will increment client-side. In this model, your application simply loads the latest row, and then attempts to insert a new row for that user with an incremented value. If two threads attempt to do this concurrently, one will get a unique index violation, and can retry again or whatever.
So let's assume there's user id 4, and their latest row has user row id 10. You code loads that, and then attempts to insert a new row with user id 4 and user row id 11. Since there's a composite key defined over the two columns, only one such insert can succeed.
This should work just fine in EF.