Вопрос про транзации и deadlocks
От: Divineshadow  
Дата: 08.07.15 13:57
Оценка:
Здравствуйте.
Есть таблица с двумя полями: id — автоинкремент, и поле value1 — ну например строковое значение.
Если запустить функцию в нескольких потоках, которая добавляет запись и возвращает новый id, то довольно часто генерируется исключение:
"Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction"
А если попытаться в management studio сделать select всей таблицы, то это исключение генерируется гарантировано

Используемый IsolationLevel = Serializable — и понятно, что когда таблица занята транзакцией из одного потока, то другой не может к ней обратиться.
Вернее он ждёт её освобождения. По крайнее мере я так понимаю принцип работы транзакций с уровнем Serializable.

Объясните пожалуйста, почему возникает исключение и что я не так не так понимаю?

Вот псевдокод функции:
private static int ProcessTableRecord(DbObject dbObject)
        {
            Connection connection = BeginTransaction(ConnectionString, Connection.IsolationLevel.Serializable);

            try
            {
                var table = connection.Select(string.Format("SELECT * FROM Table1 WHERE value1 = '{0}'", dbObject.Value1));

                if (table.Rows.Count > 0)
                    return Convert.ToInt32(table.Rows[0]["id"]);

                int id = connection.InsertIdentity("Table1", dbObject);

                return id ;
            }
            catch
            {
                connection.RollbackTransaction();
                return -1;
            }
            finally
            {
                connection.CommitTransaction(); //если был rollback, то коммита не будет
            }
        }
Re: Вопрос про транзации и deadlocks
От: vsb Казахстан  
Дата: 08.07.15 14:05
Оценка: 1 (1)
Здравствуйте, Divineshadow, Вы писали:

D>Используемый IsolationLevel = Serializable — и понятно, что когда таблица занята транзакцией из одного потока, то другой не может к ней обратиться.

D>Вернее он ждёт её освобождения. По крайнее мере я так понимаю принцип работы транзакций с уровнем Serializable.

Это очень медленный подход к изоляции транзакций — разрешать работать с таблицей только одному клиенту. Если для вас такое подходит — самостоятельно делайте лок на таблицу, все базы такое умеют.

Базы лочат не таблицу, а строки. Могут и таблицу залочить, если посчитают нужным, но стараются лочить записи, чтобы транзакции, работающие с разными строками могли работать параллельно.

D>Объясните пожалуйста, почему возникает исключение и что я не так не так понимаю?


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

Нужно просто перезапускать транзакцию в цикле и всё. Не выполнилась в первый раз, выполнится во второй. Ну или не использовать SERIALIZABLE уровень, другие уровни более снисходительны к таким нарушениям изоляции.
Re: Вопрос про транзации и deadlocks
От: Tigor Россия  
Дата: 08.07.15 14:13
Оценка: -1
Здравствуйте, Divineshadow, Вы писали:

D>Используемый IsolationLevel = Serializable — и понятно, что когда таблица занята транзакцией из одного потока, то другой не может к ней обратиться.

D>Вернее он ждёт её освобождения. По крайнее мере я так понимаю принцип работы транзакций с уровнем Serializable.

Это не совсем так. А точнее, совсем не так.
MS SQL сервер реализует так называемую оптимистик конкарренси. Об этом лучше почитать, но если совсем на пальцах, то Serializable (то есть иллюзия последовательного выполнения транзакций) гарантируется не их реальным последовательным выполнением, а откатом всех конфликтующих транзакций кроме одной, когда что-то пошло не так.
Что Вы и наблюдаете.

Чтобы залочить таблицу целиком, придется использовать хинт (как один из вариантов).
Вам оно точно нужно? Нельзя использовать идентити колонку?
Сорри, исправляюсь, identity колонка и так есть и вторая колонка строковая.

Можете добавить уникальный индекс на вторую колонку?
Это должно несколько помочь.
Еще можно совместить проверку на существование и добавление новой строки в один оператор.
Это может помочь еще чуть-чуть засчет увеличения скорости выполнения.

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

Еще есть оператор merge.
К сожалению, в действительности все выглядит иначе, чем на самом деле.
Отредактировано 08.07.2015 14:30 Tigor . Предыдущая версия . Еще …
Отредактировано 08.07.2015 14:29 Tigor . Предыдущая версия .
Re: Вопрос про транзации и deadlocks
От: Vaako Украина  
Дата: 08.07.15 14:22
Оценка: -1
Здравствуйте, Divineshadow, Вы писали:

D>Здравствуйте.


"SELECT * FROM Table1 with(NOLOCK) WHERE value1 = '{0}'"
Re: Вопрос про транзации и deadlocks
От: wildwind Россия  
Дата: 08.07.15 14:48
Оценка:
Здравствуйте, Divineshadow, Вы писали:

