Problems with correctly implementing pessimistic locking

[Matthew]

Hi there,

I've had some issues with implementing pessimistic locking with OpenAccess. I'm using C#.NET and have not found anything online that clearly shows how should do this.

I've set the backend settings on my model diagram to PESSIMISTIC EXPLICIT with REPEATABLE READ. but this doesn't seem to fix the issue

I have an order lines object with quantity and quantityDelivered properties. I don't want to deliver an order if quantity =< quantityDelivered. I only want to deliver an order and increment quantityDelivered If quantityDelivered is less than quantity.

I get my orderLines object, check quantityDelivered < quantity, do some stuff, increment quantityDelivered.

How can I do this? Can I lock the orderLines object using Transaction.Lock? I've had a look previously but was never successful.

I've been confused with the mixture of old and new documentation and nothing is particularly clear!

Thanks for your help

[Ralph Waldenmaier]

Hi Matthew,

The good news first. You can achieve the desired goal to manually lock objects on the database and then do your business logic.
The bad news is, that for the moment, you have to use the ObjectScope to achieve this.
See the following example how to do this:

var ctx = new LockingExampleContext();
using (IObjectScope s = ctx.GetScope().Database.GetObjectScope())
{
    s.TransactionProperties.Concurrency = TransactionMode.PESSIMISTIC_EXPLICIT;

    s.Transaction.Begin();
    var product = new Product() { ProductName = "product to be locked" };
    s.Add(product);
    s.Transaction.Commit();

    s.Transaction.Begin();
    s.Transaction.Lock(product, LockMode.WRITE);
    product.ProductName = "my changed string";
    s.Transaction.Commit();
}

In order to be able to do this also with the OpenAccessContext in the future, I have created a feature request in our feedback portal. Please vote for it in order to give it more attention.

I hope this information is helpful for you.
Do come back in case you need further assistance.Regards,
Ralph
Telerik

[Matthew]

Thanks for your reply Ralph, I'll take a look with your suggestion and let you know how I get on. I liked the feature request also!

[Allen]

Hi Ralph Waldenmaier
The GetScope is a protected method, I can't call it, please say me how to do?
Thank

[Ralph Waldenmaier]

Hello Zhen Peng,
To use the GetScope() method in your context, you can create a partial context class and expose the protected scope explicitly. See this example:

public IObjectScope GetScope()
{
    return base.GetScope();
}

Hope this helps.
Do come back in case you have any further questions.

Regards,
Ralph Waldenmaier
Telerik

 

 

[Allen]

Oh,Thank you very much.
I can't believe I didn't think about this before.
I was so stupid

[Allen]

Hello, Ralph Waldenmaier.

public static bool Add(IEnumerable<AdvertisementImage> list, int shopId, byte configType)
{
    bool result;
    using (CityMallContext context = ContextFactory.CreateContext())
    {
        using (IObjectScope s = context.GetScope().Database.GetObjectScope())
        {
            try
            {
                s.Transaction.Begin();
                var queryable = context.AdvertisementImages.Where(t => t.ShopId == shopId && t.ConfigType == configType);
                foreach (var item in queryable)
                {
                    s.Remove(item);
                }
                foreach (var item in list)
                {
                    s.Add(item);
                }
                s.Transaction.Commit();
                result = true;
            }
            catch (Exception)
            {
                s.Transaction.Rollback();
                result = false;
            }
        }
    }
    return result;
}

in s.Remove(item); Throw Exception：
Object references between two different object scopes are not allowed. The object 'Allen.Data.AdvertisementImage' is already managed by 'ObjectScopeImpl 0x2 OpenAccessRuntime.EnlistableObjectScope' and was tried to be managed again by 'ObjectScopeImpl 0x3 OpenAccessRuntime.ObjectScope'.
Why,help me.
Thank very much.

[Allen]

Hello, Ralph Waldenmaier

I monitor SQL execution process, Let us look at this picture, here.
We take a look at this code：

public static bool Add(AdvertisementImage[] images, int shopId, byte configType)
{
    bool result;
    using (CityMallContext context = ContextFactory.CreateContext())
    {
        try
        {
            var queryable = context.AdvertisementImages.Where(t => t.ShopId == shopId && t.ConfigType == configType);
            context.Delete(queryable);
            context.Add(images);
            context.SaveChanges();
            result = true;
        }
        catch (Exception)
        {
            context.ClearChanges();
            result = false;
        }
    }
    return result;
}

Very obvious, The code, first delete, then add. However, TDA is first added, then delete. This is the first question.
The second question is:
The SaveChanges method is use transaction processing?
How do I test whether it uses a transaction?

Thank you.
respect greetings

[Ralph Waldenmaier]

Hi Allen,
To answer your first question: In the example code you are working with the scope and the context in parallel. This is not supported. A object should be maintained by only one instance of either scope or context.

To your previous question: When working with the context, you are always using transactions implicitly. See this link for details. In case you are working with the scope approach, you have to handle them by yourself. See this link for more details.

Hope this helps.
Do come back in case you need further assistance.

Regards,
Ralph Waldenmaier
Telerik

 

 

[Allen]

Thank,Ralph Waldenmaier.
Linked content, a great help to me.
I improved code.

public static bool Add(IEnumerable<AdvertisementImage> list, int shopId, byte configType)
{
    bool result;
    using (IObjectScope objScope = Context.GetObjectScope())
    {
        ITransaction tran = objScope.Transaction;
        try
        {
            objScope.TransactionProperties.Concurrency = TransactionMode.PESSIMISTIC_EXPLICIT;
            tran.Begin();

            var queryable = objScope.Extent<AdvertisementImage>().Where(t => t.ShopId == shopId && t.ConfigType == configType).ToArray();
            tran.Lock(queryable, LockMode.WRITE);
            foreach (var item in queryable)
            {
                item.Url = "test1";
                item.Value = "test2";
                objScope.Add(item);//Update old data
            }

            foreach (var item in list)
            {
                objScope.Add(item);//Insert new data
            }

            tran.Commit();
            result = true;
        }
        catch (Exception)
        {
            if (tran.IsActive)
            {
                tran.Rollback();
            }
            result = false;
        }
    }
    return result;
}

Why executive order is not the same?
TDA was first inserted and then update.
I was first updated and then insert.
No impact?

[Ralph Waldenmaier]

Hello Allen,
First I am glad the provided information helped you to proceed.

The order of the executed operations against the database is not following the order of operations done within your code. The reason for that is, that we have to check the correct order by dependencies between the objects. In case you have references, then we have to ensure that they are provided correctly against the database which would otherwise cause constraint errors.

In your case, the ordering is calculated internally using the same approach and thus you see a difference in how you specified the execution and how it is executed against the database.


Regards,
Ralph Waldenmaier
Telerik

 

 

[Allen]

I am so excited, solves the problem, thank you. R.W.

[Ralph Waldenmaier]

Hi Allen,
I am glad that I was able to help.

Feel free to ask in case you have any other question.

Regards,
Ralph Waldenmaier
Telerik