I have a question regarding Transactions and Sybase using LLBLGen Pro Version 2.6 (build 2.6.9.616). We have been developing our app over the last few months and we have started running into some concurrency issues. What we are finding is that the built-in transactions in LLBLGen Pro are overriding our default database Isolation Level. We have the isolation level set to 0 for our database and this is fine for most things. We discovered that the automatic transactions that LLBLGen is creating are setting the Isolation Level on the connection to 1.

This is creating a problem for us because we have some procedures that delete large amounts of data and rebuild it. When these background updates occur, it is encapsulated in a transaction. The transaction is forcing the isolation level to 1 so any subsequent selects that occur during the update are returning with a hard failue "User DBA has the row in table A locked".

The update happens in a matter of a few seconds and we thought it would succeed with the Isolation Level set to 0 (which is what we want). We understand that this could lead to inconsistencies and we have built in concurrency checking to make sure that subsequent updates verify before saving, but we need our Selects to work during the update and not return a hard failure which they are doing now.

I also noticed that we don't have the option to pick from the iAnywhere Isolation Level enumeration. We are forced to use the System.Data. Isolation level enumeration which doesn't have the same corresponding valid values for the Isolation Level that the SAConnection object uses (for example, we can't use IsolationLevel.ReadUncommitted).

My question is are we using this correctly? Is there a better way for us to do this with LLBLGen?

Please post:

• iAnywhere server version
• iAnywhere ADO.NET runtime version
• An example of the offending update code
• An example of the offending fetch code
• The exact exception message and stack trace.

Thank you for the reply, however, my question is really more general than that and isn't related to a specific error per se. Perhaps if I ask the question differently:

LLBLGen does not exposes the iAnywhere Transaction enumeration and forces us to use the System.Data.Isolation level enumeration which doesn't contain the correct values for Sybase. Is there any way to change this behavior?

By Default, when LLBLGen creates a transaction on it's own (all updates and deletes are put in a transaction by LLBLGen if not already included in one) it uses IsolationLevel.ReadCommitted. Is there anyway to change the behavior and make it use the correct IsolationLevel enumeration for Sybase and the one that we want?

We are using Sybase 11 ADO.NET version 11.0.1.22222

The updated code looks like this:

public void PostCalculationBatchSave(EmployeeEntity employee, EntityCollection<EmployeeTotalEntity> employeeTotals)

        {

            adapter.StartTransaction(IsolationLevel.ReadCommitted, "Doing batch delete/save after employee calculation");

            

            try

            {

                DeleteEmployeeTotals(employee.EmployeeId);

                

                DeleteEmployeeIntervals(employee.EmployeeId);



                //save the employee entity along with his intervals

                bool success = false;

                IPredicateExpression concurrencyFilter = CreateConcurrencyFilter(employee);

                if (concurrencyFilter != null)

                    success = adapter.SaveEntity(employee, false, concurrencyFilter);

                else

                    success = adapter.SaveEntity(employee, false);



                //save the employee totals

                adapter.SaveEntityCollection(employeeTotals, false, false);

                adapter.Commit();

            }

            catch (Exception ex)

            {

                adapter.Rollback();

                throw DataExceptionFactory.CreateException(ex);

            }

        }

This is the code that is trying to fetch while the above transaction is in progress.

public EntityCollection<EmployeeEntity> FetchEmployeesForTimeCard(int payclassId, int payperiodId)

        {

            EntityCollection<EmployeeEntity> employees = new EntityCollection<EmployeeEntity>(new EmployeeEntityFactory());

            IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.EmployeeEntity);



            //ISortExpression intervalSort = new SortExpression();

            //intervalSort.Add(IntervalFields.CalculatedDay | SortOperator.Ascending);

            PredicateExpression employeeFilter = new PredicateExpression();

            employeeFilter.Add(EmployeeFields.PayclassId == payclassId);



            PredicateExpression employeeTotalFilter = new PredicateExpression();

            employeeTotalFilter.Add(EmployeeTotalFields.PayperiodId == payperiodId);



            PredicateExpression approvedFilter = new PredicateExpression();

            approvedFilter.Add(ApprovedEmployeeFields.PayPeriodId == payperiodId);

            prefetchPath.Add(EmployeeEntity.PrefetchPathApprovedEmployee, Int32.MaxValue, approvedFilter);



            RelationPredicateBucket bucket = new RelationPredicateBucket();

            bucket.PredicateExpression.Add(employeeFilter);

            //bucket.PredicateExpression.Add(employeeTotalFilter);



            prefetchPath.Add(EmployeeEntity.PrefetchPathEmployeeTotals, Int32.MaxValue, employeeTotalFilter);

            prefetchPath.Add(EmployeeEntity.PrefetchPathDepartment);

            prefetchPath.Add(EmployeeEntity.PrefetchPathEmployeeTypes);



            adapter.FetchEntityCollection(employees, bucket, Int32.MaxValue, null/*sort*/, prefetchPath);

            

            return employees;

        }

The errors are random and depend on somebody doing something at the same time. I can't generate them on demand per se, but we can set it up. I'm not at the office right now so I can't force an error but hope this gives enough to go on.

Thanks,

Allen

Yes you can, with a bit of manual coding.

Create a subclass of DataAccessAdapter and override CreateNewPhysicalTransaction. First call the base' version, which will create the DbTransaction object for you. You can now set the isolation level on the object. For example you add a property to the subclass of DataAccessAdapter and set the isolation level to that property's value.

This is the only place where transactions are created so it should work in any situation.

Thanks Frans for the reply. We will try that and see how that goes.

Thanks again!