Многопользовательская работа в среде MSSQL Server

← →
_Anton (2006-03-22 19:16) [0]

Проблема довольно специфичная:
блокируется таблица в эксклюзивную блокировку до окончания
работы приложения.

подробно:
на главной форме есть ADOConnection, этот коннект передаётся
во все формы через свойство FConnection: TADOConnection

1. блокируем конкретную строку в SQL Query Analyzer
так:

begin tran

SELECT CNAME
FROM WH_Social.dbo.OWH_OrgType_tb WITH (UPDLOCK)
WHERE CNAME IN ("Банк")

2. открываем нашу форму с переданным через свойство
коннектом и пытаемся удалить эту строку вызовом
процедуры:

CREATE PROCEDURE dbo.OWH_DeleteTypeOrg_sp
--
-- DESCRIPTION: Процедура удаления типа ППВ из справочника
--
-- for WH_Social
--
(
@p_TypeOrgName varchar(100)
)
AS
BEGIN

DECLARE @l_ErrCode INT

IF @p_TypeOrgName IS NULL BEGIN
SET @l_ErrCode = -1
RAISERROR("Пустой параметр процедуры", 16, 1)
GOTO FINAL
END

BEGIN TRANSACTION
SAVE TRANSACTION transp_DelTypeOrg

DELETE FROM WH_Social.dbo.OWH_OrgType_tb
WHERE CNAME = @p_TypeOrgName

IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
SET @l_ErrCode = -2
RAISERROR("Ошибка удаления", 16, 1)
GOTO FINAL_TRAN
END

FINAL_TRAN:
IF @l_ErrCode <> 0 ROLLBACK TRANSACTION transp_DelTypeOrg
COMMIT TRANSACTION

FINAL:
RETURN(@l_ErrCode)
END

3. Истекает timeout ADOStoredProc.

4. делаем откат\коммит нашей транзакции в SQL Query Analyzer

5. теперь повторяем попытку удалить строку через форму.
и видим, что строка удалена в нашей сесии.

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

7. по окончании работы приложения удалённая через форму
строка появляется в таблице.

буду очень признателен за разьяснения, перепробывал уже всё, что пришло на ум

← →
Nikolay M. © (2006-03-22 19:38) [1]

1) Возможная причина эскалация эксклюзивной блокировки со строки до таблицы - в отсутствии индекса на CNAME или, что маловероятно, в слишком высоком уровне изоляции SERIALIZABLE.

2) Не понял сакральный смысл такой ХП (зачем нужна транзакция, да еще с сейвпоинтом), но сразу вижу отсутствие инициализации @l_ErrCode, что может является причиной ролбэка в конце ХП.

← →
Nikolay M. © (2006-03-22 19:52) [2]

> Nikolay M. © (22.03.06 19:38) [1]
> 1) Возможная причина эскалация

1) Возможная причина эскалациИ

← →
Ega23 © (2006-03-22 19:58) [3]

> но сразу вижу отсутствие инициализации @l_ErrCode

Ну да, как минимум после As Begin надо поставить Set @l_ErrCode=0

← →
_Anton (2006-03-23 11:01) [4]

- инициализация @l_ErrCode здесь не при чём, можно и без неё;
- индекс по CNAME может и нужен, но там словарная таблица из двух
столюцов, такие таблицы в ORACLE организуют по индексу, а в MS не
знаю, можно ли так;
- явное написание транзакции в процедуре осталось у меня после её
правки, там ещё были операторы;

Решил проблему:
- перенёс явное управление транзакцией из процедуры в форму, всё
заработало;

← →
Ega23 © (2006-03-23 11:44) [5]

> - инициализация @l_ErrCode здесь не при чём, можно и без
> неё;

Я бы не стал так категорично утверждать.
Набери в QA следующий код:
Declare @X int Select X=@X

И выполни.

← →
Nikolay M. © (2006-03-23 14:06) [6]

> - инициализация @l_ErrCode здесь не при чём, можно и без
> неё;
> - индекс по CNAME может и нужен, но там словарная таблица
> из двух
> столюцов, такие таблицы в ORACLE организуют по индексу,
> а в MS не
> знаю, можно ли так;

