[SQL] 어떻게 데이터베이스의 동시 업데이트를 처리하는?

어떻게 데이터베이스의 동시 업데이트를 처리하는?

SQL 데이터베이스에 동시 업데이트를 처리 할 수있는 일반적인 방법은 무엇입니까?

간단한의 SQL 스키마 (제약 및 기본값이 표시되지 ..) 등의 고려

create table credits (
  int id,
  int creds,
  int user_id
);

목적은, 예를 들어, 사용자에 대한 크레딧의 어떤 종류를 저장하는 것입니다 유래의 명성과 같은.

어떻게 그 테이블에 대한 동시 업데이트를 처리하는? 몇 가지 옵션 :

그래서 단순히 DB 오류가 발생하면 어떤 위에 설명 된 (매우 간단) 문제를 처리 할 수있는 승인 된 방법은 무엇인가?

해결법

1. ==============================

   1.사용 거래 :

   사용 거래 :

   BEGIN WORK;
   SELECT creds FROM credits WHERE userid = 1;
   -- do your work
   UPDATE credits SET creds = 150 WHERE userid = 1;
   COMMIT;
   

   몇 가지 중요한 참고 사항 :

   SQL 저장 프로 시저와의 거래를 결합하여 다룰 후반 쉽게 만들 수 있습니다; 응용 프로그램은 트랜잭션에 하나의 저장 프로 시저를 호출 할 것이며, 거래 중단 및 경우 다시는-호출합니다.

2. ==============================

   2.MySQL의 InnoDB 테이블의 경우,이 정말 당신이 설정 한 분리 레벨에 따라 달라집니다.

   MySQL의 InnoDB 테이블의 경우,이 정말 당신이 설정 한 분리 레벨에 따라 달라집니다.

   기본 레벨 3 (REPEATABLE READ)를 사용하는 경우, 당신은 당신이 트랜잭션에있는 경우에도, 연속 쓰기에 영향을주는 행을 잠글 필요가있다. 귀하의 예제에서 당신은해야합니다 :

   SELECT FOR UPDATE creds FROM credits WHERE userid = 1;
   -- calculate --
   UPDATE credits SET creds = 150 WHERE userid = 1;
   

   당신은 레벨 4 (SERIALIZABLE)를 사용하는 경우, 다음 SELECT는 업데이트로 다음 간단한 충분하다. 이노에서 레벨 4는 읽기 잠금 읽고하는 모든 행을에 의해 구현됩니다.

   SELECT creds FROM credits WHERE userid = 1;
   -- calculate --
   UPDATE credits SET creds = 150 WHERE userid = 1;
   

   그러나 이러한 특정 예에서, 계산 이후 (크레딧을 추가하는) SQL 간단한에서 수행되는 간단한 충분하다 :

   UPDATE credits set creds = creds - 150 where userid=1;
   

   는 SELECT FOR UPDATE UPDATE는 다음에 해당 될 것이다.

3. ==============================

   3.에 관계없이 어떤 경우에는 충분한 사용자가 정의한 격리 수준이 아니다 트랜잭션 내부의 코드를 포장 (예를 들어, 영상 프로덕션에 2 개 개의 다른 서버에 코드를 배포 한).

   에 관계없이 어떤 경우에는 충분한 사용자가 정의한 격리 수준이 아니다 트랜잭션 내부의 코드를 포장 (예를 들어, 영상 프로덕션에 2 개 개의 다른 서버에 코드를 배포 한).

   의는이 단계 및 2 개 동시 스레드 있다고 가정 해 봅시다 :

   1) open a transaction
   2) fetch the data (SELECT creds FROM credits WHERE userid = 1;)
   3) do your work (credits + amount)
   4) update the data (UPDATE credits SET creds = ? WHERE userid = 1;)
   5) commit
   

   그리고이 타임 라인 :

   Time =  0; creds = 100
   Time =  1; ThreadA executes (1) and creates Txn1
   Time =  2; ThreadB executes (1) and creates Txn2
   Time =  3; ThreadA executes (2) and fetches 100
   Time =  4; ThreadB executes (2) and fetches 100
   Time =  5; ThreadA executes (3) and adds 100 + 50
   Time =  6; ThreadB executes (3) and adds 100 + 50
   Time =  7; ThreadA executes (4) and updates creds to 150
   Time =  8; ThreadB tries to executes (4) but in the best scenario the transaction
             (depending of isolation level) won't allow it and you get an error
   

   트랜잭션 방지 당신은 잘못된 값으로 creds 값을 대체 할 수 있지만, 내가 어떤 오류를 실패하지 않기 때문에 그것은 충분하지 않습니다.

   내가 대신 실패하지 않을 것이라는 느린 과정을 선호 나는 순간 내가 데이터를 가져올 나는 그것으로 끝났어요 때까지 방지 다른 스레드가 같은 행을 읽을 수 (2 단계)에서 "데이터베이스 행 잠금"으로 문제를 해결했다.

   이 SQL 서버에서 할 수있는 몇 가지 방법이 있으며이 중 하나입니다 :

   SELECT creds FROM credits WITH (UPDLOCK) WHERE userid = 1;
   

   나는이 개선 이전의 타임 라인을 다시하는 경우는 다음과 같이 얻을 :

   Time =  0; creds = 100
   Time =  1; ThreadA executes (1) and creates Txn1
   Time =  2; ThreadB executes (1) and creates Txn2
   Time =  3; ThreadA executes (2) with lock and fetches 100
   Time =  4; ThreadB tries executes (2) but the row is locked and 
                      it's has to wait...
   
   Time =  5; ThreadA executes (3) and adds 100 + 50
   Time =  6; ThreadA executes (4) and updates creds to 150
   Time =  7; ThreadA executes (5) and commits the Txn1
   
   Time =  8; ThreadB was waiting up to this point and now is able to execute (2) 
                      with lock and fetches 150
   Time =  9; ThreadB executes (3) and adds 150 + 50
   Time = 10; ThreadB executes (4) and updates creds to 200
   Time = 11; ThreadB executes (5) and commits the Txn2
   

