Есть простой процесс:

1. Снять с №1 счета X.
2. Увеличить №2 счет на X.
3. Записать текущие остатки на счетах №1 и №2.

Проблема в том, что счет №1 системный и на него зачисляют/снимают по множеству раз в секунду. Если использовать оптимистическую блокировку в Entity Framework, то при SaveChanges() постоянно вываливается DbUpdateConcurrencyException (так как кто-то другой успел изменить раньше нас). После DbUpdateConcurrencyException, конечно, перезапускаем вставку и в конечном счете все проходит, однако эти перезапуски приводят к падению скорости в 20 раз (!) даже по сравнению с обычной блокировкой (lock) и записью в один поток.

И исполнение данных в транзакции не решает проблему: ни один вид транзакций (SERIALIZABLE, REPEATABLE READ, READ COMMITTED) не запрещает читать то что мы прочитали или изменять то что мы уже изменили (но еще не читали). То есть опять таки будет множество перезпусков транзакций, которые приведут к лишней работе и потере производительности.

Что делать? Есть ли что лучшее, чем использование ручной блокировки "EXEC sp_getapplock" и исполнения в одном потоке?

Здравствуйте, Аноним, Вы писали:

А>Что делать?

Выкинуть EF и использовать bltoolkit/linq2db для генерации нормальных UPDATE/INSERT стейтментов.

Здравствуйте, IT, Вы писали:

IT>Выкинуть EF и использовать bltoolkit/linq2db для генерации нормальных UPDATE/INSERT стейтментов.

Да можно и вручную. Но что это изменит?

Сначала нужно прочитать, потом изменить. Так вот, пока мы читаем, кто-то другой сможет прочесть раньше нас и заблокировать изменение. Пока дойдем до изменения -- данные станут не актуальными и потребуется перезапуск.

Нужна какая то блокировка на чтение и изменение, как я понимаю... Или хотя бы на изменение тех данных, которые мы изменили.

Здравствуйте, Аноним, Вы писали:

А>Сначала нужно прочитать, потом изменить. Так вот, пока мы читаем, кто-то другой сможет прочесть раньше нас и заблокировать изменение. Пока дойдем до изменения -- данные станут не актуальными и потребуется перезапуск.

Это изменение можно сделать одним SQL запросом без EF?

Здравствуйте, Аноним, Вы писали:

А>Сначала нужно прочитать, потом изменить.

А если сначала изменить, потом прочитать -- то нет гарантии, что кто-то не изменил кроме нас. Ведь транзакция не блокирует на изменение записи, которые не были прочитаны.

Здравствуйте, IT, Вы писали:

IT>Это изменение можно сделать одним SQL запросом без EF?

Получается 2 чтения (можно 1 запросом), 2 изменения, одна вставка. Разве что в хранимой процедуре. Но какая разница 3 запроса в хранимой или без?

Здравствуйте, Аноним, Вы писали:

А>Что делать? Есть ли что лучшее, чем использование ручной блокировки "EXEC sp_getapplock" и исполнения в одном потоке?

Посмотри вот здесь

http://www.codeproject.com/Articles/114262/6-ways-of-doing-locking-in-NET-Pessimistic-and-opt#How%20can%20we%20do%20pessimistic%20locking

Здравствуйте, Pavel Dvorkin, Вы писали:

PD>Посмотри вот здесь

PD>http://www.codeproject.com/Articles/114262/6-ways-of-doing-locking-in-NET-Pessimistic-and-opt#How%20can%20we%20do%20pessimistic%20locking

А что именно?

Здравствуйте, Аноним, Вы писали:

А>А что именно?

Пессимистическая блокировка и isolation level не подойдет ?

Кстати, есть еще SELECT FOR UPDATE, но для MS SQL о ней нехорошо отзываются.

http://stackoverflow.com/questions/1483725/select-for-update-with-sql-server

Здравствуйте, Pavel Dvorkin, Вы писали:

PD>Пессимистическая блокировка и isolation level не подойдет ?

Блокировать с помощью BEGIN TRAN? Это не помогает избежать необходимости перезапусков транзакции (в случае неудачи).

То есть 2 процесса одновременно считывают данные, так как блокировка на чтение не поддерживается. Потом однин из процессов изменяет данные (второй ждет в очереди). Пока подошла очередь второго, данные станут не актуальными и ему вновь прийдется выполнять работу с самого начала (чтение и пр.).

Как иначе заблокировать, чтобы не могли читать одновременно? Только EXEC sp_getapplock?

Здравствуйте, Аноним, Вы писали:

А>Что делать? Есть ли что лучшее, чем использование ручной блокировки "EXEC sp_getapplock" и исполнения в одном потоке?

Использование оптимистической блокировки для данных, которые часто меняются — очевидный антипаттерн. Вы теперь сами видите, почему.
EF по определению является кэширующим контекстом данных, предназначен для других задач и потому пессимистические блокировки нативно не поддерживает.

Тупым решением для Вас является да, эксклюзивная блокировка записей до момента завершения транзакции (придется вставить raw SQL с хинтами):

