Distributed transaction and ORA-01591: lock held by in-doubt distributed transaction

Иногда при работе с удаленной базой non-Oracle по средствам Heterogenous service (HSODBC) или Oracle GateWay (DG4MSQL) может возникнуть ситуация, когда во время распределенной транзакции происходит сбой (при DML-операциях) и транзакция зависает, что может вызвать эксклюзивные блокировки некоторых объектов или вообще заблокировать доступ к удаленной базе.
Чтобы разрешить эту ситуацию нужно выполнить следующие шаги:

1. Проверяем alert_<your_dbname>.log на ошибки связанные с распределенными транзакциями или смотрим на всплывающую ошибку при работе с удаленной базой. Например, ORA-01591: lock held by in-doubt distributed transaction 9.3.919809,
где 9.3.919809 — это ID транзакции, которая вызывает ошибку.

2. Проверяем в каком состоянии (State) находится транзакция:

SELECT LOCAL_TRAN_ID,
STATE,
FAIL_TIME,
TOP_OS_USER,
TOP_OS_TERMINAL
FROM SYS.PENDING_TRANS$
WHERE LOCAL_TRAN_ID = '9.3.919809';

LOCAL_TRAN_ID   STATE       FAIL_TIME              TOP_OS_USER      TOP_OS_TERMINAL
---             ---         ---                    ---              ---
9.3.919809      prepared    12.05.2014 14:55:00    ITWARE\ITUSER    IT03

LOCAL_TRAN_ID - ID транзакции
STATE - состояние транзакции (особое внимание!)
FAIL_TIME - время сбоя
TOP_OS_USER - имя пользователя ОС
TOP_OS_TERMINAL - имя компьютера пользователя

3. Определив состоянии зависшей транзакции, можем перейти к таблице 1, где описаны варианты решения проблем при помощи пакета DBMS_TRANSACTION (состояние транзакции в поле State Column):

STATE Column

State of Global Transaction

State of Local Transaction

Normal Action

     Alternative Action

Collecting Rolled back Rolled back None PURGE_LOST_DB_ENTRY(only if autorecovery cannot resolve transaction)
Committed Committed Committed None PURGE_LOST_DB_ENTRY(only if autorecovery cannot resolve transaction)
Prepared Unknown Prepared None Force commit or rollback
Forced commit Unknown Committed None PURGE_LOST_DB_ENTRY(only if autorecovery cannot resolve transaction)
Forced rollback Unknown Rolled back None PURGE_LOST_DB_ENTRY(only if autorecovery cannot resolve transaction)
Forced commit Mixed Committed Manually remove  inconsistencies then use PURGE_MIXED
Forced rollback Mixed Rolled back Manually remove inconsistencies then use PURGE_MIXED

Пример
После того, как произошел сбой при обновлении данных в одной из таблиц удаленной базы (MS SQL) через интерфейс Oracle DG4MSQL, дальнейшее подключение к этой базе из Oracle стало невозможным. Процесс DG4MSQL.exe отключен, сессия пользователя отключена, но зависшая транзакция не завершилась (не откатилась).
Выполнив п.1 — 3, описанные выше, определил, что зависшая распределенная транзакция 9.3.919809 находится в состоянии prepared. По таблице 1 в качестве альтернативы решения, предлагается выполнить commit force или rollback force.
Я использовал:

commit force '9.3.919809';

Затем проверил вновь состояние транзакции, оно изменилось на forced commit. По таблице 1 предлагается удалить транзакцию при помощи процедуры DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY:

EXEC DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('9.3.919809');

После выполнения данной процедуры, транзакция была успешно удалена, и она исчезла из зависших (запрос п.2 ничего не вернул по ней). После чего подключение к удаленной базе было восстановлено.

Дополнительно:

http://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_txns001.htm#ADMIN12211
http://www.dba-oracle.com/t_two_phase_commit_2pc.htm
http://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_txnman006.htm#ADMIN12266
http://blog.itpub.net/38267/viewspace-713103
https://blogs.oracle.com/db/entry/oracle_support_master_note_for_troubleshooting_managed_distributed_transactions_doc_id_1006641