Инициализация переменной нужна - это раз.
Не знаю, что такое "словарная таблица из 2 столбцов", но если индекса по CNAME нет, то я правильно указал причину эксклюзивной блокировки на всю таблицу - это два.
Ну и, наконец, мое непокобелимое имхо заключается в том, что управление транзакциями с клиента неверно идеологически - это три.

← →
_Anton (2006-03-23 16:00) [7]

:)
да знаю я что NULL без инициализации

индекс я тоже само собой добавил,
а на счет управления транзакциями с клиента -
например без этого не обойтись при select for update

спасибо всем

← →
Ega23 © (2006-03-23 16:34) [8]

> да знаю я что NULL без инициализации

А раз знаешь, то посмотри внимательно на свою процедуру и назови тот случай, когда у тебя Commit Tran произойдёт.

IF @l_ErrCode <> 0 ROLLBACK TRANSACTION transp_DelTypeOrg COMMIT TRANSACTION

← →
_Anton (2006-03-23 16:47) [9]

sorry

← →
Nikolay M. © (2006-03-23 16:59) [10]

> Ega23 © (23.03.06 16:34) [8]
> А раз знаешь, то посмотри внимательно на свою процедуру
> и назови тот случай, когда у тебя Commit Tran произойдёт.
>
>
> IF @l_ErrCode <> 0 ROLLBACK TRANSACTION transp_DelTypeOrg
> COMMIT TRANSACTION

Очевидно, при

SET ANSI_NULLS OFF.

← →
Ega23 © (2006-03-23 17:02) [11]

> SET ANSI_NULLS OFF.

Не вижу явного объявления. :о)

> Ega23 © (23.03.06 17:02) [11]
> > SET ANSI_NULLS OFF.
> Не вижу явного объявления. :о)

Так именно поэтому у него запись и не удаляется :))))

← →
_Anton (2006-03-23 19:20) [13]

ни индекс, ни SET ANSI_NULLS OFF не помогли;
дело всё-таки в ADO

честно говоря на счет помощи в этом деле индекса я сомневался,
знаю, что эскалация происходит при неиндексировании внешних ключей (опять же в oracle), а насчёт одной таблицы - ничего не слышал,
хотя чем Microsoft не шутит

CREATE PROCEDURE dbo.OWH_DeleteTypeOrg_sp
--
-- DESCRIPTION: Процедура удаления типа ППВ из справочника
--
-- for WH_Social
--
(
@p_TypeOrgName varchar(100)
)
AS
BEGIN
SET ANSI_NULLS OFF

DECLARE @l_ErrCode INT

SET @l_ErrCode=0

IF @p_TypeOrgName IS NULL BEGIN
SET @l_ErrCode = -1
RAISERROR("Пустой параметр процедуры", 16, 1)
GOTO FINAL
END

BEGIN TRANSACTION
SAVE TRANSACTION transp_DelTypeOrg

DELETE FROM WH_Social.dbo.OWH_OrgType_tb
WHERE CNAME = @p_TypeOrgName

IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
SET @l_ErrCode = -2
RAISERROR("Ошибка удаления", 16, 1)
--GOTO FINAL_TRAN
END

FINAL_TRAN:
IF @l_ErrCode <> 0 ROLLBACK TRANSACTION transp_DelTypeOrg
COMMIT TRANSACTION

FINAL:
RETURN(@l_ErrCode)
END
GO

> _Anton (23.03.06 19:20) [13]
> ни индекс, ни SET ANSI_NULLS OFF не помогли;

А селективность поля какая? Т.е. сколько уникальных записей и сколько всего записей в таблице?
Если таблица большая, а значения в поле, скажем, только "0" и "1", то индекс тут не поможет, при выборке с хинтом UPDLOCK в незакрытой транзакции потребуется сканирование таблицы, что тоже может привести к блокировке таблицы.
Хотя, честно говоря, устал я гаданиями на кофейной гуще заниматься. Готовые скрипты и данные в студию.

← →
_Anton (2006-03-24 12:02) [15]

:)
если бы в столбце были "1" и "0", то не проще построить bitmap?

ещё раз спасибо всем, вопрос считаю снятым

> _Anton (24.03.06 12:02) [15]
> если бы в столбце были "1" и "0", то не проще построить
> bitmap?

Есть таблица неких сущностей, у каждой есть признак Enabled/Disabled, т.е. 0/1. Какую маску ты собрался строить?

Многопользовательская работа в среде MSSQL Server Найти похожие ветки