Application hangs intermittently when explicit transactions are used

Hello Randy.

While the information you provided is insufficient to determine the exact cause of the issue, I must say that the current XAF design doesn't provide support for explicit transactions. An explicit transaction requires that the session that starts an explicit transaction is an exclusive owner of the database connection (because database transactions are bound connections, not to sessions), but XAF shares the data layer (and thus, the connection) among all sessions. So, once you start an explicit transaction on a session of one object space, all operations performed by other object spaces will be included in this transaction.

Hmmm… that's interesting.  How do XAF CRUD operations ensure atomic transactions? (for parent/child commits, etc.)

But yes, what I was seeing is that there were different process IDs created within the explicit transaction and they were blocking each other on reads.

Is there a way to force in code to use the same data layer with the new ObjectSpace provider and connection pooling?  Create a new datalayer perhaps?

Code is below. Is there any way to make this more resilient to future XAF changes?  It has been working well.  This is inside a process and we wanted to make sure the updates succeed and not get stopped due to user concurrency so setting OptimisticLockField on the Service update.  Other interactive users may get concurrency errors but that's OK.  Unless there is another way to turn off concurrency checking on a persistent object in code then I could use normal XPO logic and not need to do direct SQL.

C#
           using (Session session = new Session(objectSpace.Session.DataLayer))  
            {  
                try  
                {  
// Here is where it was hanging up when trying to read referenced object data on ServiceRecording:  
                    srlocal = session.GetObjectByKey<ServiceRecording>(sr.Oid);  
                    srh = new ServiceRecordingStatusHistory(session);  
                    srh.Mode = smode;  
                    srh.ServiceStatus = session.FindObject<ServiceStatus>(CriteriaOperator.Parse(string.Format("StatusCode = '{0}' AND Division = {1}", lcNewStatus, (int)Divisions.Recording)));  
                    srh.StatusChangeAction = session.FindObject<StatusChangeAction>(CriteriaOperator.Parse(string.Format("ActionCode = '{0}' AND Division = {1}", "PRINTDOC", (int)Divisions.Recording)));  
                    srh.DateChanged = DateTime.Now;  
                    srh.ChangedBy = session.GetObjectByKey<eclipse.Module.BusinessObjects.User>(((eclipse.Module.BusinessObjects.User)SecuritySystem.CurrentUser).Oid);  

                    session.ExplicitBeginTransaction();  
                    srlocal.ServiceRecordingStatusHistory.Add(srh);  
                    srlocal.Save();  
                    session.ExecuteNonQuery(string.Format("{0} {1}", lcUpdateService, lcUpdateWhere));  
                    session.ExecuteNonQuery(string.Format("{0} {1}", lcUpdateServiceRecording, lcUpdateWhere));  
                    session.ExplicitCommitTransaction();  
                }  
                catch (Exception ex)  
                {  
                    llReturn = false;  
                    sr.lCheckStatus = true;  
                    sr.lCheckDupe = true;  
                    cMessage = "Error updating Recording Status or Status History: " + ex.Message;  
                    session.ExplicitRollbackTransaction();  
                }  
            }  

>>>How do XAF CRUD operations ensure atomic transactions? (for parent/child commits, etc.)

XAF uses regular Units of Work and Nested Units of Work (for aggregated objects). When the root unit of work is committed, it collects all pending changes and passes them to the database connection provider at once. The connection provider opens a database transaction, executes all the required commands and commits the transaction.

>>>Is there a way to force in code to use the same data layer with the new ObjectSpace provider and connection pooling?  Create a new datalayer perhaps?

XPObjectSpaceProvider has a constructor that accepts an IXpoDataStoreProvider instance. You can create a custom IXpoDataStoreProvider that uses an externally supplied data layer instead of creating a new one. Refer to the How to customize the underlying database provider options and data access behavior in XAF article for details. However, I don't understand how this may help you with your current issue.

As for improving the code fragment you posted, I cannot suggest anything specific because it is unclear what this code is supposed to do in general, and why you are using the explicit transaction and custom SQL commands here. Please describe your task in detail, and we will do our best to find a solution.

The code, in general, is a way to make this particular set of updates (in a processing loop) take precedence over other end-users updating the same records (concurrency) by incrementing OptimisticLockField manually and using ExecuteNonQuery so that if a user has changed the record this process had already read from database, the process wins (but will only update the fields that are changing, not then entire record).  This is a high volume OLTP application and normally the optimistic locking and concurrency handling is fine, but for this process which is doing bulk printing and status updates it cannot fail in the scenario where a user might be saving the same record just before these updates.

It sounds like I could possibly do something with the XPO objects and maybe try/catch so that if the commit fails I can reload the objects and retry the commits?

Short term, this can stay the way it is as it works find with the current objectspace provider default and explicit transactions, ExecuteNonQuery, etc…  However, long term, I would like to be able to implement the new defaults/recommendations for pooling, better performance, etc.  I just need to have an idea how to re-factor this particular logic to accomplish the same thing with straight ObjectSpace/XPO methods and not run the risk of having the process stop due to user concurrency issues.

Thanks for your update, Randy. We need some additional time to be able to provide further comments. Please with us.