Реклама

MS SQL WORKTIME — расчет рабочего времени

Я рассматривал случай рабочего дня с 08:00 по 17:00, включая обед с 12:00 по 13:00. Функция рассчитывает количество часов,
которые попадают в заданный диапазон времени с учетом выходных дней (суббота и воскресенье), рамок рабочего дня и обеденного времени. Исправил одно исключение в расчете и добавил проверку праздничных дней для Беларуси.

CREATE FUNCTION [worktime] (@beg_dt datetime, @end_dt datetime)
RETURNS FLOAT
with schemabinding
AS

BEGIN

declare @beg_datetime datetime
declare @end_datetime datetime
declare @kol_day INT
declare @res FLOAT
declare @dt FLOAT
declare @i INT

set @beg_datetime = @beg_dt;
set @end_datetime = @end_dt;
set @dt = 0;
set @res = 0;
set @i = 0;
      
    IF @beg_datetime < @end_datetime
    BEGIN
        --Расчет количества дней в заданном периоде дат
       set @kol_day = datediff(day, @beg_datetime, @end_datetime);
 
        WHILE (@i <= @kol_day)
        BEGIN
          -- Проверка выходных дней и праздничные дни
            if not datepart(w, dateadd(day, @i, @beg_datetime)) in (7, 1)
            OR  ( convert(date, dateadd(day, @i, @beg_datetime)) IN (
                convert(date, convert(varchar(4), Year(dateadd(day, @i, @beg_datetime))) + '-01-01'),
                convert(date, convert(varchar(4), Year(dateadd(day, @i, @beg_datetime))) + '-01-07'),
                convert(date, convert(varchar(4), Year(dateadd(day, @i, @beg_datetime))) + '-03-08'),
                convert(date, convert(varchar(4), Year(dateadd(day, @i, @beg_datetime))) + '-04-29'),
                convert(date, convert(varchar(4), Year(dateadd(day, @i, @beg_datetime))) + '-05-01'),
                convert(date, convert(varchar(4), Year(dateadd(day, @i, @beg_datetime))) + '-05-09'),
                convert(date, convert(varchar(4), Year(dateadd(day, @i, @beg_datetime))) + '-07-04'),
                convert(date, convert(varchar(4), Year(dateadd(day, @i, @beg_datetime))) + '-11-07'),
                convert(date, convert(varchar(4), Year(dateadd(day, @i, @beg_datetime))) + '-12-25')) )
            
            BEGIN
                -- Расчет количества рабочего времени по каждому дню
                set @res = case       
                -- если промежуточный день
                when not cast(dateadd(day, @i, @beg_datetime) as date) in (cast(@beg_datetime as date), cast(@end_datetime as date))
                    then 8
                -- если даты заданного дипазона совпадают
                when @kol_day = 0 and cast(dateadd(day, @i, @beg_datetime) as date) = cast(@end_datetime as date) then
                cast(datediff(second, dateadd(day, @i, @beg_datetime), @end_datetime) as float)/60/60
                else case
                    -- если начало периода
                    when cast(dateadd(day, @i, @beg_datetime) as date) = cast(@beg_datetime as date) and
                    datepart(hh, @beg_datetime) < 8
                        then 8
                    when cast(dateadd(day, @i, @beg_datetime) as date) = cast(@beg_datetime as date) and
                    datepart(hh, @beg_datetime) >= 8 and datepart(hh, @beg_datetime) < 12
                        then cast(datediff(second, @beg_datetime, dateadd(hour, 17, cast(cast(@beg_datetime as date) as datetime))) as float)/60/60 - 1
                    when cast(dateadd(day, @i, @beg_datetime) as date) = cast(@beg_datetime as date) and
                    datepart(hh, @beg_datetime) >= 13 and datepart(hh, @beg_datetime) < 17
                        then cast(datediff(second, @beg_datetime, dateadd(hour, 17, cast(cast(@beg_datetime as date) as datetime))) as float)/60/60
                    -- если конец периода
                    when cast(dateadd(day, @i, @beg_datetime) as date) = cast(@end_datetime as date) and
                    datepart(hh, @end_datetime) >= 8 and datepart(hh, @end_datetime) < 12
                        then cast(datediff(second, dateadd(hour, 8, cast(cast(@end_datetime as date) as datetime)), @end_datetime) as float)/60/60
                    when cast(dateadd(day, @i, @beg_datetime) as date) = cast(@end_datetime as date) and
                    datepart(hh, @end_datetime) >= 13 and datepart(hh, @end_datetime) < 17
                        then cast(datediff(second, dateadd(hour, 9, cast(cast(@end_datetime as date) as datetime)), @end_datetime) as float)/60/60
                    when cast(dateadd(day, @i, @beg_datetime) as date) = cast(@end_datetime as date) and
                    datepart(hh, @end_datetime) > 17
                        then 8
                    -- ошибка
                    else 0
                    END
                END
            -- Расчет суммароного рабочего времени за период
            set @dt = @dt + @res;

            END
            set @i = @i + 1;
        END
    END
    ELSE
        set @dt = -1;