private readonly AccountDbContext _ctx = new AccountDbContext();

public string MoneyTransfer()
{
    Debug.WriteLine("Transfer started");

    var sw = new Stopwatch();
    sw.Start();

    using(var scope = new TransactionScope())
    {
        var acc1 = this._ctx.Accounts.SqlQuery("select * from Accounts with (rowlock, xlock) where ID = 1").Single();
        Debug.WriteLine("Balance1: " + acc1.Balance);

        var acc2 = this._ctx.Accounts.SqlQuery("select * from Accounts with (rowlock, xlock) where ID = 2").Single();
        Debug.WriteLine("Balance2: " + acc2.Balance);

        acc1.Balance -= 1;

        Thread.Sleep(TimeSpan.FromSeconds(10));

        acc2.Balance += 1;

        Debug.WriteLine("New Balance1: " + acc1.Balance);
        Debug.WriteLine("New Balance2: " + acc2.Balance);

        this._ctx.SaveChanges();
        Debug.WriteLine("New balances saved to DB");

        scope.Complete();
        Debug.WriteLine("Transaction commited");
    }
    sw.Stop();

    return "Money transferred successfully in " + sw.ElapsedMilliseconds + " ms!";
}

И тогда если параллельно запустить два трансфера, то имеем в аутпуте характерное:

    Transfer started
    Balance1: 75,00
    Balance2: 25,00
    Transfer started
    New Balance1: 74,00
    New Balance2: 26,00
    New balances saved to DB
    Transaction commited
    Balance1: 74,00
    Balance2: 26,00
    New Balance1: 73,00
    New Balance2: 27,00
    New balances saved to DB
    Transaction commited

А по-правильному в действительно высоконагруженных системах используют известный паттерн, название которого я забыл. Хранят баланс не в виде поля в таблице, а в виде отдельной таблицы. И в эту таблицу кладут текущее значение баланса, а также изменения:

UserId Balance
--------------
VASYA 100500
VASYA +10
VASYA -100

И изменения периодически схлопываются.
Это позволяет при списывании или зачислении ничего не блокировать, а блокировать только при схлопывании (гораздо реже).

P.S. — воистину, классика никогда не стареет! Пройдут годы, может быть, столетия — а наши правнуки по-прежнему будут на собеседованиях спрашивать друг друга, как перечислить деньги со счета на счет

Здравствуйте, scale_tone, Вы писали:

_>P.S. — воистину, классика никогда не стареет! Пройдут годы, может быть, столетия — а наши правнуки по-прежнему будут на собеседованиях спрашивать друг друга, как перечислить деньги со счета на счет

А если данные по счетам разнесены по 2+ серверам (типа шардинг), без собственного велосипеда не обойтись, или тоже есть паттерны?

Здравствуйте, agat50, Вы писали:

A>А если данные по счетам разнесены по 2+ серверам (типа шардинг), без собственного велосипеда не обойтись, или тоже есть паттерны?

Это уже вопрос организации транзакции. Если шардинг нативный SQL Server-ный — то SQL Server сам обо всем заботится.
Если шардинг кастомный — тогда нужна распределенная транзакция.

Вопрос обеспечения эксклюзивной блокировки записи (чтобы клиенты не отваливались с исключением, а выстраивались в очередь) с локальностью/распределенностью транзакции никак не связан.

Здравствуйте, Аноним, Вы писали:

А>Как иначе заблокировать, чтобы не могли читать одновременно? Только EXEC sp_getapplock?

См. ответ scaletown.

Здравствуйте, Аноним, Вы писали:

А>Здравствуйте, Pavel Dvorkin, Вы писали:

PD>>Пессимистическая блокировка и isolation level не подойдет ?

А>Блокировать с помощью BEGIN TRAN? Это не помогает избежать необходимости перезапусков транзакции (в случае неудачи).

А>То есть 2 процесса одновременно считывают данные, так как блокировка на чтение не поддерживается. Потом однин из процессов изменяет данные (второй ждет в очереди). Пока подошла очередь второго, данные станут не актуальными и ему вновь прийдется выполнять работу с самого начала (чтение и пр.).

А>Как иначе заблокировать, чтобы не могли читать одновременно? Только EXEC sp_getapplock?

Можно поставить serializable уровень изоляции, тогда фактически каждое обращение к записи будет вызывать эксклюзивную блокировку.

Но лучше сразу хранить в виде двойной записи, а остатки получать через индексированные view.

Читать тут: http://rsdn.ru/article/db/RDBMS.xml

Автор(ы): Владислав Чистяков

Статье уже 10 лет, а до сих пор не все читали и не все используют.

Здравствуйте, Аноним, Вы писали:

А>Здравствуйте, IT, Вы писали:

IT>>Это изменение можно сделать одним SQL запросом без EF?

А>Получается 2 чтения (можно 1 запросом), 2 изменения, одна вставка. Разве что в хранимой процедуре. Но какая разница 3 запроса в хранимой или без?

