MySQL에서 최적의 잠금 기능

MySQL에서 최적의 잠금 기능

MySQL에서 낙관적인 잠금에 대한 자세한 내용을 찾을 수 없습니다.동기화된 두 엔티티에서 트랜잭션 계속 업데이트를 시작해도 두 사용자가 동시에 데이터를 업데이트하는 것을 막을 수 없습니다.

낙관적인 잠금으로 이 문제를 해결할 수 있을까요?MySQL에서는 어떻게 적용됩니까?이에 대한 SQL 구문/키워드가 있습니까?아니면 MySQL에 기본 동작이 있나요?

여러분 감사합니다.

요점은 Optimistic Locking은 데이터베이스 기능이 아니며 MySQL이나 기타 기능이 아니라는 것입니다.Optimistic Locking은 DB를 표준 명령과 함께 사용하는 방법입니다.

예를 들어 여러 사용자/클라이언트를 동시에 실행할 수 있는 코드로 이 작업을 수행하는 경우를 가정해 보겠습니다.

1. 1개의 ID 필드(iD)와 2개의 데이터 필드(val1, val2)가 있는 행에서 데이터를 선택합니다.
2. 옵션으로 데이터를 사용하여 계산을 수행합니다.
3. 해당 행의 데이터 업데이트

잠금 해제 방법은 다음과 같습니다.

참고: 모든 코드 {between colly brackets}은(필요한) SQL 쪽이 아닌 앱 코드에 있어야 합니다.

- SELECT iD, val1, val2
       FROM theTable
       WHERE iD = @theId;
 - {code that calculates new values}
 - UPDATE theTable
       SET val1 = @newVal1,
           val2 = @newVal2
       WHERE iD = @theId;
 - {go on with your other code}

OPTIMISTIC LOCKING 방식은 다음과 같습니다.

- SELECT iD, val1, val2
       FROM theTable
       WHERE iD = @theId;
 - {code that calculates new values}
 - UPDATE theTable
       SET val1 = @newVal1,
           val2 = @newVal2
       WHERE iD = @theId
           AND val1 = @oldVal1
           AND val2 = @oldVal2;
 - {if AffectedRows == 1 }
 -     {go on with your other code}
 - {else}
 -     {decide what to do since it has gone bad... in your code}
 - {endif}

키 포인트는 UPDATE 명령 구조 및 이후 영향을 받는 행 수 체크에 있습니다.SELECT와 UPDATE를 실행한 사이에 다른 사용자가 이미 데이터를 수정한 것을 코드로 인식할 수 있습니다.모든 작업이 트랜잭션 없이 수행되었습니다.이것은 매우 단순한 예이기 때문에(거래의 부재) 가능했지만, 이것은 또한 낙관적인 잠금의 핵심은 거래 자체에 있지 않다는 것을 나타냅니다.

그러면 트랜잭션에 대해서는 어떻게 생각하십니까?

 - SELECT iD, val1, val2
       FROM theTable
       WHERE iD = @theId;
 - {code that calculates new values}
 - BEGIN TRANSACTION;
 - UPDATE anotherTable
       SET col1 = @newCol1,
           col2 = @newCol2
       WHERE iD = @theId;
 - UPDATE theTable
       SET val1 = @newVal1,
           val2 = @newVal2
       WHERE iD = @theId
           AND val1 = @oldVal1
           AND val2 = @oldVal2;
 - {if AffectedRows == 1 }
 -     COMMIT TRANSACTION;
 -     {go on with your other code}
 - {else}
 -     ROLLBACK TRANSACTION;
 -     {decide what to do since it has gone bad... in your code}
 - {endif}

이 마지막 예에서는 어떤 시점에서 충돌을 체크하고 다른 테이블/행을 이미 수정했을 때 충돌이 발생한 것을 발견하면 트랜잭션을 사용하여 처음부터 수행한 모든 변경을 롤백할 수 있음을 보여 줍니다.각 충돌에 대해 롤백할 작업의 양을 결정하고 트랜잭션 경계를 어디에 둘지, 특수 UPDATE + Affected Rows 체크를 사용하여 충돌을 확인할지를 결정하는 것은 물론(어플리케이션이 무엇을 하고 있는지 아는) 사용자에게 달려 있습니다.

