MS SQL 2K, deadlock

[trialex]

Возникает иногда несколько странный deadlock.
Итак,
SPID 58 делает
INSERT INTO table1(...) VALUES (...)
SET @ID = SCOPE_IDENTITY()
INSERT INTO table2(PARENT_ID, ...) VALUES (@ID, ...)

Все это в хранимой процедуре. Процедура выполняется с указанием явной транзакции

Code: Select all

SqlCommand cmd = new SqlCommand("dbo.ap_VehCommitWork_SE", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlTransaction tran = conn.BeginTransaction(IsolationLevel.ReadCommitted);
cmd.Transaction = tran;

Далее, SPID 54 делает
SELECT ...
FROM table1
WHERE ID IN (SELECT PARENT_ID
FROM table2
WHERE (...)
GROUP BY ...
HAVING COUNT(...) = ...)
Транзакция - неявная. По умолчанию Isolation Level тоже Read Committed. Теперь собственно deadlock info (я заменил object_id на условное имя таблицы):


Deadlock encountered .... Printing deadlock information
2003-08-12 18:57:32.40 spid3
2003-08-12 18:57:32.40 spid3 Wait-for graph
2003-08-12 18:57:32.40 spid3
2003-08-12 18:57:32.40 spid3 Node:1
2003-08-12 18:57:32.40 spid3 KEY: 8:table1:1 (4200e3437b80) CleanCnt:1 Mode: X Flags: 0x0
2003-08-12 18:57:32.40 spid3 Grant List 0::
2003-08-12 18:57:32.40 spid3 Owner:0x4725e5c0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:58 ECID:0
2003-08-12 18:57:32.40 spid3 SPID: 58 ECID: 0 Statement Type: INSERT Line #: 51
2003-08-12 18:57:32.40 spid3 Input Buf: RPC Event: dbo.ap_VehCommitWork_SE;1
2003-08-12 18:57:32.40 spid3 Requested By:
2003-08-12 18:57:32.40 spid3 ResType:LockOwner Stype:'OR' Mode: S SPID:54 ECID:0 Ec:(0x4336B568) Value:0x4725aec0 Cost:(0/0)
2003-08-12 18:57:32.40 spid3
2003-08-12 18:57:32.40 spid3 Node:2
2003-08-12 18:57:32.40 spid3 TAB: 8:table2 [] CleanCnt:1 Mode: S Flags: 0x0
2003-08-12 18:57:32.40 spid3 Grant List 0::
2003-08-12 18:57:32.40 spid3 Owner:0x46df6a60 Mode: S Flg:0x0 Ref:2 Life:00000001 SPID:54 ECID:0
2003-08-12 18:57:32.40 spid3 SPID: 54 ECID: 0 Statement Type: SELECT Line #: 1
2003-08-12 18:57:32.40 spid3 Input Buf: Language Event: SELECT ...
2003-08-12 18:57:32.40 spid3 Requested By:
2003-08-12 18:57:32.40 spid3 ResType:LockOwner Stype:'OR' Mode: IX SPID:58 ECID:0 Ec:(0x48681568) Value:0x4725ae80 Cost:(0/660)
2003-08-12 18:57:32.40 spid3 Victim Resource Owner:
2003-08-12 18:57:32.40 spid3 ResType:LockOwner Stype:'OR' Mode: S SPID:54 ECID:0 Ec:(0x4336B568) Value:0x4725aec0 Cost:(0/0)


Т.е.
1. SPID 58 вставил запись в table1, в то же время SPID 54 обрабатывает подзапрос на table2
2. SPID 58 готов вставить запись в table2, но ждет SPID 54
3. SPID 54 не может завершится, так как SPID 58 держит лок на table 1.
Имеем deadlock.

Как выход я вижу вынос подзапроса - с созданием #temp и join table1 и #temp.

Вопрос однако, почему SPID 58 INSERT получил KEY, а не RID ?
Если я правильно понял BOL:

Key-range locking solves the phantom read concurrency problem and supports serializable transactions. Key-range locks cover individual records and the ranges between records, preventing phantom insertions or deletions into a set of records accessed by a transaction. Key-range locks are used only on behalf of transactions operating at the serializable isolation level.

У меня isolation level не Serializable...

[tengiz]

Как выход я вижу вынос подзапроса - с созданием #temp и join table1 и #temp.

Да, это поможет. Что там в where в подзапросе и что за индексы есть в table2? Причина deadlock в том, что при выполнении этого подзапроса берётся табличная блокировка table2. Поэтому один из других вариантов решения - посмотреть, нельзя ли избежать табличной блокировки, создав полезный индекс. Это может значительно уменьшить шансы нарваться на deadlock.

Вопрос однако, почему SPID 58 INSERT получил KEY, а не RID ?
Если я правильно понял BOL:...У меня isolation level не Serializable...

Key-range lock и key lock - это разные вещи. RID lock определён только для таблиц без кластерных индексов, только в этом случае имеет смысл стабильный rowid. При наличии кластерного индекса, т.е. когда таблица сама является b-tree, не существует стабильного rowid, поэтому адресация строк делается через key - поэтому и key lock. А на SERIALIZABLE, соответственно, может быть key-range lock.

[trialex]

Причина deadlock в том, что при выполнении этого подзапроса берётся табличная блокировка table2. Поэтому один из других вариантов решения - посмотреть, нельзя ли избежать табличной блокировки, создав полезный индекс. Это может значительно уменьшить шансы нарваться на deadlock.

Я немного упростил подзапрос. На самом деле есть join table2 и table3, к которой процедура никак не обращается.
а join обычный

Code: Select all

table2 a join table3 b on a.t3_ID = b.id
WHERE b.Field IN ('val1', 'val2', 'val3')
group by a.Field having count(b.Field) = 3

Как оказалось, на table3.Field нет индекса. В Table3 - ~3,400 записей,
в table2 ~ 73,000 записей. DBCC SHOW_STATISTICS говорит что для индекса по table2.t3_ID
Density: 2.8690527E-4, Steps: 195

Если создать индекс по table3.Field - уберет ли это table lock на table2 ?

Key-range lock и key lock - это разные вещи

Спасибо за пояснение. Я решил что это range из BOL к описанию информации о deadlock:

KEY

Identifies the key range within an index on which a lock is held or requested.

KEY is represented in Trace Flag 1204 as KEY: db_id:object_id:index_id; for example, KEY: 2:1977058079:1.

[tengiz]

Если создать индекс по table3.Field - уберет ли это table lock на table2

Да, это может помочь, при условии, что table3.Field имеет хорошую селективность, и если оптимизатор выберет loop join вместо hash join. А hash join скорее всего и имеет сейчас место. Отсюда полный скан table2, поэтому и имеет смысл табличная блокировка. Кроме того, наличие индекса в случае хорошей селективности table3.Field точно уменьшит вероятность deadlock даже если бы осталась табличная блокировка - просто потому, что запрос будет выполняться быстрее. А чтобы ещё упростить оптимизатору задачу, можно перенести условие на table3.Field в join:

Code: Select all

table2 a join table3 b on a.t3_ID = b.id and b.Field IN ('val1', 'val2', 'val3') 
group by a.Field
having count(b.Field) = 3

-----------
В BOL, похоже, есть маленькая неточность с вольным использованием терминологии или неоднозначным прочтением. Спасибо что поймали, я перешлю запрос тем, кто отвечает за эту часть документации.

[SkyWalker]

Если создать индекс по table3.Field - уберет ли это table lock на table2

Да, это может помочь, при условии, что table3.Field имеет хорошую селективность, и если оптимизатор выберет loop join вместо hash join. А hash join скорее всего и имеет сейчас место. Отсюда полный скан table2, поэтому и имеет смысл табличная блокировка. Кроме того, наличие индекса в случае
...

tengiz, прошу прощения. Осторожно, почти шепотом...
Вы не перепутали hash и nested loop joins ?
Я в том смысле что nested loops join = table scan.

[tengiz]

Вы не перепутали hash и nested loop joins? Я в том смысле что nested loops join = table scan.

Да вроде нет, ничего не перепутал. Что касается nested loops join = table scan, то так конечно бывает для внешней таблицы в соединении, но это не наш случай, если я понимаю, что Вы имеете в виду. Не поясните?

[SkyWalker]

Вы не перепутали hash и nested loop joins? Я в том смысле что nested loops join = table scan.

Да вроде нет, ничего не перепутал. Что касается nested loops join = table scan, то так конечно бывает для внешней таблицы в соединении, но это не наш случай, если я понимаю, что Вы имеете в виду. Не поясните?

Вы предположили, что при хорошей селективности table3.field и создании соответственно индекса на этом поле, удасться избежать table scan при join операции. При этом упомянули что сейчас видимо происходит именно table scan при hash join. Вот я и пытаюсь уточнить, не перепутали ли Вы hash join с nested loops join. Потому как table scan будет происходить по-моему при nested loops join, а не при hash join. Тоесть сейчас, вероятно, происходит nested loops, а после создания индекса будет hash.

[tengiz]

SkyWalker,

В нашем случае, когда общее количество строк в table2 больше, что общее количество строк в table3, и когда по join колокне есть индекс с каждой стороны, возможны три разных с точки зрения оптимизатора варианта:

1. В table3 мало строк, удовлетворяющих условию b.Field IN ('val1', 'val2', 'val3') - их количество заметно меньше количества страниц в table2
2. В table3 много строк, удовлетворяющих условию b.Field IN ('val1', 'val2', 'val3') - их порядка количества страниц в table2 или намного больше.
3. О количестве сток в table3, удовлетворяющих условию b.Field IN ('val1', 'val2', 'val3') ничего неизвестно.

В случае (1) делается loop join, причём в качестве "внешней" таблицы выбирается подмножество table3 со строками, удовлетворяющими условию b.Field IN ('val1', 'val2', 'val3'), благо их относительно мало. Для каждой строки делается index lookup во "внутренней" таблице table2, так как по join колонке имеется индекс.

В случае (2) предыдущий план уже становится довольно дорогим, потому, что один index lookup - это два IO, поэтому полный скан table2 был бы дешевле. В итоге более выгодным может оказеться план c hash join - сначала строится hash для подмножества строк table3 со строками, удовлетворяющими условию b.Field IN ('val1', 'val2', 'val3'), а затем делается полный скан table2 и для каждой строки делается поиск в построенной hash таблице.

Случай (3), как правило, больше похож на случай (2), поэтому скорее всего в наших условиях будет выбран hash join, как наименее дорогой с точки зрения оптимизатора.

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

[SkyWalker]

tengiz, спасибо большое за разьяснения. Я просто изначально немного неверно Вас понял.