단일 SQL Server 문이 원자적이고 일관성이 있습니까?

단일 SQL Server 문이 원자적이고 일관성이 있습니까?

Server SQL Server의 입니다.ACID?

그게 무슨 말입니까?

하나의 T-SQL 문이 주어지면, 다음과 같이 묶이지 않습니다.BEGIN TRANSACTION/COMMIT TRANSACTION다음은 해당 문의 동작입니다.

• 원자: 모든 데이터 수정이 수행되거나 수행되지 않습니다.
• 일관성:트랜잭션이 완료되면 모든 데이터가 일관된 상태로 유지되어야 합니다.
• 격리됨:동시 트랜잭션에 의한 수정은 다른 동시 트랜잭션에 의한 수정과 분리되어야 합니다.
• 내구성: 트랜잭션이 완료되면 해당 효과가 시스템에 영구적으로 적용됩니다.

내가 묻는 이유

활성 시스템에 쿼리 규칙을 위반하는 것으로 보이는 단일 문이 있습니다.

사실상 제 T-SQL 문은 다음과 같습니다.

--If there are any slots available, 
--then find the earliest unbooked transaction and mark it booked
UPDATE Transactions
SET Booked = 1
WHERE TransactionID = (
   SELECT TOP 1 TransactionID
   FROM Slots
      INNER JOIN Transactions t2
      ON Slots.SlotDate = t2.TransactionDate
   WHERE t2.Booked = 0 --only book it if it's currently unbooked
   AND Slots.Available > 0 --only book it if there's empty slots
   ORDER BY t2.CreatedDate)

참고: 더 간단한 개념적 변형은 다음과 같습니다.

--Give away one gift, as long as we haven't given away five
UPDATE Gifts
SET GivenAway = 1
WHERE GiftID = (
   SELECT TOP 1 GiftID
   FROM Gifts
   WHERE g2.GivenAway = 0
   AND (SELECT COUNT(*) FROM Gifts g2 WHERE g2.GivenAway = 1) < 5
   ORDER BY g2.GiftValue DESC
)

두 .UPDATE...SET...WHERE).

잘못된 거래가 "예약"되는 경우가 있습니다. 실제로는 나중의 거래를 선택하는 것입니다.16시간 동안 이것을 보고 있자니 당황스럽습니다.마치 SQL Server가 규칙을 위반하는 것과 같습니다.

어떨까 했습니다.Slots업데이트가 발생하기 전에 보기가 변경됩니까? Server를 보유하고 있지 됩니까?SHARED그 날짜에 거래가 잠겼습니까?하나의 진술이 일관성이 없을 수 있습니까?

그래서 나는 그것을 테스트하기로 결정했습니다.

하위 쿼리, 즉 내부 작업의 결과가 일관성이 없는지 확인하기로 했습니다.단일 열로 간단한 테이블을 만들었습니다.

CREATE TABLE CountingNumbers (
   Value int PRIMARY KEY NOT NULL
)

여러 연결에서 T-SQL 문 하나를 호출합니다.

INSERT INTO CountingNumbers (Value)
SELECT ISNULL(MAX(Value), 0)+1 FROM CountingNumbers

즉, 의사 코드는 다음과 같습니다.

while (true)
{
    ADOConnection.Execute(sql);
}

몇 초 안에 다음과 같은 정보를 얻을 수 있습니다.

Violation of PRIMARY KEY constraint 'PK__Counting__07D9BBC343D61337'. 
Cannot insert duplicate key in object 'dbo.CountingNumbers'. 
The duplicate value is (1332)

진술은 원자적입니까?

하나의 진술이 원자적이지 않았다는 사실은 하나의 진술이 원자적인지 궁금하게 만듭니다.

또는 SQL Server가 문으로 간주하는 것과 다른(예를 들어) 문에 대한 보다 미묘한 정의가 있습니까?

이것은 근본적으로 단일 T-SQL 문의 범위 내에서 SQL Server 문이 원자적이지 않다는 것을 의미합니까?

그리고 만약 하나의 진술이 원자적이라면, 무엇이 핵심 위반을 설명합니까?

저장 프로시저 내에서

원격 클라이언트가 n개의 연결을 여는 대신 저장 프로시저로 시도했습니다.

CREATE procedure [dbo].[DoCountNumbers] AS

SET NOCOUNT ON;

DECLARE @bumpedCount int
SET @bumpedCount = 0

WHILE (@bumpedCount < 500) --safety valve
BEGIN
SET @bumpedCount = @bumpedCount+1;

PRINT 'Running bump '+CAST(@bumpedCount AS varchar(50))

INSERT INTO CountingNumbers (Value)
SELECT ISNULL(MAX(Value), 0)+1 FROM CountingNumbers

IF (@bumpedCount >= 500)
BEGIN
    PRINT 'WARNING: Bumping safety limit of 500 bumps reached'
END
END

PRINT 'Done bumping process'

그리고 SSMS에서 5개의 탭을 열고 각각 F5를 누른 후 그 탭이 ACID를 위반하는 것을 보았습니다.

Running bump 414
Msg 2627, Level 14, State 1, Procedure DoCountNumbers, Line 14
Violation of PRIMARY KEY constraint 'PK_CountingNumbers'. 
Cannot insert duplicate key in object 'dbo.CountingNumbers'. 
The duplicate key value is (4414).
The statement has been terminated.

따라서 장애는 ADO, ADO.net 또는 위의 어느 것과도 무관합니다.

15년 동안 SQL Server의 단일 문이 일관적이라는 가정 하에 운영되어 왔습니다.

트랜잭션 분리 레벨 xxx는 어떻습니까?

실행할 SQL 배치의 다양한 변형의 경우:

• 기본값(읽기 커밋됨): 키

  INSERT INTO CountingNumbers (Value)
  SELECT ISNULL(MAX(Value), 0)+1 FROM CountingNumbers
  

• 기본값(읽기 전용), 명시적 트랜잭션: 틀림없는 키 위반

  BEGIN TRANSACTION
  INSERT INTO CountingNumbers (Value)
  SELECT ISNULL(MAX(Value), 0)+1 FROM CountingNumbers
  COMMIT TRANSACTION
  

• 직렬화 가능: 교착 상태

  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
  BEGIN TRANSACTION
  INSERT INTO CountingNumbers (Value)
  SELECT ISNULL(MAX(Value), 0)+1 FROM CountingNumbers
  COMMIT TRANSACTION
  SET TRANSACTION ISOLATION LEVEL READ COMMITTED
  

• 스냅샷(스냅샷 분리를 사용하도록 데이터베이스를 변경한 후): 키 위반

  SET TRANSACTION ISOLATION LEVEL SNAPSHOT
  BEGIN TRANSACTION
  INSERT INTO CountingNumbers (Value)
  SELECT ISNULL(MAX(Value), 0)+1 FROM CountingNumbers
  COMMIT TRANSACTION
  SET TRANSACTION ISOLATION LEVEL READ COMMITTED
  

보너스

• Microsoft SQL Server 2008 R2(SP2) - 10.50.4000.0(X64)
• 기본 트랜잭션 격리 수준(READ COMMITTED)

내가 작성한 모든 쿼리가 고장난 것으로 드러났습니다.

이것은 확실히 상황을 변화시킵니다.제가 작성한 모든 업데이트 내역서는 근본적으로 깨졌습니다.예:

--Update the user with their last invoice date
UPDATE Users 
SET LastInvoiceDate = (SELECT MAX(InvoiceDate) FROM Invoices WHERE Invoices.uid = Users.uid)

값입니다. 잘된값다 될 수 있기 입니다. 다음에 다른 송장이 삽입될 수 있기 때문입니다.MAX 리고그전 에.UPDATE예: "볼" 예:

UPDATE Sales.SalesPerson
SET SalesYTD = SalesYTD + 
    (SELECT SUM(so.SubTotal) 
     FROM Sales.SalesOrderHeader AS so
     WHERE so.OrderDate = (SELECT MAX(OrderDate)
                           FROM Sales.SalesOrderHeader AS so2
                           WHERE so2.SalesPersonID = so.SalesPersonID)
     AND Sales.SalesPerson.BusinessEntityID = so.SalesPersonID
     GROUP BY so.SalesPersonID);

, 점적인홀없이록드독,없이▁the,SalesYTD틀렸습니다.

내가 어떻게 그 동안 아무것도 할 수 있었습니까?

SQL Server의 단일 문이 일관성이 있다고 가정하여 운영해 왔습니다.

그 가정은 틀렸습니다.다음 두 트랜잭션의 잠금 의미는 동일합니다.

STATEMENT

BEGIN TRAN; STATEMENT; COMMIT

전혀 차이가 없습니다.단일 문과 자동 커밋은 아무것도 변경하지 않습니다.

따라서 모든 논리를 하나의 진술로 병합하는 것은 도움이 되지 않습니다(만약 그렇다면, 계획이 변경되었기 때문에 우연히 병합된 것입니다).