дело в том, что не надо читать то, что было до того, как операция совершена.
ну просто незачем, т.к. это то что получилось плюс/минус (зависит от счета) дельта
в таком разрезе Read Commited вполне себе работает (второй апдейт будет ждать выполнения первого).

для BLT/linq2db это выглядит примерно так, для EF не знаю можно ли оно:

using(var db = new DbManager())
{
    db.BeginTransaction();
    var acs = db.GetTable<Account>();

    var upd  = acs
      .Where(a => a.Id = @id /*&& a.Amount >= @delta*/) //если нужно проверить что на счету достаточно денег
      .Set(a => a.Amount, a => a.Amount - @delta)
      .Update(); 

    if (upd == 0)
       throw new OperationException("Not enough money"); //если нужно проверить что на счету достаточно денег

     var upd  = acs
      .Where(a => a.Id).Select(a => a).First();
   
//.....
}

Здравствуйте, gandjustas, Вы писали:

G>Можно поставить serializable уровень изоляции, тогда фактически каждое обращение к записи будет вызывать эксклюзивную блокировку.

Ошибаетесь. Serializable гарантирует:

1. Инструкции не могут считывать данные, которые были изменены другими транзакциями, но еще не были зафиксированы.
2. Другие транзакции не могут изменять данные, считываемые текущей транзакцией, до ее завершения.
3. Другие транзакции не могут вставлять новые строки со значениями ключа, которые входят в диапазон ключей, считываемых инструкциями текущей транзакции, до ее завершения.

Отсюда.

Превое не запрещает ЧИТАТЬ данные, которые были уже ПРОЧИТАНЫ в другой транзакции.
Второе на запрещает ИЗМЕНЯТЬ данные, которые уже были ИЗМЕНЕНЫ в другой транзакции. Читать нельзя, а вот изменять -- пожалуйста.

Получается, если мы сначала прочитаем -- то и другой сможет прочитать. Если сначала изменим (без чтения) -- то и другой сможет изменить.

G>Но лучше сразу хранить в виде двойной записи, а остатки получать через индексированные view.

А как предотвратить баланс нижу нуля? Все равно блокировка нужна.

Здравствуйте, gandjustas, Вы писали:

G>Можно поставить serializable уровень изоляции, тогда фактически каждое обращение к записи будет вызывать эксклюзивную блокировку.
Неверно. При чтении (в ms sql) будет intent lock, если явно не сказать, что нужен эксклюзивный.

Здравствуйте, ili, Вы писали:

ili>для BLT/linq2db это выглядит примерно так, для EF не знаю можно ли оно:

ili>

ili>using(var db = new DbManager())
ili>{
ili>    db.BeginTransaction();
ili>    var acs = db.GetTable<Account>();

ili>    var upd  = acs
ili>      .Where(a => a.Id = @id /*&& a.Amount >= @delta*/) //если нужно проверить что на счету достаточно денег
ili>      .Set(a => a.Amount, a => a.Amount - @delta)
ili>      .Update(); 

ili>    if (upd == 0)
ili>       throw new OperationException("Not enough money"); //если нужно проверить что на счету достаточно денег

ili>     var upd  = acs
ili>      .Where(a => a.Id).Select(a => a).First();
   
ili>//.....
ili>}
ili>

Дело в том, что любой уровень изоляции позволяет ИЗМЕНЯТЬ то, что мы уже изменили (но не читали). То есть нет гарантии, что перед "var upd = acs.Where(a => a.Id).Select(a => a).First()" кто-то другой не выполнит еще одно обновление.

Здравствуйте, Shmj, Вы писали:

S>Дело в том, что любой уровень изоляции позволяет ИЗМЕНЯТЬ то, что мы уже изменили (но не читали). То есть нет гарантии, что перед "var upd = acs.Where(a => a.Id).Select(a => a).First()" кто-то другой не выполнит еще одно обновление.

тест, открываем 2 соединения в Management Studio, и пишем такой скрипт:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED 
GO
BEGIN TRANSACTION;
GO

UPDATE Account SET Amount = Amount + 1 WHERE AccountId = 1

SELECT * FROM Account WHERE AccountId = 1

GO

COMMIT TRANSACTION;
GO

Допустим начальный Amount = 0

Выполняем первый до селекта
Выполняем второй до селекта (второй висит)
Выполняем селект в первом, получаем 1 (второй висит)
Выполняем коммит в первом, (второй отвисает)
Выполняем селект во втором, получаем 2
Выполняем коммит во втором

Вроде как всё хорошо?...

а вот если вы попытаетесь сделать так:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED 
GO
BEGIN TRANSACTION;
GO

DECLARE @amnt decimal(18, 2)

SELECT @amnt = Amount FROM Account WHERE AccountId = 1

UPDATE Account SET Amount = @amnt + 1 WHERE AccountId = 1

SELECT * FROM Account WHERE AccountId = 1

GO

COMMIT TRANSACTION;
GO

то на тех же результатах вы получите грязные данные, т.к. таки да, первые селекты в обоих транзакциях выполнятся без всяких блокировок