4. ==============================

   4.이 동시성 문제를 해결할 수있는 새로운 타임 스탬프 열을 사용하여 낙관적 잠금.

   이 동시성 문제를 해결할 수있는 새로운 타임 스탬프 열을 사용하여 낙관적 잠금.

   UPDATE credits SET creds = 150 WHERE userid = 1 and modified_data = old_modified_date
   

5. ==============================

   5.첫 번째 시나리오의 경우 당신은 확인하기 위해 어디 절에 다른 조건을 추가 할 수 있습니다 당신은 아닌 동시 사용자에 의해 덮어 쓰기가 변경됩니다. 예를 들면

   첫 번째 시나리오의 경우 당신은 확인하기 위해 어디 절에 다른 조건을 추가 할 수 있습니다 당신은 아닌 동시 사용자에 의해 덮어 쓰기가 변경됩니다. 예를 들면

   update credits set creds= 150 where userid = 1 AND creds = 0;
   

6. ==============================

   6.당신은 순위 유형 값에서에 추가 또는 뺄셈 일부 작업에 의해 주기적으로 LIFO 처리를 위해 대기하고 얻을 것이다 큐잉 메커니즘을 설정할 수 있습니다. 순위의 "균형"에 대한 실시간 정보가 필요한 경우이 뛰어난 큐 항목이 조정 될 때까지 균형을 계산하지 않기 때문에 맞지 않을 것이지만, 그것의 어떤 경우가 될 수있는 즉각적인 조정이 필요하지 않습니다.

   당신은 순위 유형 값에서에 추가 또는 뺄셈 일부 작업에 의해 주기적으로 LIFO 처리를 위해 대기하고 얻을 것이다 큐잉 메커니즘을 설정할 수 있습니다. 순위의 "균형"에 대한 실시간 정보가 필요한 경우이 뛰어난 큐 항목이 조정 될 때까지 균형을 계산하지 않기 때문에 맞지 않을 것이지만, 그것의 어떤 경우가 될 수있는 즉각적인 조정이 필요하지 않습니다.

   이는 기존의 기갑 일반 시리즈와 같은 게임은 개인의 이동을 처리하는 방법에보고 외부에 적어도 반영하는 것으로 보인다. 한 플레이어의 턴이 등장하고, 그들은 그들의 움직임을 선언합니다. 차례로 각 이동 순서대로 처리하고, 각 이동 큐에서의 위치가 있기 때문에 충돌이 없다.

