Допускается ли несовместимое состояние в транзакции?

Asked
Viewd1514

3

У меня очень простой вопрос по транзакциям. (В sql server 2000, но я предполагаю, что это применимо к общим транзакциям db.).

 PkId        
-----
1
2
3
 

 Id   ForeignKey  
---- ----- 
1    1
2    2
3    3
4    1
 

У меня есть 2 таблицы, одна ссылается на другую (tblForeignKey.ForeignKey ссылается на tblPrimaryKey.PkID). Теперь у меня есть логика, которая изменяет таблицу первичного ключа путем удаления и повторной вставки ключа.

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

Думаю, что-то вроде этого должно сработать:

 BEGIN TRAN eg

    DELETE tblPrimaryKey WHERE PkId = 3     
    INSERT INTO tblPrimaryKey  SELECT 3

COMMIT TRAN eg
 

Но это не работает. Может ли кто-нибудь предоставить мне пример рабочей транзакции, в которой применяется эта логика?

ОБНОВЛЕНИЯ:

Последовательность Эта характеристика означает, что база данных должна быть согласованной до и после транзакции.

Ни в коем случае нельзя зафиксировать частичную транзакцию в базе данных, поскольку это приведет к тому, что база данных останется в несогласованном состоянии.

Не означает ли это, что в возможна несогласованность транзакции?

ОБНОВЛЕНИЕ:

Некоторые спрашивали меня, почему я не использовал обновление в этом случае. Довольно сложно, но я попробую: необходимый sql был частью сценария публикации, который строит таблицы из представлений, а затем обновляет эти таблицы. Поскольку представления содержали модель публикации, изменения представления были сделаны там и только там. Остальная часть скрипта не может полагаться на имена столбцов для обновления.

Конечно, я мог бы запросить эти названия столбцов, но в то время это казалось проблемой, поэтому я решил не делать этого, а вместо этого отбросил ограничения и перестроил их. Теперь я должен признать, что я не чувствовал себя комфортно с этим решением, поэтому теперь я действительно использую вместо него обновление. Я написал sproc для этого, если у кого-то есть другое решение, сообщите мне.

 CREATE PROC usp_SyncRecords
(
 @tableName1 as nvarchar(255),
 @tableName2 as nvarchar(255), 
 @joinClause as nvarchar(255),
 @whereClause as nvarchar(1000)
)
-- this proc updates all fields in table 1 that have corresponding names 
-- in table2 to the value of the field in table2.
AS 
BEGIN 
    DECLARE @sqlClause nvarchar(4000)
    DECLARE @curFieldName nvarchar(255)
    DECLARE @sqlColumnCursorClause nvarchar(1000)
    SET @sqlClause = 'UPDATE [' + @tableName1 + '] SET '

    -- get FieldNames for second table 
    SET @sqlColumnCursorClause = 
        'DECLARE cur CURSOR FAST_FORWARD FOR SELECT name FROM syscolumns ' + 
        'WHERE id=' + CAST(object_id(@tableName2) as nvarchar(50))

    EXEC sp_executeSql @sqlColumnCursorClause


    OPEN cur
        -- compose sqlClause using fieldnames
        FETCH NEXT FROM CUR INTO @curFieldName
        WHILE @@fetch_status <> -1 
        BEGIN 
            SET @sqlClause = @sqlClause + @curFieldName  + '=' +
                                                      @tableName2 +  '.' + @curFieldName  + ','
            FETCH NEXT FROM CUR INTO @curFieldName
        END

    CLOSE cur 
    DEALLOCATE cur 

    -- drop last comma 
    SET @sqlClause = LEFT(@sqlClause,LEN(@sqlClause) -1)

    -- adding from/join/where clauses 
    SET @sqlClause = @sqlClause + ' FROM [' + @tableName1 + '] INNER JOIN [' + @tableName2 + '] '
               + 'ON ' + @joinClause +  ' WHERE '  +  @whereClause

    EXEC sp_executeSQL @sqlClause

END
 
  • что вы на самом деле пытаетесь сделать?

    Mitch Wheat07 июня 2009, 07:33
  • Вы говорите, что не можете вставить в таблицу tblPrimaryKey, потому что столбцы PkId (или Id) являются столбцами с идентификаторами?

    Kane07 июня 2009, 07:41
  • @kane: нет, здесь ничего общего с личностью

    Peter07 июня 2009, 07:57

4 ответов

2

Самым чистым решением было бы отложить ограничение внешнего ключа. Это отложит проверку ограничения до COMMIT времени, что позволит временно нарушить его во время транзакции. К сожалению, эта функция явно недоступна в SQL Server. В системах, которые поддерживают отложенные ограничения, будет работать что-то вроде следующего:

 alter table tblForeignKey
  modify constraint YourFKNameHere
    deferrable
    initially deferred;
 

Некоторые системы не позволяют изменять возможность отсрочки ограничения, и в этом случае вам придется заново создать ограничение (и, возможно, таблицу).

Оператор SET CONSTRAINT[S] может использоваться для переключения отложенного ограничения, например в начале транзакции:

 set constraint YourFKNameHere deferred;
 