당면한 문제를 해결합시다. SERIALIZABLE트랜잭션이 단일 트랜잭션으로 실행된 것처럼 동작하도록 보장하므로 표시되는 불일치가 수정됩니다.마찬가지로, 즉시 실행한 것처럼 동작합니다.

당신은 교착 상태에 빠질 것입니다.재시도 루프에 문제가 없으면 이 시점에서 완료됩니다.

더 많은 시간을 투자하려면 잠금 힌트를 적용하여 관련 데이터에 대한 단독 액세스를 강제로 적용합니다.

UPDATE Gifts  -- U-locked anyway
SET GivenAway = 1
WHERE GiftID = (
   SELECT TOP 1 GiftID
   FROM Gifts WITH (UPDLOCK, HOLDLOCK) --this normally just S-locks.
   WHERE g2.GivenAway = 0
    AND (SELECT COUNT(*) FROM Gifts g2 WITH (UPDLOCK, HOLDLOCK) WHERE g2.GivenAway = 1) < 5
   ORDER BY g2.GiftValue DESC
)

이제 감소된 동시성이 표시됩니다.그것은 당신의 짐에 따라 완전히 괜찮을 수도 있습니다.

문제의 특성상 동시성을 달성하기가 어렵습니다.이에 대한 해결책이 필요하다면 좀 더 침습적인 기술을 적용해야 합니다.

업데이트를 약간 단순화할 수 있습니다.

WITH g AS (
   SELECT TOP 1 Gifts.*
   FROM Gifts
   WHERE g2.GivenAway = 0
    AND (SELECT COUNT(*) FROM Gifts g2 WITH (UPDLOCK, HOLDLOCK) WHERE g2.GivenAway = 1) < 5
   ORDER BY g2.GiftValue DESC
)
UPDATE g  -- U-locked anyway
SET GivenAway = 1

이렇게 하면 불필요한 조인 하나가 제거됩니다.

아래는 카운터 값을 원자적으로 증가시키는 UPDATE 문의 예입니다.

-- Do this once for test setup
CREATE TABLE CountingNumbers (Value int PRIMARY KEY NOT NULL)
INSERT INTO CountingNumbers VALUES(1) 

-- Run this in parallel: start it in two tabs on SQL Server Management Studio
-- You will see each connection generating new numbers without duplicates and without timeouts
while (1=1)
BEGIN
  declare @nextNumber int
  -- Taking the Update lock is only relevant in case this statement is part of a larger transaction
  -- to prevent deadlock
  -- When executing without a transaction, the statement will itself be atomic
  UPDATE CountingNumbers WITH (UPDLOCK, ROWLOCK) SET @nextNumber=Value=Value+1
  print @nextNumber
END

Select(선택)는 단독으로 잠기지 않으며, 직렬화 가능한 경우에도 잠기지 않습니다. 단, 선택이 실행되는 동안에만 잠깁니다!선택이 끝나면 선택 잠금이 사라집니다.그런 다음 업데이트 잠금은 Select has return results(선택에 결과가 있습니다)로 인해 잠글 대상을 알 수 있게 됩니다.다른 사용자는 다시 선택할 수 있습니다!

행을 안전하게 읽고 잠그는 유일한 방법은 다음과 같습니다.

begin transaction

--lock what i need to read
update mytable set col1=col1 where mykey=@key

--now read what i need
select @d1=col1,@d2=col2 from mytable where mykey=@key

--now do here calculations checks whatever i need from the row i read to decide my update
if @d1<@d2 set @d1=@d2 else set @d1=@d2 * 2 --just an example calc

--now do the actual update on what i read and the logic
update mytable set col1=@d1,col2=@d2 where mykey=@key

commit transaction

이렇게 하면 동일한 데이터에 대해 동일한 문을 실행하는 다른 연결은 이전이 완료될 때까지 첫 번째(가짜) 업데이트 문에서 확실히 대기합니다.이렇게 하면 잠금이 해제될 때 하나의 연결만 요청을 '업데이트'에 잠글 수 있는 권한을 부여하며, 이 연결은 두 번째 '실제' 업데이트에서 실제로 업데이트할지 여부와 업데이트할 내용을 결정하기 위해 커밋된 최종 데이터를 확실히 읽을 수 있습니다.

즉, 업데이트 여부/방법을 결정하기 위해 정보를 선택해야 하는 경우에는 시작/커밋 트랜잭션 블록이 필요하며 선택해야 하는 항목을 선택하기 전에 가짜 업데이트로 시작해야 합니다(업데이트 출력도 마찬가지입니다).

언급URL : https://stackoverflow.com/questions/21468742/is-a-single-sql-server-statement-atomic-and-consistent