D> Если запустить функцию в нескольких потоках, которая добавляет запись и возвращает новый id, то довольно часто генерируется исключение:

D> "Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction"
D> А если попытаться в management studio сделать select всей таблицы, то это исключение генерируется гарантировано

В разных потоках попадаются одни и те же данные?
Вторичные индексы есть на таблице? Какие?
avalon/1.0.442
Re[2]: Вопрос про транзации и deadlocks
От: wildwind Россия  
Дата: 08.07.15 14:52
Оценка: +1
Здравствуйте, Tigor, Вы писали:

T> Что Вы и наблюдаете.


Нет. ТС наблюдает дедлок, и "откат всех конфликтующих транзакций кроме одной" происходит при любом уровне изоляции.
avalon/1.0.442
Re[3]: Вопрос про транзации и deadlocks
От: Tigor Россия  
Дата: 08.07.15 15:05
Оценка: +1
Здравствуйте, wildwind, Вы писали:

W>Нет. ТС наблюдает дедлок, и "откат всех конфликтующих транзакций кроме одной" происходит при любом уровне изоляции.


Да, сорри за неполное объяснение. Хотел немного упростить картину мира

Можно добавить, что уровень изоляции очень сильно связан с дедлоком.
Vaako так вообще предложил nolock для первого запроса Что, вроде как, противоречит целяим автора.
К сожалению, в действительности все выглядит иначе, чем на самом деле.
Re[2]: Вопрос про транзации и deadlocks
От: wildwind Россия  
Дата: 08.07.15 15:17
Оценка: +2 :)
Здравствуйте, Vaako, Вы писали:

V> "SELECT * FROM Table1 with(NOLOCK) WHERE value1 = '{0}'"


Да чего мелочиться-то, весь SELECT и последующую проверку выкинуть из кода. Результат будет эквивалентный. Нет блокировок — нет проблем!
avalon/1.0.442
Re[2]: Вопрос про транзации и deadlocks
От: Divineshadow  
Дата: 08.07.15 15:20
Оценка:
Здравствуйте, wildwind, Вы писали:

W>В разных потоках попадаются одни и те же данные?

Конечно — в этом-то вся и суть.

W>Вторичные индексы есть на таблице? Какие?

Нет.
Re[2]: Вопрос про транзации и deadlocks
От: Divineshadow  
Дата: 08.07.15 15:30
Оценка:
Здравствуйте, vsb, Вы писали:

vsb>Если для вас такое подходит — самостоятельно делайте лок на таблицу, все базы такое умеют.

В принципе лок на таблицу мне подходит. Только не совсем понятно как его использовать.
Как мне сделать select, а затем insert "в рамках одного лока"? (не знаю как правильно выразиться)
Насколько я понял TABLOCK можно применять в рамках одного запроса:
 SELECT * FROM Table1 WITH (TABLOCK)

Как это применить в моей задаче?
Re[3]: Вопрос про транзации и deadlocks
От: vsb Казахстан  
Дата: 08.07.15 15:50
Оценка:
Здравствуйте, Divineshadow, Вы писали:

vsb>>Если для вас такое подходит — самостоятельно делайте лок на таблицу, все базы такое умеют.

D>В принципе лок на таблицу мне подходит. Только не совсем понятно как его использовать.

Судя по всему у вас MSSQL. Значит что-то вроде

 SELECT * FROM Table1 WITH (TABLOCKX)


Это захватит Table1 эксклюзивно для этой транзакции до конца транзакции. Другие транзакции на этом запросе остановятся и будут ждать освобождения блокировки (то бишь окончания захватившей блокировку транзакции).
Re[3]: Вопрос про транзации и deadlocks
От: wildwind Россия  
Дата: 08.07.15 17:52
Оценка: 6 (1)
Здравствуйте, Divineshadow, Вы писали:

D> W>В разных потоках попадаются одни и те же данные?

D> Конечно — в этом-то вся и суть.

D> W>Вторичные индексы есть на таблице? Какие?

D> Нет.

После этой информации попробую объяснить происходящее. (Я не большой специалист по SQL Server, и если в чем-то ошибусь, пусть меня кто-нибудь поправит.)

Вначале немного теории. На уровне изоляции serializable накладываются не обычные блокировки строк, а т.н. key-range блокировки. Они блокируют не конкретную запись, а диапазон значений поля, по которому идет отбор в предложении WHERE. То есть все строки, у которых значение поля попадает в этото диапазон. Например, у тебя в запросе есть WHERE value1 = '{0}'. Если в параметре передать 'ABC', то будет заблокирован диапазон от 'ABC' до 'ABC', даже если таких записей в таблице нет.
И еще один момент: блокировки на уровне serializable удерживаются до окончания транзакции.