По моему опыту, свойства ACID, хотя и четко различаются, обычно работают вместе. Например, в вашей проблеме вы пытаетесь выполнить обновление, которое временно недействительно. Другие пользователи не увидят никаких ваших изменений (Изоляция, Атомарность), пока вы их не зафиксируете (Долговечность), и никакая часть вашей транзакции не будет иметь никакого эффекта (Атомарность), если ваша транзакция не завершится с базой данных в согласованном состоянии (Согласованность).

  • За исключением того, что deferred не является ключевым словом SQL Server (а в вопросе указан SQL 2005)

    Удаление и воссоздание ограничений возможно, но в SLQ Server это приведет к некоторым довольно неприятным проблемам масштабируемости (добавление или удаление ограничений требует блокировки sch-M)

    GilaMonster07 июня 2009, 20:43
  • Благодарим за исправления. ответ изменен соответственно. Судя по стилю вопроса, «general db. транзакции »и отсутствие тега« sqlserver »казалось, что вопрос мог быть более общим. :)

    cheduardo09 июня 2009, 00:23
  • Это не решение SQL Server любой версии

    gbn08 июня 2009, 10:35
0

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

Похоже, вместо пары DELETE / INSERT вам лучше просто ОБНОВИТЬ нужную строку? Либо так, либо вам нужно сначала удалить ключ в tblForeignKey и воссоздать его.

1

Согласованность в ACID означает, что будут записаны только действительные данные. Не то чтобы в транзакции допускались несоответствия.

Чтобы решить эту конкретную проблему SQL, но предположим, что столбцы ForeignKey могут иметь значение NULL.

 DECLARE @FKTabIDs (FKTabID int)

BEGIN TRAN eg

    INSERT FKTabIDs (FKTabID) SELECT [Id] FROM tblForeignKey WHERE ForeignKey = 3

    --Assumes NULL but could use any valid value
    UPDATE tblForeignKey SET ForeignKey = NULL WHERE ForeignKey = 3

    DELETE tblPrimaryKey WHERE PkId = 3         
    INSERT tblPrimaryKey SELECT 3

    UPDATE tFK
    SET ForeignKey = 3
    FROM tblForeignKey tFK JOIN @FKTabIDs tv ON tFK.[Id] =  tv.FKTabID
    --... or use exists, in etc if you prefer

COMMIT TRAN eg
 
4

Но мой вопрос таков: я узнал, что транзакция является атомарной, поэтому внутри транзакции разрешено несогласованное состояние.

"Атомарный" означает не это. Атомарный означает «неделимый», а для баз данных это просто означает, что транзакция является делом «все или ничего». Транзакционная целостность требует, чтобы транзакция была либо полностью зафиксирована, либо полностью откатывалась.

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

Что касается того, что вы пытаетесь сделать, я знаю, что в SQL Server 2005 вы можете временно ОТКЛЮЧИТЬ FK, и это может быть и в 2000 году. Однако обычно это не считается лучшей практикой. Вместо этого BP должна либо

1) НЕ удаляйте значение родительского ключа, а вместо этого обновляйте строку, сохраняя значение родительского ключа, ИЛИ,

2) Если вы собираетесь удалить (или изменить) родительский ключ навсегда, вам следует сначала удалить или переназначить дочерние записи.

Структурная несогласованность никогда не должна быть видна пользователям (если это так, то вы структурно испорчены).

Несогласованность транзакций допускается только внутри транзакции. Он никогда не должен быть виден за пределами транзакции (за исключением того, что уровни изоляции ниже, чем Serializable, в некоторой степени позволяют это).

Ссылочная несогласованность не имеет ничего общего с этими двумя. Однако в большинстве случаев ссылочную целостность можно отключить с помощью параметра NOCHECK:

     -- Disable the constraint.
ALTER TABLE cnst_example NOCHECK CONSTRAINT FK_salary_caps;

--Do stuff that violates RI here:

-- Reenable the constraint.
ALTER TABLE cnst_example WITH CHECK CHECK CONSTRAINT FK_salary_caps;
 

Однако это НЕ предпочтительный способ. Предпочтительный способ - вносить изменения в правильном порядке (прямо из BOL).

ПРИМЕЧАНИЕ 1. У меня нет доступа к SQL 2000, поэтому я не знаю, работает ли там вышеупомянутое. Работает с 2005 года.

ПРИМЕЧАНИЕ 2: «ОТЛОЖЕННЫЙ» - это параметр Oracle. Это не подходит для SQL Server.

  • Это действительно то, что означает атомарный. Следует заметить несогласованность в середине транзакции, поскольку транзакция неделима.

    Peter07 июня 2009, 07:53
  • Но вы можете ответить на вопрос? Все определения, похоже, указывают на допущение несогласованного состояния между ними, будь то ссылочное или иное.

    Peter07 июня 2009, 18:36
  • Питер: вы пытаетесь применить термин "атомарный", чтобы думать, что он не предназначен для применения. «Неделимый» просто означает, что у вас не может остаться только часть транзакции, когда вы закончили . Это не означает, что ссылочный механизм не может выдавать ошибки, а означает, что таблица не может выдавать ошибки нарушения PK или что компилятор не может выдавать синтаксические ошибки.

    RBarryYoung07 июня 2009, 08:14
  • Ничего из того, что мне известно, не указывает в этом направлении. Атомарность, как она определена для реляционных баз данных, не имеет к этому никакого отношения.

    RBarryYoung07 июня 2009, 19:37