이 경우 트랜잭션에서는 UPDATE를 실행하는 시점과 커밋되는 시점이 구분되어 있습니다.이 기간 내에 "다른 프로세스"가 업데이트를 수행하면 어떻게 됩니까?정확히 무슨 일이 일어나는지 알기 위해서는 격리 레벨의 상세(및 각 엔진에서의 관리방법)를 조사해야 합니다.예를 들어 READ_를 사용하는 Microsoft SQL Server의 경우업데이트된 행은 COMMIT 때까지 잠겨 있기 때문에 "기타 프로세스"는 해당 행에서 아무것도 할 수 없습니다(대기 상태로 유지됨).SELECT(실제로 읽기만 가능)도 할 수 없습니다.인정).따라서 "기타 프로세스" 작업이 지연되므로 업데이트는 실패합니다.

Versioning Optimistic Locking 옵션:

 - SELECT iD, val1, val2, version
       FROM theTable
       WHERE iD = @theId;
 - {code that calculates new values}
 - UPDATE theTable
       SET val1 = @newVal1,
           val2 = @newVal2,
           version = version + 1
       WHERE iD = @theId
           AND version = @oldversion;
 - {if AffectedRows == 1 }
 -     {go on with your other code}
 - {else}
 -     {decide what to do since it has gone bad... in your code}
 - {endif}

여기에서는 값이 모든 필드에 동일한지 확인하는 대신 전용 필드(UPDATE를 실행할 때마다 변경됨)를 사용하여 자신보다 빠른 사용자가 있는지 확인하고 SELECT와 UPDATE 사이의 행을 변경할 수 있음을 보여 줍니다.여기서 트랜잭션이 없는 것은 첫 번째 예시와 같이 단순하기 때문이며 버전 열 사용과는 관련이 없습니다.이 컬럼의 사용은 어플리케이션코드 구현에 따라 다르며 데이터베이스 엔진 기능이 아닙니다.

이 밖에도 이 답변이 너무 길다고 생각되는 요점이 있기 때문에(이미 너무 길다고 생각되므로) 저는 몇 가지 참고 자료로만 언급하고 있습니다.

• 트랜잭션 격리 수준(MySQL의 경우)을 참조하십시오.
• 프라이머리 키가 자동 생성되지 않은(또는 고유한 제약조건) 테이블 위의 INSERT는 두 프로세스가 일치해야 하는 위치에 동일한 값을 삽입하려고 하면 특별한 체크가 필요 없이 자동으로 실패합니다.
• id 열(프라이머리 키 또는 고유 제약 조건)이 없는 경우 단일 SELECT + UPDATE에 트랜잭션이 필요합니다.이는 다른 사용자가 변경한 후 UPDATE의 WHERE 절 기준에 일치하는 행이 예상보다 많다는 사실에 놀라움을 느낄 수 있기 때문입니다.

실습을 체크하고 자신감을 얻는 방법

격리 레벨의 값과 구현은 다를 수 있으므로 (이 사이트에서 통상적으로) 사용하는 플랫폼/환경에서 테스트를 수행하는 것이 가장 좋습니다.

어려워 보일 수 있지만 실제로는 두 개의 별도 창을 사용하여 각 창에서 트랜잭션을 시작하고 명령을 하나씩 실행하는 모든 DB 개발 환경에서 매우 쉽게 수행할 수 있습니다.

어느 시점에서 명령어 실행이 무기한으로 계속됨을 알 수 있습니다.그런 다음 다른 창에서 COMMIT 또는 ROLLBLK이라고 하면 실행이 완료됩니다.

다음은 앞에서 설명한 대로 테스트할 수 있는 매우 기본적인 명령어입니다.

테이블과 1개의 유용한 행을 작성하려면 다음 명령을 사용합니다.

CREATE TABLE theTable(
    iD int NOT NULL,
    val1 int NOT NULL,
    val2 int NOT NULL
);
INSERT INTO theTable (iD, val1, val2) VALUES (1, 2 ,3);

그런 다음 두 개의 다른 창에서 단계별로 다음을 수행합니다.

BEGIN TRAN

SELECT val1, val2 FROM theTable WHERE iD = 1;

UPDATE theTable
  SET val1=11
  WHERE iD = 1 AND val1 = 2 AND val2 = 3;

COMMIT TRAN

그런 다음 생각할 수 있는 순서대로 명령어 순서와 실행 순서를 변경합니다.

언급URL : https://stackoverflow.com/questions/17431338/optimistic-locking-in-mysql