Здравствуйте, wildwind, Вы писали:
W>Здравствуйте, Tigor, Вы писали:
T>> А сиквелу приходится всю таблицу фактически залочить. Не на что наложить красивую блокировку.
W>Как не на что, на key-range же. Или я что-то неверно понимаю, тогда ссылку можешь дать, где это разъясняется?
В этом и заключается подоплёка. Работая на нижнем уровне гранулярности, выполняя вставку/обновление записей в отсутствии индекса для реализации уровня изоляции serializable, СУБД пытается преобразовать S блокировки в SIX/X и наложить их на всю таблицу. Потому что по-другому нельзя исключить фантомное чтение, которое призван решить данный уровень изоляции. При наличии индекса как раз срабатывал бы key-range lock, не эскалируя блокировку на всю таблицу. Более того, если значения не пересекаются по интервалам, то и вставка в параллельном запросе так же работала без проблем.
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'.
Здравствуйте, wildwind, Вы писали:
W>Рассмотрим например ситуацию, когда индекс имеется, и мы на уровне serializable делаем select where column between 'X' and 'Y'. Даже если в данном интервале не окажется строк, key-range блокировка будет все равно наложена, на пустой диапазон, так? Или в этом случае на всю таблицу?
Если есть индекс, а диапазон пустой, то блокировка наложится на ближайшие значения индекса (причем, влючая нижний индекс и не включая верхний, если я правильно помню). Если же индекса нет, то на всю таблицу.
Слово Key в Key-Range обозначает именно ключ индекса )
Если бы можно было накладывать блокировку на пустое место, то небыло бы смысла в такой затее как Key-Range — заблокировал пустое значение и все...
Не смотря на то, что блокировки хранятся отдельно — все равно нужна какая-то привязка к реальным объектам по которым идет выборка, отсюда и возникает необходимость привязываться к ключам индекса.
Здравствуйте, wildwind, Вы писали: W>Это таблица? Почему тогда ее имя не отображается?
Да, это таблица. Мой запрос просто не рассчитан на это, отображать имя таблицы. Главное определить какой индекс используется и какие блокировки на него накладываются. Ниже исправленная версия:
Запрос
select l.request_session_id as Spid,
db_name(l.resource_database_id) as DbName,
case
when l.resource_type = 'OBJECT' then object_name(l.resource_associated_entity_id)
when l.resource_associated_entity_id = 0 then ''
else object_name(p.object_id) end as ObjectName,
i.name as IndexName,
l.request_mode as LockRequestMode,
l.resource_type as LockResourceType,
l.resource_description as LockResourceDescription
from sys.dm_tran_locks as l
left join sys.partitions p on p.hobt_id = l.resource_associated_entity_id
left join sys.indexes as i on i.[object_id] = p.[object_id] and i.index_id = p.index_id
where l.resource_type != 'database'
Здравствуйте, 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 блокировки, наложенной другой транзакцией.
Здравствуйте, Divineshadow, Вы писали:
D>Используемый IsolationLevel = Serializable — и понятно, что когда таблица занята транзакцией из одного потока, то другой не может к ней обратиться. D>Вернее он ждёт её освобождения. По крайнее мере я так понимаю принцип работы транзакций с уровнем Serializable.
Это очень медленный подход к изоляции транзакций — разрешать работать с таблицей только одному клиенту. Если для вас такое подходит — самостоятельно делайте лок на таблицу, все базы такое умеют.
Базы лочат не таблицу, а строки. Могут и таблицу залочить, если посчитают нужным, но стараются лочить записи, чтобы транзакции, работающие с разными строками могли работать параллельно.
D>Объясните пожалуйста, почему возникает исключение и что я не так не так понимаю?
Потому что две транзакции пошли работать с одинаковыми строками (или БД так думает, что с одинаковыми). Та, которая отработала первой, закоммитила данные. Та, которая отработала второй, на момент коммита понимает, что всё, с чем она работала внутри транзакции, на самом деле неправда. Например первый селект должен был бы вернуть строку, которая была вставлена предыдущей транзакцией. Поэтому изоляцию транзакций БД обеспечить не смогла. Посколько не смогла — выкинула ошибку и не даёт закоммитить.
Нужно просто перезапускать транзакцию в цикле и всё. Не выполнилась в первый раз, выполнится во второй. Ну или не использовать SERIALIZABLE уровень, другие уровни более снисходительны к таким нарушениям изоляции.
Здравствуйте, Divineshadow, Вы писали:
D>Используемый IsolationLevel = Serializable — и понятно, что когда таблица занята транзакцией из одного потока, то другой не может к ней обратиться. D>Вернее он ждёт её освобождения. По крайнее мере я так понимаю принцип работы транзакций с уровнем Serializable.
Это не совсем так. А точнее, совсем не так.
MS SQL сервер реализует так называемую оптимистик конкарренси. Об этом лучше почитать, но если совсем на пальцах, то Serializable (то есть иллюзия последовательного выполнения транзакций) гарантируется не их реальным последовательным выполнением, а откатом всех конфликтующих транзакций кроме одной, когда что-то пошло не так.
Что Вы и наблюдаете.
Чтобы залочить таблицу целиком, придется использовать хинт (как один из вариантов).
Вам оно точно нужно? Нельзя использовать идентити колонку?
Сорри, исправляюсь, identity колонка и так есть и вторая колонка строковая.
Можете добавить уникальный индекс на вторую колонку?
Это должно несколько помочь.
Еще можно совместить проверку на существование и добавление новой строки в один оператор.
Это может помочь еще чуть-чуть засчет увеличения скорости выполнения.
Уникальному индексу вообще очень хорошая штука в данном случае,
можете снизить уровень изоляции транзакции и просто игнорировать исключения о попытке нарушить уникальность индекса.
Еще есть оператор merge.
К сожалению, в действительности все выглядит иначе, чем на самом деле.
Здравствуйте, wildwind, Вы писали:
W>Нет. ТС наблюдает дедлок, и "откат всех конфликтующих транзакций кроме одной" происходит при любом уровне изоляции.
Да, сорри за неполное объяснение. Хотел немного упростить картину мира
Можно добавить, что уровень изоляции очень сильно связан с дедлоком.
Vaako так вообще предложил nolock для первого запроса Что, вроде как, противоречит целяим автора.
К сожалению, в действительности все выглядит иначе, чем на самом деле.
Здравствуйте.
Есть таблица с двумя полями: 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, то коммита не будет
}
}
Здравствуйте, 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 всей таблицы, то это исключение генерируется гарантировано
В разных потоках попадаются одни и те же данные?
Вторичные индексы есть на таблице? Какие?
Здравствуйте, wildwind, Вы писали:
W>В разных потоках попадаются одни и те же данные?
Конечно — в этом-то вся и суть.
W>Вторичные индексы есть на таблице? Какие?
Нет.
Здравствуйте, vsb, Вы писали:
vsb>Если для вас такое подходит — самостоятельно делайте лок на таблицу, все базы такое умеют.
В принципе лок на таблицу мне подходит. Только не совсем понятно как его использовать.
Как мне сделать select, а затем insert "в рамках одного лока"? (не знаю как правильно выразиться)
Насколько я понял TABLOCK можно применять в рамках одного запроса:
Здравствуйте, Divineshadow, Вы писали:
vsb>>Если для вас такое подходит — самостоятельно делайте лок на таблицу, все базы такое умеют. D>В принципе лок на таблицу мне подходит. Только не совсем понятно как его использовать.
Судя по всему у вас MSSQL. Значит что-то вроде
SELECT * FROM Table1 WITH (TABLOCKX)
Это захватит Table1 эксклюзивно для этой транзакции до конца транзакции. Другие транзакции на этом запросе остановятся и будут ждать освобождения блокировки (то бишь окончания захватившей блокировку транзакции).
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
К сожалению, в действительности все выглядит иначе, чем на самом деле.
Здравствуйте, wildwind, Вы писали:
W>Здравствуйте, Tigor, Вы писали:
T>> Чтобы это происходило, нужен индекс по value1.
W>Зачем?
А сиквелу приходится всю таблицу фактически залочить. Не на что наложить красивую блокировку.
T>> 1. Без индекса одинаковость значений dbObject.Value1 не играет никакой роли. Дедлок будет даже если они разные.
W>Тогда бы дедлок возникал постоянно, а не иногда.
А он вроде всегда и возникает, если оба селекта отработали до инсертов. Просто попробуй.
К сожалению, в действительности все выглядит иначе, чем на самом деле.
Здравствуйте, Tigor, Вы писали:
T> А сиквелу приходится всю таблицу фактически залочить. Не на что наложить красивую блокировку.
Как не на что, на key-range же. Или я что-то неверно понимаю, тогда ссылку можешь дать, где это разъясняется?
T> T>> 1. Без индекса одинаковость значений dbObject.Value1 не играет никакой роли. Дедлок будет даже если они разные.
T> W>Тогда бы дедлок возникал постоянно, а не иногда.
T> А он вроде всегда и возникает, если оба селекта отработали до инсертов. Просто попробуй.
Спасибо за ссылку. Я эту статью читал, но этот момент, видимо, упустил.
Для меня все же остается непонятным, почему нельзя использовать key-range lock в отсутствие индекса. key-range это ведь виртуальный объект, ни к какому физическому (вроде таблицы или записи) не привязан.
Здравствуйте, 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.
Здравствуйте, Tigor, Вы писали:
T>Да, сорри за неполное объяснение. Хотел немного упростить картину мира
T>Можно добавить, что уровень изоляции очень сильно связан с дедлоком.
Уровень изоляции никак не связан с дедлоком.
Наоборот, может быть. Да и то тут надо гадать, как у них там настроено все,
и кстати MVCC может быть включено, а может быть и нет.
Короче, первое, что надо сделать -- выключить Serializable, и повторить все снова.
Приложение, которое работает, обязано быть готовым к дедлокам, и её реакция
должна быть -- повторить транзакцию. Его следует немного переписать для этого.
Здравствуйте, wildwind, Вы писали:
W>Для меня все же остается непонятным, почему нельзя использовать key-range lock в отсутствие индекса. key-range это ведь виртуальный объект, ни к какому физическому (вроде таблицы или записи) не привязан.
Key-range один из режимов блокирования, но использует он физический индекс. А каким образом можно иначе реализовать механизм исключающий фантомное чтение для уровня serializable, чтобы в определенном интервале заблокировать вставку записей с минимальными затратами ресурсов и доступным уровнем конкуренции? Например, страница, но она по сути своей представляет разнородный неупорядоченный набор данных, где невозможно определить ни начало, ни конец, а чтобы это сделать нужно обойти все страницы, что в конечном итоге сводится к блокировке целой таблицы. Про записи я вообще молчу, это более мелкая единица, а затраты на реализацию блокировок в интервальном значении еще больше. Другое дело индекс, который представляет собой сбалансированное дерево с упорядоченной структурой, где легко можно найти начало-конец значения и использовать их для предотвращения вставки новых значений, тем самым, исключив аномалию фантомное чтение. Но это только мое мнение.
Здравствуйте, Olaf, Вы писали:
O> Key-range один из режимов блокирования, но использует он физический индекс.
Я понимаю, для чего используется Key-range. Мне интересно, для чего он логически привязан именно к индексу, а не просто к полю. Конечно, это уже интминые детали реализации, но все же. Насколько мне известно, блокировки в SQL Server физически не привязаны к физическим структурам данных (таблицам и индексам), менеджер блокировок хранит их в своих структурах в памяти. В отличие, скажем, от Oracle, где блокировки именно физически привязаны к данным, информация о них хранится вместе с данными, на страницах таблиц и индексов. Казалось бы, зачем менеджеру блокировок необходимо существование индекса?
Рассмотрим например ситуацию, когда индекс имеется, и мы на уровне serializable делаем select where column between 'X' and 'Y'. Даже если в данном интервале не окажется строк, key-range блокировка будет все равно наложена, на пустой диапазон, так? Или в этом случае на всю таблицу?
Здравствуйте, wildwind, Вы писали: w> Рассмотрим например ситуацию, когда индекс имеется, и мы на уровне serializable делаем select where column between 'X' and 'Y'. Даже если в данном интервале не окажется строк, key-range блокировка будет все равно наложена, на пустой диапазон, так?
Нашел старый SQL 205 Express? новее у меня сейчас ничего нет. В описанной ситуации наблюдаю key-range блокировки что с индексом, что без оного.
Подготовка
Скрытый текст
drop table T1
create table T1 (
Id Int Identity primary key,
Value1 varchar(50),
Value2 varchar(50)
)
create index IX_T1_Value1 on T1 (value1)
insert into T1 (Value1, Value2) values ('AAA', 'AAA')
insert into T1 (Value1, Value2) values ('BBB', 'BBB')
insert into T1 (Value1, Value2) values ('EEE', 'EEE')
Эксперимент
Скрытый текст
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT * FROM T1
WHERE Value2 between 'XXX' and 'YYY'
SELECT resource_type, resource_description, request_mode
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID
AND resource_type <> 'DATABASE'
Здравствуйте, Tigor, Вы писали:
T> Так это, наверно, рендж блокировки на весь кластернный индекс и получились.
Похоже на то.
Значения resource_description, кстати, соответствуют таковым при выборке по PK. И вставка строки в другой транзакции блокируется, причем независимо от того, попадает ли Value2 в данный диапазон.
Здравствуйте, wildwind, Вы писали:
W>Здравствуйте, wildwind, Вы писали:
w>> Рассмотрим например ситуацию, когда индекс имеется, и мы на уровне serializable делаем select where column between 'X' and 'Y'. Даже если в данном интервале не окажется строк, key-range блокировка будет все равно наложена, на пустой диапазон, так?
W>Нашел старый SQL 205 Express? новее у меня сейчас ничего нет. В описанной ситуации наблюдаю key-range блокировки что с индексом, что без оного.
W>...
1. Есть описание алгоритма для уникального/неуникального индекса с предикатом сравнения и диапазона Range locks
Ваш случай следующий:
Range Predicate (key between the two values)
◦‘range lock on all the key values in the range when using ‘between’
◦‘range’ lock on the ‘next’ key that is outside the range. This is true both for unique and non-unique indexes. This is to ensure that no row can be inserted between the requested key and the one after that. If the ‘next’ key does not exist, then a range lock is taken on the ‘infinity’ value.
Т.е. диапазонная блокировка накладывается до так называемого бесконечного значения, фактически до конца диапазона. Судя потому, что вы показали это и есть значение ffffffffffff.
2. Давайте для подтверждения наличия key-range блокировок (с индексом и без) запускать следующий запрос
select object_name(i.[object_id]) as ObjectName,
i.name AS IndexName,
l.request_mode AS LockRequestMode,
l.resource_type AS LockResourceType,
l.resource_description AS LockResourceDescription
from sys.dm_tran_locks as l
left join sys.partitions p on p.hobt_id = l.resource_associated_entity_id
left join sys.indexes as i on i.[object_id] = p.[object_id] and i.index_id = p.index_id
where l.resource_type != 'database'
вместо вашего select * from sys.dm_tran_locks.
3. Индекс на самом деле вы создали, причем кластерный по полю Id, а потом еще один. Уберите его и проведите эксперимент заново.
Здравствуйте, Divineshadow, Вы писали:
D>Объясните пожалуйста, почему возникает исключение и что я не так не так понимаю?
Ну, почему здесь вылезает дедлок — уже объяснили, а сама задача правильно решается так:
1. Использовать дефолтный уровень изоляции (Read Committed)
2. Построить уникальный индекс по value1
3.1 Надо быть готовым, что очень-очень редко, при высоких нагрузках и высокой вероятности нескольким транзакциям одновременно добавить одно и то же значение value1 будет вылезать исключение при попытке вставить двойное значение в уникальный индекс. Скорее всего за время жизни приложения это ни разу и не случится.
Либо
3.2 При выборке id по value1 использовать хинт UPDLOCK, что уберет вероятность вставки одного и того же значения value1 двумя разными транзакциями
Мы уже победили, просто это еще не так заметно...
