Как абсолютно избежать блокировки исходной таблицы?
Справочная информация: я извлекаю данные из базы данных IBM Filenet P8 Workflow. Каждый раз, когда механизм содержимого P8 вносит изменения в сохраненный документ, он отправляет запись в мою исходную таблицу. Каждое изменение записывается в этой таблице. Моя задача - использовать эти данные, найти самую последнюю версию метаданных для каждого документа и заполнить отдельную таблицу для использования нашим веб-сайтом.
К сожалению, мой код блокирует исходную таблицу на несколько секунд за раз. Это не может быть разрешено. Блокировка таблицы даже на несколько секунд может привести к каскаду проблем в механизме контента P8, который находится под большой нагрузкой в течение рабочего дня.
У нас еще нет базы данных отчетов. Пока мы его не получим, мне нужно использовать таблицу P8.
Мы в порядке с загрузкой грязных данных.
Техническое резюме:
- Исходная таблица:
VWVL1, содержит много записей в документе. Я использую пару CTE для извлечения самой последней строки в документе (F_WobNumтипаbinary(16)). - Таблица назначения:
AppSearchIndex, содержит одну запись на документ, для использования на нашем сайте. Эта таблица содержит ключи к различным таблицам и сводную информацию для использования веб-сайтом.
with (NOLOCK) кажется, не работает, как ожидалось. Я думаю, что это происходит, если таблица заблокирована до завершения всей транзакции.
Вопрос: я должен перейти на использование курсора, или как правильно предотвратить блокировку? Это merge? Я попытался перетащить исходные данные во временную таблицу, а затем найти оттуда самую последнюю строку для каждого документа, но это не имело заметного различия. Если мое предположение о том, что блокировка таблицы не снята до тех пор, пока транзакция не будет правильной, то это объясняет, почему временная таблица не работает.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CSP_Update_AppSearchIndex]
AS
BEGIN
SET NOCOUNT ON
--
-- 1451606400 is approx January 1, 2016
--
DECLARE @timestampStart BIGINT,
@timestampEnd BIGINT,
@secondsPerDay INT
SELECT @timestampStart = ISNULL(MAX([F_Timestamp]), 1439556451) FROM dbo.CSP_AppSearchIndex;
SELECT @secondsPerDay = 60 * 60 * 24;
SELECT @timestampEnd = @timestampStart + (@secondsPerDay * 75);
--
-- @timestampStart and @timestampEnd are an attempt to restrict the amount of data processed
-- in an effort to reduce the runtime. But regardless, the table is still getting locked.
--
PRINT 'From: ' + CONVERT(VARCHAR, DATEADD(DAY, @timestampStart / @secondsPerDay, '1970-01-01'))
PRINT 'To: ' + CONVERT(VARCHAR, DATEADD(DAY, @timestampEnd / @secondsPerDay, '1970-01-01'));
WITH src AS
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY wf_log.F_WobNum
ORDER BY
wf_log.F_TimeStamp DESC,
wf_log.F_SeqNumber DESC
) AS n,
wf_log.F_WobNum,
wf_log.F_TimeStamp,
wf_log.F_SeqNumber,
wf_log.AppType,
wf_log.Branch,
wf_log.Company,
wf_log.ClassID,
wf_log.County,
wf_log.currentStep,
wf_log.DirectMarket,
CASE
WHEN wf_log.DOB is NULL THEN NULL
WHEN wf_log.DOB < -2840140800 THEN NULL -- corresponds to jan 1, 1880.
ELSE DATEADD(DAY, wf_log.DOB / @secondsPerDay, '1970-01-01')
END AS DOB,
wf_log.DocClass,
wf_log.FirstName,
wf_log.MI,
wf_log.LastName,
wf_log.IDNumber,
wf_log.LastStep,
wf_log.PlanID,
CASE wf_log.documentTitle
WHEN 'ONLINE APP' THEN 'ONLINE'
ELSE 'PAPER'
END AS [Source],
wf_log.SpouseSSN,
wf_log.SSN,
wf_log.Status,
wf_log.InitiatingAttachmentId
FROM [FNSQL2008\FNP8].WFDB.dbo.VWVL1_TRH wf_log WITH (NOLOCK)
WHERE wf_log.F_TimeStamp BETWEEN @timestampStart AND @timestampEnd
AND wf_log.F_EventType = 360
AND wf_log.FirstName <> ''
AND wf_log.Status <> ''
AND wf_log.DocClass = 'NewApp'
), srcData AS
(
SELECT * FROM src WHERE n = 1
)
MERGE INTO dbo.CSP_AppSearchIndex AS asi
USING srcData ON asi.F_WobNum = srcData.F_WobNum
WHEN MATCHED THEN
UPDATE SET asi.F_Timestamp = srcData.F_Timestamp,
asi.F_SeqNumber = srcData.F_SeqNumber,
asi.AppType = srcData.AppType,
asi.Branch = srcData.Branch,
asi.Company = srcData.Company,
asi.ClassID = srcData.ClassID,
asi.County = srcData.County,
asi.CurrentStep = srcData.currentStep,
asi.DirectMarket = srcData.DirectMarket,
asi.DateOfBirth = srcData.DOB,
asi.DocClass = srcData.DocClass,
asi.FirstName = srcData.FirstName,
asi.MiddleInitial = srcData.MI,
asi.LastName = srcData.LastName,
asi.IDNumber = srcData.IDNumber,
asi.LastStep = srcData.LastStep,
asi.PlanID = srcData.PlanID,
asi.Source = srcData.Source,
asi.SpouseSSN = srcData.SpouseSSN,
asi.SSN = srcData.SSN,
asi.Status = srcData.Status,
asi.InitiatingAttachmentId = srcData.InitiatingAttachmentId,
asi.ModifiedDateUtc = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
INSERT ([WobNum], [F_WobNum], [F_Timestamp], [F_SeqNumber], [LastStep], [CurrentStep], [AppType], [Branch], [ClassId],
[Company], [County], [DirectMarket],[DateOfBirth], [DocClass], [FirstName], [IdNumber], [LastName], [MiddleInitial],
[PlanId], [Source], [SpouseSsn], [Ssn], [Status], [InitiatingAttachmentId], [CreatedDateUtc], [ModifiedDateUtc])
VALUES (
CONVERT(VARCHAR(50), srcData.F_Wobnum, 2),
srcData.F_WobNum,
srcData.F_TimeStamp,
srcData.F_SeqNumber,
srcData.LastStep,
srcData.currentStep,
srcData.AppType,
srcData.Branch,
srcData.ClassID,
srcData.Company,
srcData.County,
srcData.DirectMarket,
srcData.DOB,
srcData.DocClass,
srcData.FirstName,
srcData.IDNumber,
srcData.LastName,
srcData.MI,
srcData.PlanID,
srcData.Source,
srcData.SpouseSSN,
srcData.SSN,
srcData.Status,
srcData.InitiatingAttachmentId,
GETDATE(),
GETDATE())
OUTPUT
$action AS [Action Type],
Inserted.WobNum,
Inserted.F_SeqNumber,
Inserted.F_Timestamp,
Inserted.IdNumber,
Inserted.LastName;
UPDATE dbo.CSP_AppSearchIndex
SET AppReceivedDate =
(
SELECT dv.create_date
FROM os2.dbo.DocVersion dv
WHERE dv.object_id = CSP_AppSearchIndex.InitiatingAttachmentId
)
OUTPUT Inserted.WobNum,
Inserted.F_WobNum,
Inserted.InitiatingAttachmentId,
Deleted.AppReceivedDate Old_AppReceivedDate,
Inserted.AppReceivedDate New_AppReceivedDate
WHERE AppReceivedDate IS NULL
AND LEN(InitiatingAttachmentId) > 10
END