I have a BusinessListBase class which I want to fetch a number of children, and at the same time update a status property and commit this transactionally.
Essentially I am fetching a batch and ensuring that no-one else will fetch these same items.
How would I go about doing this?
Inside the DataPortal_Fetch I can open a new SqlConnection and create a new Transaction to fetch and populate the children. Then within that I need to change a property on each of the children and Save the whole list. But won't calling Save on the BusinessListBase from within the DataPortal_Fetch cause problems?
Although you could do this, I highly recommend that you do not. What you are describing can lead to deadlocks. In general pattern of fetching with transaction isolation level of serializable, which you will need to issue exclusive locks, while also updating some data, is prone to deadlocks. You probably want to just issue transnational update only, then close connection, the close new connection and fetch. Really, the best way is to do this in a store procedure that would issue an update with the OUTPUT clause you can fetch from. This will virtually guarantee no deadlocks.
Yes, I could do it in a Stored Procedure, but it's exactly what I was trying to avoid.
Assuming the Fetch query I issued was using the UPDLOCK hint to avoid deadlocks, what would the process be from within the DataPortal_Fetch to issue the updates?
I think the same as in any other DP_Fetch, you would talk to the database to issue the updates, then fetch the same data?
Copyright (c) Marimer LLC