I have .net core api that uses NHibernate, session is per request, when request start session is open at the end of the request session is committed in transaction.
One of the tables is being used as cache table so rows should be deleted in some cases.
One case is when getting data from another api crossing results with data in the table in order to take the newest rows according to date updated field, each older row is being deleted as part of this process. Another case is a background service that gets events in order to indicate that the record is older and need to be deleted.
The issue is that in the same time when deleting older rows other request can actually update this row with new values.
Of curse that this scenario cause an exception:
NHibernate.StaleObjectStateException: 'Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect)
What is the best way to solve this situation and detect that row has been deleted before SaveOrUpdate from another request at the same time?
Thank you