Теперь пройдемся по коду.

1. Выполняется запрос "SELECT * FROM Table1 WHERE value1 = '{0}'". Поскольку индекса по value1 нет, происходит перебор всех записей, то есть сканирование кластерного индекса. Накладывается разделяемая (S) блокировка по значению поля value1 .
2. выполняется проверка результата. Если запись (или несколько) найдена, транзакция коммитится и блокировки снимаются.
3. Если запись не найдена, то выполнятеся INSERT. При выполнении INSERT накладывается исключительная (X) блокировка на значение кластерного индекса (первичного ключа), вновь сгенерированное, а также X блокировка на значение поля value1 . Точнее, уже наложенная ранее S блокировка конвертируется в X.
4. commit.

Теперь как возникает дедлок. Две транзакции выполняющие этот код с одинаковыми значениями dbObject.Value1, одновременно или почти одновременно доходят до шага 3. То есть строк с данным значением value1 в таблице еще нет. На шаге 3 каждая из них, чтобы наложить X блокировку на значение value1, будет ждать снятия S блокировки, наложенной другой транзакцией.
avalon/1.0.442
Re[4]: Вопрос про транзации и deadlocks
От: Tigor Россия  
Дата: 09.07.15 10:47
Оценка:
W>Здравствуйте, Divineshadow, Вы писали:

W>Вначале немного теории. На уровне изоляции serializable накладываются не обычные блокировки строк, а т.н. key-range блокировки. Они блокируют не конкретную запись, а диапазон значений поля, по которому идет отбор в предложении WHERE. То есть все строки, у которых значение поля попадает в этото диапазон. Например, у тебя в запросе есть WHERE value1 = '{0}'. Если в параметре передать 'ABC', то будет заблокирован диапазон от 'ABC' до 'ABC'


Чтобы это происходило, нужен индекс по value1.


W> Теперь как возникает дедлок. Две транзакции выполняющие этот код с одинаковыми значениями dbObject.Value1, одновременно или почти одновременно доходят до шага 3. То есть строк с данным значением value1 в таблице еще нет. На шаге 3 каждая из них, чтобы наложить X блокировку на значение value1, будет ждать снятия S блокировки, наложенной другой транзакцией.


1. Без индекса одинаковость значений dbObject.Value1 не играет никакой роли. Дедлок будет даже если они разные.
2. Даже с индексом, одновременное добавление разных value в "конец" индекса будет приводить к дедлоку.
То есть на пустой таблице, эксперемент будет некорректный.
3. Еще существует понятие эскалации блокировок. Но не уверен, что с этим надо бороться превентивно.
Хотя можно тупо для конкретной таблицы запретить.

Вспоминал\проверял с помощью следующего кода
create table X 
(
    x_id int identity(1,1) primary key clustered, 
    x_name nvarchar(255)
)

create unique index X_IX on X (x_name)
-- drop index X_IX on X


set transaction isolation level serializable

begin tran

select * from X  where x_name = '8'

insert into x values ('8')

rollback tran


sp_lock 64 -- ид первого процесса
go

sp_lock 78 -- ид второго процесса
go
К сожалению, в действительности все выглядит иначе, чем на самом деле.
Re[5]: Вопрос про транзации и deadlocks
От: wildwind Россия  
Дата: 09.07.15 11:38
Оценка:
Здравствуйте, Tigor, Вы писали:

T> Чтобы это происходило, нужен индекс по value1.


Зачем?

T> 1. Без индекса одинаковость значений dbObject.Value1 не играет никакой роли. Дедлок будет даже если они разные.


Тогда бы дедлок возникал постоянно, а не иногда.
avalon/1.0.442
Re[6]: Вопрос про транзации и deadlocks
От: Tigor Россия  
Дата: 09.07.15 11:44
Оценка:
Здравствуйте, wildwind, Вы писали:

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


T>> Чтобы это происходило, нужен индекс по value1.


W>Зачем?

А сиквелу приходится всю таблицу фактически залочить. Не на что наложить красивую блокировку.


T>> 1. Без индекса одинаковость значений dbObject.Value1 не играет никакой роли. Дедлок будет даже если они разные.


W>Тогда бы дедлок возникал постоянно, а не иногда.

А он вроде всегда и возникает, если оба селекта отработали до инсертов. Просто попробуй.
К сожалению, в действительности все выглядит иначе, чем на самом деле.
Re[7]: Вопрос про транзации и deadlocks
От: wildwind Россия  
Дата: 09.07.15 13:30
Оценка:
Здравствуйте, Tigor, Вы писали:

