ODBCConnection и обработка deadlock

[abark]

Добрый день!

Подскажите пожалуйста как делать обработку потенциальных дедлоков при работе с подключением к внешней базе данных через ODBC.
Есть подобный код:

LoginProperty          lp;
ODBCConnection         conn;
Statement              Statement;
str                     query;
ResultSet              rs;
counter                counter = 0;
;

    lp = new LoginProperty();
    lp.setDSN("SomeDB");
    conn = new ODBCConnection(lp);
    Statement = conn.createStatement();
    query = strfmt( @"exec [dbo].[SomeSP]"  );
    rs = Statement.executeQuery(query);
    while (rs.next())           // **** Тут иногда валится с ошибкой (1)
    {
        counter++;
    }

Он порой валится с ошибкой (1) вида:

    "" ("")
 .           .

При этом в хранимой процедуре в начале стоят SET NOCOUNT ON ; SET ANSI_WARNINGS OFF ;
И в единственном select все таблицы с хинтами (nolock), но все равно происходит deadlock как-то. Как его правильно отловить в вызове из Axapta ? что-нибудь наподобие

try {ttsbegin; ... ttscommit; } catch(Exception::Deadlock) { retry; }

для ODBC есть ?

[Player1]

SET DEADLOCK_PRIORITY

[Владимир Максимов]

Цитата:

И в единственном select все таблицы с хинтами (nolock)

Значит, кроме select у Вас есть еще и модификация данных в Вашей процедуре (update/insert/delete). Или при работе Вашей процедуры Вы используете вызов других процедур, которые уже внутри себя что-то там изменяют.

Я ни разу не сталкивался с подобной ошибкой при использовании только Select с nolock. Посмотрите внимательнее, что еще Вы вызываете из своей ХП

Кстати, а Вы используете полный синтаксис с ключевым словом with?

select ... from MyTab with (nolock)

Сокращенный синтаксис без ключевого слова with может приводить к разным глюкам. Хотя обычно просто отказывается работать


PS: Для MS SQL есть свой аналог try.catch (кажется, введен с SQL 2005)

https://msdn.microsoft.com/ru-ru/library/ms175976.aspx

Но не уверен, что он отловит DeadLock

[abark]

Спасибо большое, Владимир.
Используется именно сокращенная форма, без with. Никогда раньше с этим проблем не замечал, и не отказывалось работать.

Посмотрел хранимую процедуру внимательно - ничего там дополнительно не вызывается и модификаций нет.
На всякий случай вот ее код:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--   
-- exec [dbo].[ExtraFieldsForAxapta]
/* exec [dbo].[ExtraFieldsForAxapta] 
		@Division = 'SS', 
		@Store = 'P16_',
		@Supplier = 'RCAuto_',
		@SubSupplier = '07КАУ00000_ряд01-02__',
		@OrderDate = '30.10.2013',
		@DeliveryDate = NULL, --'25.10.2013',
		@ItemID = NULL
*/
ALTER PROCEDURE [dbo].[ExtraFieldsForAxapta]			-- exec [dbo].[ExtraFieldsForAxapta]
(
	@Division		varchar(2) = NULL,
	@Store			varchar(4) = NULL,
	@Supplier		varchar(7) = NULL,
	@SubSupplier	varchar(21) = NULL,
	@OrderDate		datetime = NULL,
	@DeliveryDate	datetime = NULL,
	@ItemID			varchar(6) = NULL
)
AS
BEGIN
	SET NOCOUNT ON ;
	SET ANSI_WARNINGS OFF ;

	select 	sup.SSTOID, sup.SUBSSTOID, sup.NAME as SupplierDescription,
			it.DIV_ID, it.SITE_ID, it.ITEM_ID, it.SKU_NAME, 
			o.STATE,
			CASE WHEN o.STATE in ('PROPOSED','ISSUED') THEN 1 
				 WHEN o.STATE in ('MANUAL_PROPOSED','MANUAL_ISSUED') THEN 0 ELSE 0 END as Autoorder,
			STOCK = ISNULL(it.STOCK, 0),
			it.STOCKDATE,
			EffPresStock = ISNULL(it.USEDPRESENTATIONSTOCK,0),
			it.DYNAMICPRESENTATIONSTOCK,
			it.PREDEFINEDPRESENTATIONSTOCK,
			it.AVERAGESALES,
			it.CLASSMEAN,
			it.CLASSMEAN / 7.0 as ClassMean_perDay,
			o.OPENORDERSQTY,
			o.EFFSTOCK,
			DATEDIFF(day, o.PLAN_ORD_DATE, d.AVAIL_DATE) as DaysOfCoverage_for_Ax,
			DATEDIFF(day, o.PLAN_ORD_DATE, o.NEXT_AVAIL_DATE) as DaysOfCoverage_for_Ax2,
			d.ACCUMDEMAND,
			d.SAFETYAMOUNT,		
			o.PLAN_ORD_DATE, o.PLAN_DELIV_DATE, o.PLAN_AVAIL_DATE,
			SafetyStock_in_days = ISNULL(d.SAFETYAMOUNT,0) / CASE WHEN (it.CLASSMEAN / 7.0) = 0 THEN 1 ELSE (it.CLASSMEAN / 7.0) END,
			l.LASTUSER,
			it.CLASS_INDEX,
			CASE it.CLASS_INDEX WHEN 0 THEN 'USS1' WHEN 1 THEN 'USS2' WHEN 2 THEN 'SS1' WHEN 3 THEN 'SS2' WHEN 4 THEN 'FS1' WHEN 5 THEN 'FS2' ELSE '' END as CLASS_INDEX_DESC
	from dbo.ORDERS o (nolock)
	inner join SUPPLIER sup (nolock) ON sup.SUPPLIER_ID = o.SUPPLIER
	inner join SKU it (nolock) ON it.SKU_ID = o.SKU_ID
	left join DEMAND d (nolock) ON d.SKU_ID = o.SKU_ID and d.IDX = 1
	inner join ORDER_TO_LIST ol (nolock) ON ol.ORDER_ID = o.ORDER_ID
	inner join ORDERLST l (nolock) ON l.ORDLIST_ID = ol.LIST_ID
	where o.STATE != 'CONFIRMED'
	  and (@Supplier is NULL or sup.SSTOID = @Supplier)
	  and (@SubSupplier is NULL or sup.SUBSSTOID = @SubSupplier)
	  and (@Division is NULL or it.DIV_ID = @Division)
	  and (@Store is NULL or it.SITE_ID = @Store)
	  and (@ItemID is NULL or it.ITEM_ID = @ItemID)
	  and (@OrderDate is NULL or o.PLAN_ORD_DATE = @OrderDate)
	  and (@DeliveryDate is NULL or o.PLAN_DELIV_DATE = @DeliveryDate)
	ORDER BY it.DIV_ID, it.SITE_ID, sup.SSTOID, sup.SUBSSTOID, it.ITEM_ID, o.STATE