7. ==============================

   7.당신이 현재 신용 필드가 요청 된 양만큼 user`s을 줄이고 성공적으로 감소하는 경우 다른 작업과 문제가 예를 들어 사용자가있을 때 감소 작업을위한 많은 병렬 요청이있을 수 이론에 할 귀하의 경우 하나 개의 중요한 포인트가된다 요청이 동시에 정확히 전송 될 경우 균형에 5 개 병렬 1 개 학점 충전 요청 1 학점 그는 5 가지 사항을 구입할 수 있으며 함께 -4 user`s 균형 크레딧 끝.

   당신이 현재 신용 필드가 요청 된 양만큼 user`s을 줄이고 성공적으로 감소하는 경우 다른 작업과 문제가 예를 들어 사용자가있을 때 감소 작업을위한 많은 병렬 요청이있을 수 이론에 할 귀하의 경우 하나 개의 중요한 포인트가된다 요청이 동시에 정확히 전송 될 경우 균형에 5 개 병렬 1 개 학점 충전 요청 1 학점 그는 5 가지 사항을 구입할 수 있으며 함께 -4 user`s 균형 크레딧 끝.

   당신이 (우리의 예를 1 학점으로) 요청 금액과 현재 크레딧 값을 감소도에 확인해야합니다이를 방지하기 위해 위치를 현재 값을 뺀 요청 금액이 더 나 제로인 경우 :

   UPDATE 크레딧 SET의 creds = creds-1 WHERE creds-1> = 0, 사용자 ID = 1

   그는 시스템을 도스 경우이 의지 보증 사용자는 몇 학점에서 많은 것들을 구입하지 않을 것입니다.

   이 쿼리 후에는 현재 사용자의 신용 기준을 충족하고 행이 갱신 된 경우 알려줍니다 ROW_COUNT ()를 실행해야합니다 :

   UPDATE credits SET creds = creds-1 WHERE creds-1>=0 and userid = 1
   IF (ROW_COUNT()>0) THEN 
      --IF WE ARE HERE MEANS USER HAD SURELY ENOUGH CREDITS TO PURCHASE THINGS    
   END IF;
   

   PHP는 비슷한 일을 같이 할 수 있습니다 :

   mysqli_query ("UPDATE credits SET creds = creds-$amount WHERE creds-$amount>=0 and userid = $user");
   if (mysqli_affected_rows())
   {
      \\do good things here
   }
   

   여기에 우리가 사용하거나 SELECT ... UPDATE도 거래하지만 당신은 트랜잭션 내에서이 코드를 넣으면 바로 트랜잭션 수준은 항상 (이미 저지른 사람 다른 트랜잭션 포함) 행에서 가장 최근의 데이터를 제공합니다 있는지 확인하십시오. ROW_COUNT는 () = 0 경우에도 사용자 ROLLBACK을 할 수

   행 잠금없이 신용 - $ 금액> = 0의 단점은 다음과 같습니다 :

   갱신 후에는 반드시 사용자가 자신이 많은 요청과 함께 요 해킹 크레딧을 시도하는 경우에도 신용 잔고에 충분한 양을 한 것으로 한 가지를 알고 있지만 충전 (갱신) 및 충전 (갱신) 후 무엇 이었습니까 신용 전에 신용이 무엇 당신 같은 다른 일을 잘 모릅니다.

   주의:

   가장 최근의 행 데이터를 제공하지 않습니다이 전략의 내부 거래 수준을 사용하지 마십시오.

   당신이 전 업데이트 후 값을 무엇인지 알고 싶다면이 전략을 사용하지 마십시오.

   다만 신용이 성공적으로 영하하지 않고 충전하고 그 사실에 의존하려고합니다.

8. ==============================

   8.표는 다음과 같이 수정 낙관적 잠금을 처리하기 위해 새로운 필드 버전을 소개 할 수 있습니다. 이 데이터베이스 수준에서 잠금을 사용하는 것보다 오히려 더 나은 성능을 달성하기 위해보다 비용 효과적이고 효율적인 방법입니다 (테이블 크레딧을 만들   INT ID,   INT creds,   INT USER_ID,   INT 버전 );

   표는 다음과 같이 수정 낙관적 잠금을 처리하기 위해 새로운 필드 버전을 소개 할 수 있습니다. 이 데이터베이스 수준에서 잠금을 사용하는 것보다 오히려 더 나은 성능을 달성하기 위해보다 비용 효과적이고 효율적인 방법입니다 (테이블 크레딧을 만들   INT ID,   INT creds,   INT USER_ID,   INT 버전 );

   선택 creds 266 학점에서 버전 USER_ID = 1;

   이 수익률을 가정 creds = 100 = 버전 1

   업데이트 크레딧 설정 creds = creds * 10 = 버전 버전 + 1 여기서 USER_ID = 1 버전 = 1;

   항상 최신 버전 번호를 가진 누구든지 할 수있는 경우에만이 기록을 갱신하고 더러운 쓰기가 허용되지 않도록

9. ==============================

   9.당신이 레코드와 마지막 업데이트 타임 스탬프를 저장하는 경우이 값을 읽을 때, 타임 스탬프를 읽을뿐만 아니라. 당신이 레코드를 업데이트하기 위해 갈 때, 반드시 타임 스탬프의 일치를 확인하십시오. 누군가가 뒤에 와서 전에 업데이트 한 경우, 타임 스탬프는 일치하지 않습니다.

   당신이 레코드와 마지막 업데이트 타임 스탬프를 저장하는 경우이 값을 읽을 때, 타임 스탬프를 읽을뿐만 아니라. 당신이 레코드를 업데이트하기 위해 갈 때, 반드시 타임 스탬프의 일치를 확인하십시오. 누군가가 뒤에 와서 전에 업데이트 한 경우, 타임 스탬프는 일치하지 않습니다.

from https://stackoverflow.com/questions/1195858/how-to-deal-with-concurrent-updates-in-databases by cc-by-sa and MIT license