T> А сиквелу приходится всю таблицу фактически залочить. Не на что наложить красивую блокировку.


Как не на что, на key-range же. Или я что-то неверно понимаю, тогда ссылку можешь дать, где это разъясняется?

T> T>> 1. Без индекса одинаковость значений dbObject.Value1 не играет никакой роли. Дедлок будет даже если они разные.


T> W>Тогда бы дедлок возникал постоянно, а не иногда.


T> А он вроде всегда и возникает, если оба селекта отработали до инсертов. Просто попробуй.


Попробовал бы, но сейчас не на чем.
avalon/1.0.442
Re[8]: Вопрос про транзации и deadlocks
От: Olaf Россия  
Дата: 10.07.15 08:16
Оценка: 12 (1) +1
Здравствуйте, wildwind, Вы писали:

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


T>> А сиквелу приходится всю таблицу фактически залочить. Не на что наложить красивую блокировку.


W>Как не на что, на key-range же. Или я что-то неверно понимаю, тогда ссылку можешь дать, где это разъясняется?


В этом и заключается подоплёка. Работая на нижнем уровне гранулярности, выполняя вставку/обновление записей в отсутствии индекса для реализации уровня изоляции serializable, СУБД пытается преобразовать S блокировки в SIX/X и наложить их на всю таблицу. Потому что по-другому нельзя исключить фантомное чтение, которое призван решить данный уровень изоляции. При наличии индекса как раз срабатывал бы key-range lock, не эскалируя блокировку на всю таблицу. Более того, если значения не пересекаются по интервалам, то и вставка в параллельном запросе так же работала без проблем.

Key-Range Locking

A key-range lock is placed on an index, specifying a beginning and ending key value. This lock blocks any attempt to insert, update, or delete any row with a key value that falls in the range because those operations would first have to acquire a lock on the index. For example, a serializable transaction could issue a SELECT statement that reads all rows whose key values are between 'AAA' and 'CZZ'. A key-range lock on the key values in the range from 'AAA' to 'CZZ' prevents other transactions from inserting rows with key values anywhere in that range, such as 'ADG', 'BBD', or 'CAL'.

Re[9]: Вопрос про транзации и deadlocks
От: wildwind Россия  
Дата: 10.07.15 08:48
Оценка:
Здравствуйте, Olaf, Вы писали:

O> Key-Range Locking


Спасибо за ссылку. Я эту статью читал, но этот момент, видимо, упустил.

Для меня все же остается непонятным, почему нельзя использовать key-range lock в отсутствие индекса. key-range это ведь виртуальный объект, ни к какому физическому (вроде таблицы или записи) не привязан.
avalon/1.0.442
Re: Вопрос про транзации и deadlocks
От: MasterZiv СССР  
Дата: 10.07.15 09:56
Оценка:
Здравствуйте, Divineshadow, Вы писали:

D>Есть таблица с двумя полями: id — автоинкремент, и поле value1 — ну например строковое значение.

D>Если запустить функцию в нескольких потоках, которая добавляет запись и возвращает новый id, то довольно часто генерируется исключение:
D>"Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction"
D>А если попытаться в management studio сделать select всей таблицы, то это исключение генерируется гарантировано

D>Используемый IsolationLevel = Serializable — и понятно, что когда таблица занята транзакцией из одного потока, то другой не может к ней обратиться.

D>Вернее он ждёт её освобождения. По крайнее мере я так понимаю принцип работы транзакций с уровнем Serializable.

Зря используете такой высокий уровень. Он ненужен.

D>Объясните пожалуйста, почему возникает исключение и что я не так не так понимаю?


Ну, вопрос философский, дедлоки вообще возможны в многопользовательских системах, и в СУБД в частности.
От них полностью не избавиться.
Конкретно тут -- видимо, изза вашего IsolationLevel = Serializable.
Re[4]: Вопрос про транзации и deadlocks
От: MasterZiv СССР  
Дата: 10.07.15 10:02
Оценка:
Здравствуйте, Tigor, Вы писали:

T>Да, сорри за неполное объяснение. Хотел немного упростить картину мира


T>Можно добавить, что уровень изоляции очень сильно связан с дедлоком.


Уровень изоляции никак не связан с дедлоком.
Наоборот, может быть. Да и то тут надо гадать, как у них там настроено все,
и кстати MVCC может быть включено, а может быть и нет.

Короче, первое, что надо сделать -- выключить Serializable, и повторить все снова.

Приложение, которое работает, обязано быть готовым к дедлокам, и её реакция
должна быть -- повторить транзакцию. Его следует немного переписать для этого.
Подождите ...
Wait...
Пока на собственное сообщение не было ответов, его можно удалить.