Performing "atomic" operation "IncreaseIf" on database

Question

I need to perform atomic operation check value of some field of some entity framework model and increase it if its value is 0.

I thought about transactions, sth like:

bool controlPassed = false;
using (TransactionScope scope = new TransactionScope())
{
    var model = ...ModelEntities.first_or_default(...)
    if (model.field == 0){
        ++model.field;
        ...saveChanges();
        controlPassed = true;
    }
    scope.Complete();
}
if (controlPassed)
{
    ...
    using (TransactionScope scope = new TransactionScope())
    {
        --model.field;
        ...saveChanges();
        scope.Complete();
    }
}

Of course, everything in try catch and so on.

My question is: how would it work?

It is really hard to check. I have multithreaded application.

Is there a possibility, that two or more threads would pass control (check that field == 0 and increase it)?

Whout would be blocked in database (database, table, row, field)?

I can't let two or more threads to be in controlPassed section simultaneously.

Answer 1

Is there a possibility, that two or more threads would pass control (check that field == 0 and increase it)?

You have Serializable transaction (that is default for TransactionScope). It means that there can be two threads with field == 0 but immediately after that deadlock happens because transaction for the first thread holds a shared lock on the filed and transaction for the second thread holds another shared lock for the same field. Neither of these transaction can upgrade the lock to exclusive to save changes because they are blocked by shared lock in other transaction. I think same would happen for RepeatableRead isolation level.

If you change isolation level to ReadCommitted (the default for SaveChangeswithout TransactionScope when using MS SQL Server) the answer will be again yes but this time without deadlock because EF uses normal selects without any table hints - that means that no lock on record is held when select completes. Only save changes (modification) locks records until transaction commits or rolls back.

To lock record in ReadCommitted transaction with select you must use native SQL query and UPDLOCK (to lock record for update during select and held it until transaction ends) table hint. EF queries do not support table hints.

Edit: I wrote a long article about pessimistic concurrency which describes why your solution doesn't work and what must be changed to make it work correctly.