Select and update by one transaction

c# entity-framework entity-framework-6 sql-server transactions

Question

I have some product to sale and I want to check existing of product in store. I use Entity Framework and SQL server. I want some think like this.

IF ((SELECT TOP 1 Amount FROM tblProduct WHERE Id =1)>0)
UPDATE tblProduct
SET count = count -1
WHERE Id = 1

But this is not in one transaction and when users want to buy in same time, I can't check it correctly.I tried this:

BEGIN TRANSACTION
IF ((SELECT TOP 1 Amount FROM tblProduct WHERE Id =1)>0)
    UPDATE tblProduct
    SET count = count -1
    WHERE Id = 1
COMMIT

but is not true.How can I do it in entity framework. Thanks

1
2
11/29/2018 10:59:34 AM

Accepted Answer

As I understand you are trying to decrement product count on every sell operation. You need to do this atomically because you need to avoid selling a product which out of stock.

There are several options like optimistic lock with keeping version on your data, or distributed lock for providing one buy operation on an item.

In sql you can run update statement with versioning like below:

update [table]
set count = count - 1
where id=1 and count=5 and version=3

Every update locks the row so if another update request comes in action it waits current update and when executed it affects 0 row.

You can also use a distributed lock with redis and lock the process itself.

It depends on requirements, use cases and your resources.

Update

For doing this using entity framework, if you use code first you can do the below approach:

// add the below property to your entity
[Timestamp]
public byte[] RowVersion { get; set; }

with fluent api:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<YourEntity>().Property(s => s.RowVersion).IsConcurrencyToken();
    //modelBuilder.Entity<YourEntity>().Property(s => s.RowVersion).IsRowVersion(); // in EF6 or EF Core
    base.OnModelCreating(modelBuilder);
}

Or you can use [ConcurrencyCheck] attribute:

[ConcurrencyCheck]
public int Version { get; set; }
[ConcurrencyCheck]
public int Count { get; set; }

EF will add this columns to where condition in your update queries. If affected row count is 0 EF throws DbUpdateConcurrencyException you need to catch it.

Take a look for further information:

https://docs.microsoft.com/en-us/ef/core/modeling/concurrency

Optimistic concurrency: IsConcurrencyToken and RowVersion

https://www.codeproject.com/Articles/817432/Optimistic-Concurrency-in-Entity-Framework-Code-Fi

http://www.binaryintellect.net/articles/14e67064-634c-4206-9eca-a42d3739594a.aspx

2
11/29/2018 1:24:06 PM

Popular Answer

You can try some other ways of implementing this. For example, perform the update only, when there is quantity:

UPDATE tblProduct
SET count = count -1
WHERE Id = 1
    AND Amount > 0;

In this way, there is no need from the select statement. Or, you can add a check constraint to ensure count is always > 0 and leave the SQL Engine to apply the ACID properties. If somehow, a user (transaction) try to decrease the count below 0, an error is going to be thrown and you can catch the error in the application (for example).



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow