Проблема с кластерным индексом

[Sequel]

Не совсем понятно поведение таблиц для которых первичный ключ является кластерным индексом. К примеру, есть таблица InventJournalTrans, первичный ключ в ней по полю JournalId и по этому же полю – кластерный индекс. Теперь я делаю запрос из Аксапты:

Ttsbegin;
select forupdate inventJournalTable where inventJournalTable.JournalId == 'Код1';
pause;
ttscommit;

И пока первый клиент Аксапты стоит на паузе, во втором выполняю такой же код только для журнала с кодом Код2.
Второй клиент Аксапты виснет при выполнении запроса и просматривая блокировки я вижу что ожидается снятие блокировки по ключу индекса PostedJournalIdx. Этот индекс создан по полям Posted и JournalId. Чем вызвана блокировка ключа именно этого индекса? Ведь приведенный выше запрос должен вызывать только блокировку ключа по полю JournalId (блокировка по этому ключу тоже ставится). Связано ли это как-то с тем, что индекс по полю JournalId является кластерным?
Причем, что еще интересно – если я этот же запрос буду выполнять непосредственно из SQL Management Studio, переписанный в виде

SELECT * FROM INVENTJOURNALTABLE A WITH( INDEX(I_154JOURNALIDX), UPDLOCK) WHERE ((DATAAREAID='vir') AND (JOURNALID='КОД1')) OPTION(FAST 1)

То блокироваться будет только индекс по JournalId и 2 запроса по журналам с разными кодами (например Код1 и Код2) блокировать друг друга не будут.
Связана ли блокировка индекса PostedJournalIdx в аксапте с тем что аксапта использует для выполнения запросов API-курсоры, а не выполняет запросы в чистом виде?
Эта проблема есть только в 2005-м sql. Полная конфигурация системы: Axapta 3.0 SP1 KR1, SQL Server 2005 Enterprice Edition

[Lemming]

[QUOTE=Sequel;128614]

Может вместо :

ttsbegin;
select forupdate inventJournalTable where inventJournalTable.JournalId == 'Код1';
pause;
ttscommit;

попробовать :

ttsbegin;
select firstonly forupdate inventJournalTable where inventJournalTable.JournalId == 'Код1';
pause;
ttscommit;

?

[Sequel]

То же самое получается. Да и смысла нет - с кодом Код1 и Код2 по одной записи в таблице

[Alexius]

А не покажите этот запрос из SQL Server Prifiler ?

[Sequel]

Вот это место:

declare @p1 int
set @p1=1073741835
declare @p2 int
set @p2=180150023
declare @p5 int
set @p5=2
declare @p6 int
set @p6=2
declare @p7 int
set @p7=-1

exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 varchar(1000),@P2 varchar(1000)',N'SELECT 
A.JOURNALID,A.DESCRIPTION,A.POSTED,A.RESERVATION,A.SYSTEMBLOCKED,A.BLOCKUSERID,A.JOURNALTYPE,A.JOURNALNAMEID,A.INVENTDIMFIXED,A.EMPLID,A.BLOCKUSERGROUPID,A.VOUCHERDRAW,A.VOUCHERCHANGE,A.VOUCHERSEQID,A.SESSIONLOGINTIME,A.SESSIONLOGINDATE,A.SESSIONID,A.POSTEDUSERID,A.POSTEDDATE,A.NUMOFLINES,A.JOURNALIDORIGNAL,A.DETAILSUMMARY,A.DELETEPOSTEDLINES,A.LEDGERACCOUNTIDOFFSET,A.OFFSESSIONID_RU,A.ISLINESADDS,A.ISCHANGED,A.DATEBEFORE,A.DATELATER,A.DIFFERENCES,A.JOURNALSOURCE,A.ETENTION,A.MODIFIEDDATE,A.MODIFIEDTIME,A.MODIFIEDBY,A.MODIFIEDTRANSACTIONID,A.CREATEDDATE,A.CREATEDTIME,A.CREATEDBY,A.CREATEDTRANSACTIONID,A.RECVERSION,A.RECID,A.DEL_LOG 
FROM INVENTJOURNALTABLE A WITH( INDEX(I_154JOURNALIDX), UPDLOCK) WHERE ((DATAAREAID=@P1) AND (JOURNALID=@P2)) 
OPTION(FAST 1)',@p5 output,@p6 output,@p7 output,'vir','S_00051577'
select @p1, @p2, @p5, @p6, @p7