END

Попробую обернуть в try catch внутри ХП, и использовать полное написание with (nolock)

[abark]

Версия SQL: MS SQL Server 2008 R2 (RTM) - 10.50.1600.1 - Standard Edition (64-bit) - (Build 7601: Service Pack 1)

[abark]

собрал такую обработку на внутри ХП:

DECLARE @RetryCount tinyint
SET @RetryCount = 0
WHILE @RetryCount < 5
BEGIN
	BEGIN TRY
		BEGIN TRANSACTION
		...
		COMMIT TRANSACTION
		BREAK
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION
		IF ERROR_NUMBER() IN (  1204, -- SqlOutOfLocks
								1205, -- SqlDeadlockVictim
								1222 -- SqlLockRequestTimeout
							) 
			and @RetryCount < 5
		begin
			SET @RetryCount = @RetryCount + 1
			WAITFOR DELAY '00:00:02'
		end
		ELSE  --    ,    
			THROW ;
		
	END CATCH;
END

--    :
-- 	[url]https://technet.microsoft.com/en-us/library/aa175791(v=sql.80).aspx[/url]
--  [url]https://www.mssqltips.com/sqlservertip/3188/implementing-sql-server-transaction-retry-logic-for-failed-transactions/[/url]
--  [url]http://stackoverflow.com/questions/7559849/is-it-a-good-idea-to-handle-deadlock-retry-from-stored-procedure-catch-block[/url]

[abark]

точнее для моей версии SQL 2008 будет так:

DECLARE @RetryCount smallint
SET @RetryCount = 5
WHILE 1=1
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION
        ...
        COMMIT TRANSACTION
        BREAK
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
        IF ERROR_NUMBER() in (1204 /*SqlOutOfLocks*/, 1205 /*SqlDeadlockVictim*/, 1222 /*SqlLockRequestTimeout*/) and @RetryCount > 0
        begin
            SET @RetryCount = @RetryCount - 1
            WAITFOR DELAY '00:00:02'
        end
        ELSE  --    ,    
        begin
            --THROW ; -- SQL 2012
            -- SQL 2008: CATCH blocks can use RAISERROR to rethrow the error that invoked the CATCH block
            DECLARE @ErrorMessage NVARCHAR(4000), @ErrorNumber INT, @ErrorSeverity INT, @ErrorState INT, @ErrorLine INT, @ErrorProcedure NVARCHAR(200);
            -- Assign variables to error-handling functions that capture information for RAISERROR.
            SELECT @ErrorNumber = ERROR_NUMBER(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(), 
                   @ErrorLine = ERROR_LINE(), @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
            -- Build the message string that will contain original error information.
            SELECT @ErrorMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 'Message: '+ ERROR_MESSAGE();
            -- Raise an error: msg_str parameter of RAISERROR will contain the original error information.
            RAISERROR (@ErrorMessage, @ErrorSeverity, 1,               
                       @ErrorNumber,    -- parameter: original error number.
                       @ErrorSeverity,  -- parameter: original error severity.
                       @ErrorState,     -- parameter: original error state.
                       @ErrorProcedure, -- parameter: original error procedure name.
                       @ErrorLine       -- parameter: original error line number.
                      );
            break;
        end
    END CATCH;
END

[Владимир Максимов]

На всякий случай замечу, что если у базы установлено свойство

SET READ_COMMITTED_SNAPSHOT ON

В списке свойств базы данных в Management Studio это выглядит как настройка

Is Read Commited Snapshort On = True

И используется стандартный режим изоляции READ COMMITTED, то режим грязного чтения смысла не имеет.

При таких комбинациях настроек данные в запросе будут считываться из "мгновенного снимка" таблиц, созданного непосредственно перед внесением изменений в эти таблицы. Т.е. не будет блокировок ожидания завершения транзакций. Хотя и данные будут считаны на момент до начала транзакций

Впрочем, в справке по уровням изоляции https://msdn.microsoft.com/ru-ru/lib...=sql.105).aspx есть такая фраза

Цитата:

Сообщение от MS SQL 2008 R2

Оптимизированные операции массовой загрузки, работающие с кучами, блокируют запросы, которые выполняются со следующими уровнями изоляции:

•SNAPSHOT
•READ UNCOMMITTED
•READ COMMITTED с использованием управления версиями строк

Обратное также верно — запросы, которые выполняются с этими уровнями изоляции, блокируют оптимизированные операции массовой загрузки, работающие с кучами.

Опция with (nolock) - это режим изоляции READ UNCOMMITTED для конкретной таблицы в запросе