데이터베이스의 원자 비교 및 스왑

데이터베이스의 원자 비교 및 스왑

저는 작업 대기열 솔루션 작업을 하고 있습니다.데이터베이스에서 상태 열에 특정 값이 있는 특정 행을 쿼리하여 해당 값을 수정하고 행을 반환하면 다른 쿼리에 표시되지 않도록 원자적으로 수행합니다.

begin transaction
select * from table where pk = x and status = y
update table set status = z where pk = x
commit transaction
--(the row would be returned)

두 개 이상의 동시 쿼리가 행을 반환하는 것은 불가능해야 합니다(한 쿼리 실행 시 상태가 = y일 때 행을 볼 수 있음). 이는 일종의 연동된 CompareAndExchange 작업과 같습니다.

위의 코드가 (SQL 서버의 경우) 실행되는 것은 알고 있지만, 스왑은 항상 원자적인 것입니까?

SQL Server 및 Oracle에 적합한 솔루션이 필요합니다.

PK가 기본 키입니까?그러면 문제가 되지 않습니다. 기본 키를 이미 알고 있다면 스포츠가 없습니다.pk가 기본 키인 경우, 이 질문은 당신이 디큐할 항목의 pk를 어떻게 알 수 있는지에 대한 명백한 질문입니다.

문제는 기본 키를 모르는 상태에서 다음 '사용 가능'(즉, 상태 = y)의 큐를 해제하고 큐를 해제(삭제하거나 상태 = z 설정)하려는 경우입니다.

이를 위한 적절한 방법은 하나의 문장을 사용하는 것입니다.안타깝게도 Oracle과 SQL Server 간에 구문이 다릅니다.SQL Server 구문은 다음과 같습니다.

update top (1) [<table>]
set status = z 
output DELETED.*
where  status = y;

저는 SQL의 OUTPUT과 유사한 예를 제시할 정도로 Oracle의 RETURN 절을 잘 모릅니다.

다른 SQL Server 솔루션의 경우 SELECT(업드락 포함)에 대한 잠금 힌트가 정확해야 합니다.Oracle에서는 FOR UPDATE를 사용하지만, FOR UPDATE를 SQL의 커서와 함께 사용하기 때문에 SQL Server에서는 사용할 수 없습니다.

어쨌든 원래 게시물에서 했던 행동은 잘못된 것입니다.여러 세션에서 동일한 행을 모두 선택할 수도 있고, 심지어 모든 세션을 업데이트할 수도 있으며, 동일한 대기열 해제 항목을 여러 독서자에게 반환할 수도 있습니다.

일반적으로 이 원자와 같은 작업을 수행하려면 선택을 수행할 때 다른 트랜잭션이 업데이트 전에 행을 읽을 수 없도록 독점(또는 업데이트) 잠금을 설정해야 합니다.

이에 대한 일반적인 구문은 다음과 같습니다.

 select * from table where pk = x and status = y for update

확인하려면 찾아봐야 할 것 같아요

비슷한 패턴을 따르는 애플리케이션이 몇 개 있습니다.당신의 것과 같은 작업 줄을 나타내는 테이블이 있습니다.테이블에는 thread_id와 thread_date라는 두 개의 추가 열이 있습니다.앱이 큐에 작업을 요청하면 스레드 ID를 제출합니다.그런 다음 하나의 업데이트 문이 제출된 ID를 가진 스레드 ID 열과 현재 시간을 가진 스레드 날짜 열로 모든 해당 행을 업데이트합니다.업데이트 후 해당 스레드 ID를 가진 모든 행을 선택합니다.이렇게 하면 명시적인 거래를 선언할 필요가 없습니다.초기 업데이트 시 "잠금"이 발생합니다.

thread_date 열은 작업 항목이 분리되지 않도록 하는 데 사용됩니다.큐에서 항목을 꺼냈다가 앱이 충돌하면 어떻게 됩니까?당신은 그 작업 아이템들을 다시 시도할 수 있는 능력이 있어야 합니다.따라서 완료된 것으로 표시되지 않았지만 먼 과거에 스레드 날짜가 있는 스레드에 할당된 모든 항목을 대기열에서 잡을 수 있습니다."거리"를 정의하는 것은 당신에게 달려있습니다.

이거 먹어봐요.유효성 검사는 UPDATE 문에 있습니다.

코드

IF EXISTS (SELECT * FROM sys.tables WHERE name = 't1')
    DROP TABLE dbo.t1
GO
CREATE TABLE dbo.t1 (
    ColID       int         IDENTITY,
    [Status]    varchar(20)
)
GO

DECLARE @id             int
DECLARE @initialValue   varchar(20)
DECLARE @newValue       varchar(20)

SET @initialValue = 'Initial Value'

INSERT INTO dbo.t1 (Status) VALUES (@initialValue)
SELECT @id = SCOPE_IDENTITY()

SET @newValue = 'Updated Value'

BEGIN TRAN

UPDATE dbo.t1
SET
    @initialValue = [Status],
    [Status]      = @newValue
WHERE ColID    = @id
  AND [Status] = @initialValue

SELECT ColID, [Status] FROM dbo.t1

COMMIT TRAN

SELECT @initialValue AS '@initialValue', @newValue AS '@newValue'

결과.

ColID Status
----- -------------
    1 Updated Value

@initialValue @newValue
------------- -------------
Initial Value Updated Value

언급URL : https://stackoverflow.com/questions/1506950/atomic-compare-and-swap-in-a-database