RETURN @dt

END

Пример вызова функции:

select [worktime]('2014-06-02 10:20:00.000', '2014-06-15 16:45:37.000') dt
dt
---
77,6666666666667

MSSQL date and time functions

Несколько часто применяемых функций даты и времени в MSSQL.

CREATE FUNCTION [BEGDAY] (@DAT DATETIME)
RETURNS DATETIME
with schemabinding
AS
/* Возвращает начало дня: 2014-05-19 00:00:00.000 */
BEGIN
	DECLARE @RET DATETIME
	set @RET = null
	SET @RET=cast(cast(@DAT as date) as datetime)
	RETURN @RET
END
GO

CREATE FUNCTION [ENDDAY] (@DAT DATETIME)
RETURNS DATETIME
with schemabinding
AS
/* Возвращает конец дня: 2014-05-19 23:59:59.997 */
BEGIN
	DECLARE @RET DATETIME
	set @RET = null
	SET @RET=(convert(datetime, convert(varchar, @DAT, 101) + ' 23:59:59.998'))
	RETURN @RET
END
GO

CREATE FUNCTION [BEGMONTH] (@DAT DATETIME)
RETURNS DATETIME
with schemabinding
AS
/* Возвращает начало месяца: 2014-05-01 00:00:00.000 */
BEGIN
	DECLARE @RET DATETIME
	set @RET = null
	SET @RET=convert(date,dateadd(day,1-day(@DAT),@DAT))
	RETURN @RET
END
GO

CREATE FUNCTION [ENDMONTH] (@DAT DATETIME)
RETURNS DATETIME
with schemabinding
AS
/* Возвращает конец месяца: 2014-05-31 00:00:00.000 */
BEGIN
	DECLARE @RET DATETIME
	set @RET = null
	SET @RET=convert(datetime, convert(varchar, dateadd(month,1,dateadd(day,1-day(@DAT),@DAT))-1, 101))
	RETURN @RET
END
GO

Примеры вызова функций:

select [BEGDAY] ('2014-04-23 09:45:21') dt
select [BEGDAY] ('2014-04-23') dt
dt
--
2014-04-23 00:00:00.000

select [ENDDAY] ('2014-04-23 09:45:21') dt
select [ENDDAY] ('2014-04-23') dt
dt
--
2014-04-23 23:59:59.997

select [BEGMONTH] ('2014-04-23 09:45:21') dt
select [BEGMONTH] ('2014-04-23') dt
dt
--
2014-04-01 00:00:00.000

select [ENDMONTH] ('2014-04-23 09:45:21') dt
select [ENDMONTH] ('2014-04-23') dt
dt
--
2014-04-30 00:00:00.000