exec sp_cursorfetch 180150023,2,1,1

exec sp_cursor 180150023,40,1

exec sp_cursorclose 180150023

Собственно из текста, я думаю, понятно, что запрос вызывается не напрямую а через курсор

[Sequel]

И вот еще: эти же запросы с использованием курсоров, когда выполняешь из SQL Server Management Studio - блокировка возникает только по индексу JournalIdx - как и должна

[Vadik]

На MSSQL 2005 SP2 (декабрьский CTP) не воспроизводится
Планы исполнения в обоих случаях сравнивали?

[Alexius]

Воспроизвести такое поведение удалось только преобразованием индекса JournalIdx из кластерного в обычный, проверьте его, на всякий случай, на самом SQL.
Еще попробуйте посмотреть в Activity Monitor в момент паузы заблокированные объекты, скорее всего, будет заблокирована страница(PAGE) с Request Mode : U

[Alexius]

"Версионный режим" = Compatability Level : SQL Server 2005 (90) ?

[Sequel]

2Vadik
Планы исполнения сравнивал - и в том и в другом случае план состоит из одного действия: Cluster Index Seek

[Sequel]

Кстати - наверное еще самого главного не сказал: SQL Server работает в версионном режиме. В блокировочном режиме мне кажется ошибка и не будет воспроизводиться

[Sequel]

2 Аlexius:
Нет, индекс именно кластерный
Версионный режим - это не Compatability Level: Compatability Level - это уровень совместимости, он просто говорит будут ли использоваться фичи, добавленные в 2005-м SQL Server или работа будет в режиме совместимости с SQL Server 2000
Версионный режим - это режим обработки транзакций, режим работы с блокировками.
По умолчанию режим - блокировочный, версионный включается командами
ALTER DATABASE database_name SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE database_name
SET READ_COMMITTED_SNAPSHOT ON

[Sequel]

Собственно я тут поэкспериментировал еще и у меня получилось перевести задачу из разряда специфических для Аксапты в разряд легко воспроизводимых на любой базе данных SQL Server 2005 в версионном режиме. Сама постановка задачи и вопросы - в следующем посте

[Sequel]

SQL Server 2005 Enterprice Edition
База данных работает в версионном режиме.
Имеется простенькая табличка:

