Ответ 1
I может также воспроизвести это 100% времени на моей машине. (см. примечание в конце)
Суть проблемы заключается в том, что вы удаляете блокировки S в строках системной таблицы в tempdb, которые могут конфликтовать с блокировками, необходимыми для внутренних транзакций очистки tempdb.
Когда эта работа по очистке распределяется на тот же сеанс, которому принадлежит блокировка S, может возникнуть неопределенное зависание.
Чтобы избежать этой проблемы, вам нужно прекратить ссылаться на объекты system внутри tempdb.
Можно создать таблицу чисел без ссылки на любые внешние таблицы. Следующее должно читать строки базовых таблиц и, следовательно, также не принимать блокировок.
WITH Ten(N) AS
(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)
SELECT TOP 1000000 IDENTITY(INT, 1, 1) Number
INTO Numbers
FROM Ten T10,
Ten T100,
Ten T1000,
Ten T10000,
Ten T100000,
Ten T1000000
Шаги по воспроизведению
Сначала создайте процедуру
CREATE PROC P
AS
SET NOCOUNT ON;
DECLARE @T TABLE (X INT)
GO
Затем перезапустите службу SQL и в одном соединении выполните
WHILE NOT EXISTS(SELECT *
FROM sys.dm_os_waiting_tasks
WHERE session_id = blocking_session_id)
BEGIN
/*This will cause the problematic droptemp transactions*/
EXEC sp_recompile 'P'
EXEC P
END;
SELECT *
FROM sys.dm_os_waiting_tasks
WHERE session_id = blocking_session_id
Затем в другом соединении запустите
USE tempdb;
SELECT TOP 1000000 IDENTITY(INT, 1, 1) Number
INTO #T
FROM sys.objects s1
CROSS JOIN sys.objects s2
CROSS JOIN sys.objects s3
CROSS JOIN sys.objects s4;
DROP TABLE #T
Кажется, что запрос, заполняющий таблицу Numbers, попадает в ситуацию блокировки в реальном времени с внутренними системными транзакциями, которые очищают временные объекты, такие как переменные таблицы.
Мне удалось заблокировать идентификатор сеанса 53 таким образом. Он заблокирован на неопределенный срок. Выходной сигнал sp_WhoIsActive показывает, что этот spid тратит почти все время приостановления. В последовательных прогонах числа в столбце reads возрастают, но значения в других столбцах остаются в основном одинаковыми.
Продолжительность ожидания не показывает возрастающий шаблон, хотя указывает, что он должен быть разблокирован периодически, прежде чем снова заблокировать его.
SELECT *
FROM sys.dm_os_waiting_tasks
WHERE session_id = blocking_session_id
Возвращает
+----------------------+------------+-----------------+------------------+-----------+--------------------+-----------------------+---------------------+--------------------------+--------------------------------------------------------------------------------------------------+
| waiting_task_address | session_id | exec_context_id | wait_duration_ms | wait_type | resource_address | blocking_task_address | blocking_session_id | blocking_exec_context_id | resource_description |
+----------------------+------------+-----------------+------------------+-----------+--------------------+-----------------------+---------------------+--------------------------+--------------------------------------------------------------------------------------------------+
| 0x00000002F2C170C8 | 53 | 0 | 86 | LCK_M_X | 0x00000002F9B13040 | 0x00000002F2C170C8 | 53 | NULL | keylock hobtid=281474978938880 dbid=2 id=lock2f9ac8880 mode=U associatedObjectId=281474978938880 |
+----------------------+------------+-----------------+------------------+-----------+--------------------+-----------------------+---------------------+--------------------------+--------------------------------------------------------------------------------------------------+
Использование идентификатора в описании ресурса
SELECT o.name
FROM sys.allocation_units au WITH (NOLOCK)
INNER JOIN sys.partitions p WITH (NOLOCK)
ON au.container_id = p.partition_id
INNER JOIN sys.all_objects o WITH (NOLOCK)
ON o.object_id = p.object_id
WHERE allocation_unit_id = 281474978938880
Возвращает
+------------+
| name |
+------------+
| sysschobjs |
+------------+
Запуск
SELECT resource_description,request_status
FROM sys.dm_tran_locks
WHERE request_session_id = 53 AND request_status <> 'GRANT'
Возвращает
+----------------------+----------------+
| resource_description | request_status |
+----------------------+----------------+
| (246708db8c1f) | CONVERT |
+----------------------+----------------+
Подключение через ЦАП и запуск
SELECT id,name
FROM tempdb.sys.sysschobjs WITH (NOLOCK)
WHERE %%LOCKRES%% = '(246708db8c1f)'
Возвращает
+-------------+-----------+
| id | name |
+-------------+-----------+
| -1578606288 | #A1E86130 |
+-------------+-----------+
Интересно, что это за
SELECT name,user_type_id
FROM tempdb.sys.columns
WHERE object_id = -1578606288
Возвращает
+------+--------------+
| name | user_type_id |
+------+--------------+
| X | 56 |
+------+--------------+
Это имя столбца в переменной таблицы, используемой хранимой процедурой.
Запуск
SELECT request_mode,
request_status,
request_session_id,
request_owner_id,
lock_owner_address,
t.transaction_id,
t.name,
t.transaction_begin_time
FROM sys.dm_tran_locks l
JOIN sys.dm_tran_active_transactions t
ON l.request_owner_id = t.transaction_id
WHERE resource_description = '(246708db8c1f)'
Возвращает
+--------------+----------------+--------------------+------------------+--------------------+----------------+-------------+-------------------------+
| request_mode | request_status | request_session_id | request_owner_id | lock_owner_address | transaction_id | name | transaction_begin_time |
+--------------+----------------+--------------------+------------------+--------------------+----------------+-------------+-------------------------+
| U | GRANT | 53 | 227647 | 0x00000002F1EF6800 | 227647 | droptemp | 2013-11-24 18:36:28.267 |
| S | GRANT | 53 | 191790 | 0x00000002F9B16380 | 191790 | SELECT INTO | 2013-11-24 18:21:30.083 |
| X | CONVERT | 53 | 227647 | 0x00000002F9B12FC0 | 227647 | droptemp | 2013-11-24 18:36:28.267 |
+--------------+----------------+--------------------+------------------+--------------------+----------------+-------------+-------------------------+
Таким образом, транзакция SELECT INTO содержит блокировку S в строке в tempdb.sys.sysschobjs, относящуюся к переменной таблицы #A1E86130. Операция droptemp не может получить блокировку X в этой строке из-за этой конфликтующей блокировки S.
Выполнение этого запроса неоднократно показывает, что транзакция transaction_id для транзакции droptemp неоднократно изменяется.
Я предполагаю, что SQL Server должен распределять эти внутренние транзакции на пользовательских spids и приоритизировать их перед тем, как пользователь будет работать. Таким образом, идентификатор сеанса 53 застрял в постоянном цикле, где он запускает транзакцию droptemp, блокируется транзакцией пользователя, выполняемой на том же spid. Возвращает внутреннюю транзакцию, а затем повторяет процесс на неопределенный срок.
Это подтверждается отслеживанием различных событий блокировки и транзакций в SQL Server Profiler после того, как spid будет виден.
Я также отслеживал события блокировки до этого.
Блокировка блокировки событий
Большинство блокировок разделяемых ключей, извлеченных транзакцией SELECT INTO на клавишах в sysschobjs, немедленно освобождаются. Исключением является первая блокировка на (246708db8c1f).
Это имеет смысл, так как на плане показаны вложенные циклы сканирования [sys].[sysschobjs].[clst] [o] и, поскольку временные объекты получают заданные отрицательные объекты, они будут первыми строками, встречающимися в порядке сканирования.
Я также столкнулся с ситуацией, описанной в OP, где сначала выполняется трехстороннее перекрестное соединение, что позволяет добиться успеха в четырех направлениях.
Первые несколько событий в трассировке для транзакции SELECT INTO имеют совершенно другой шаблон.
Это было после перезапуска службы, поэтому значения ресурса блокировки в столбце текстовых данных не сопоставимы напрямую.
Вместо того, чтобы удерживать блокировку на первом ключе, а затем образец получения и отпускания последующих клавиш, похоже, он приобретает гораздо больше блокировок, не отпуская их изначально.
Я предполагаю, что в стратегии исполнения должна быть некоторая дисперсия, которая позволяет избежать проблемы.
Обновить
Подключенный элемент, о котором я рассказал об этомне был отмечен как фиксированный, но теперь я на SQL Server 2012 с пакетом обновления 2 (SP2) и теперь могу воспроизводить только временную блокировку, а не постоянную. Я по-прежнему получаю самоблокировку, но после некоторого количества неудачных попыток выполнить транзакцию droptemp она, похоже, возвращается к обработке транзакции пользователя. После этого транзакция системы выполняется успешно. Тем не менее на том же спиде. (восемь попыток в одном примере запускаются. Я не уверен, будет ли это повторяться последовательно)