CREATE TABLE [dbo].[_Table1](
[id] [varchar](10) COLLATE Cyrillic_General_CI_AS NOT NULL,
[name] [varchar](50) COLLATE Cyrillic_General_CI_AS NOT NULL,
[flag] [int] NOT NULL,
[dataareaid] [varchar](10) COLLATE Cyrillic_General_CI_AS NOT NULL

Первичный ключ PK__Table1 в ней: по полям dataareaid и id , ключ является кластерным. Есть еще индекс IX__Table1 по полям dataareaid и flag. Использование поля dataareaid в качестве первого поля во всех индексах и ключах здесь принципиально: в Аксапте это поле является идентификатором компании, используется практически во всех таблицах, и включается во все ключи и индексы как первое поле.
Мы хотим выполнить запрос на обновление записи (записей) в этой таблицы:

SELECT * FROM _TABLE1 WITH( INDEX(PK__Table1), UPDLOCK) WHERE dataareaid='comp' and (ID='2')

Причем выполнить в виде курсора используя хранимые процедуры sp_cursorprepexec, sp_cursorfetch и т.д. В результате список операций получается такой:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

begin transaction

DECLARE @p1 int
SET @p1=-1
DECLARE @p2 int
SET @p2=0
DECLARE @p5 int
SET @p5=2
DECLARE @p6 int
SET @p6=2
DECLARE @p7 int
SET @p7=-1

exec sp_cursorprepexec @p1 output,@p2 output,
NULL,
N'SELECT * FROM _TABLE1 WITH( INDEX(PK__Table1), UPDLOCK) WHERE dataareaid=''comp'' and (ID=''2'')',
@p5 output,@p6 output,@p7 output
select @p1, @p2, @p5, @p6, @p7

exec sp_cursorfetch 180150023,2,1,1

exec sp_cursor 180150023,40,1

exec sp_cursorclose 180150023
commit

Рассмотрим по порядку то, что здесь выполняется.
Оператор exec sp_cursorprepexec парсит и выполняет запрос, здесь важно обратить внимание на 2 параметра: p5 и p6, которые представляют из себя тип курсора (значение 2 соответствует динамическому типу) и тип конкурентности (2 – scroll locks) ,т.е. записи блокируются в тот момент, когда они выбираются командой fetch и разблокируются при переходе к следующей записи.
Выполняю эти действия пошагово, и смотрю блокировки в базе данных
После exec sp_cursorprepexec никаких блокировок нет.
После sp_cursorfetch блокируется ключ индекса PK__Table1, что в общем то никаких вопросов не вызывает.
А вот после выполнения exec sp_cursor возникает блокировка индекса IX__Table1 которая вызывает массу вопросов: я выбираю на изменение запись по значению первичного ключа, это значение первичного ключа должно заблокироваться, оно блокируется как и положено…но причем тут индекс по совершенно другому полю, почему он блокируется?
Причем блокировка индекса PK__Table1 проходит безобидно: я могу из другой транзакции выбрать точно так же на обновление любую другую запись этой же таблицы. А вот блокировка IX__Table1 приводит к тому, что попытка из другой транзакции выбрать на обновление любую запись из этой же таблицы приведет к блокировке ожидания, и запрос не выполнится до тех пор, пока первая транзакция не разблокирует индекс. Такое поведение SQL Server, естественно безобидным назвать нельзя, поэтому хотелось бы разобраться из-за чего такое возникает

[Sequel]

Что еще интересно: если индекс PK__Table1 - некластерный - такой блокировки IX__Table1 нет. Если индексы не включают поле dataareaid (т.е. первый - по полю id, второй - по полю flag) - такой блокировки тоже нет.

[polygris]

Sequel
напоролись на такую же проблему как у тебя, только на InventJournalTable (честно говоря вообче не понимаю смысла кластерного индекса на этой таблице). Чем закончилось расследование? Все таки это глюк 2005? Наблюдается ли эта проблема на 2000?

[Андре]

Цитата:

Собственно я тут поэкспериментировал еще и у меня получилось перевести задачу из
разряда специфических для Аксапты в разряд легко воспроизводимых на любой базе данных SQL Server 2005 в версионном режиме.

А план запроса можите привести, если не сложно? Просто если SQL Server использует второй индекс, то он почему то решает, что его использование выгодно, с точки зрения стоимостного оптимизатора. Чтобы понять, почему он так решает - нужно для начала видеть план запроса из которого будет хотя бы понятно для чего он его использует.

[egorych]

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

[Андре]

Цитата:

Думаю, что дело в том, что при наличии кластерного индекса на таблице, все остальные индексы ссылаются на него

Только в том случае, если поля входящие во второй индекс входят и в кластерный. Еще можно предположить, что SQL Sever решил, что ему выгоднее работать с двумя индексами (кластреным и простым), не делая bookmark lookup, но, с учетом, что один из индексов кластерный данной предположение выглядит дико.

Поэтому, чтобы не предполагать, я и попросил план запроса

[egorych]

Цитата:

Сообщение от Андре

Только в том случае, если поля входящие во второй индекс входят и в кластерный.

Нет, в случае, если на таблице есть кл.инд. ВСЕ остальные индексы ссылаются на него (вместо row ID) !
http://www.sql.ru/articles/mssql/03013101Indexes.shtml