[SQL] SELECT 또는 INSERT는 경쟁 조건에 발생하기 쉬운 함수에서인가?

SELECT 또는 INSERT는 경쟁 조건에 발생하기 쉬운 함수에서인가?

나는 간단한 블로그 엔진에 대한 게시물을 작성하는 기능을 썼다 :

CREATE FUNCTION CreatePost(VARCHAR, TEXT, VARCHAR[])
RETURNS INTEGER AS $$
    DECLARE
        InsertedPostId INTEGER;
        TagName VARCHAR;
    BEGIN
        INSERT INTO Posts (Title, Body)
        VALUES ($1, $2)
        RETURNING Id INTO InsertedPostId;

        FOREACH TagName IN ARRAY $3 LOOP
            DECLARE
                InsertedTagId INTEGER;
            BEGIN
                -- I am concerned about this part.
                BEGIN
                    INSERT INTO Tags (Name)
                    VALUES (TagName)
                    RETURNING Id INTO InsertedTagId;
                EXCEPTION WHEN UNIQUE_VIOLATION THEN
                    SELECT INTO InsertedTagId Id
                    FROM Tags
                    WHERE Name = TagName
                    FETCH FIRST ROW ONLY;
                END;

                INSERT INTO Taggings (PostId, TagId)
                VALUES (InsertedPostId, InsertedTagId);
            END;
        END LOOP;

        RETURN InsertedPostId;
    END;
$$ LANGUAGE 'plpgsql';

이것은 동시에 할 때 여러 사용자가 삭제 태그 및 작성 게시물 경쟁 조건하는 경향이 있습니까? 특히, 거래 (및 기능) 발생에서 인종 조건 방지합니까? 나는 PostgreSQL을 9.2.3을 사용하고 있습니다.

해결법

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

   1.그것은 (INSERT 또는 UPDATE 임)에 관한 반복 가능한 동시 기록 하중 SELECT 또는 INSERT의 문제 (그러나 다른)을 UPSERT이다.

   그것은 (INSERT 또는 UPDATE 임)에 관한 반복 가능한 동시 기록 하중 SELECT 또는 INSERT의 문제 (그러나 다른)을 UPSERT이다.

   갈등 .. DO UPDATE ON ... 새로운 UPSERT 구현 INSERT를 사용하여, 우리는 크게 단순화 할 수 있습니다. INSERT에 PL / pgSQL의 기능 또는 하나의 행 (태그)를 선택 :

   CREATE OR REPLACE FUNCTION f_tag_id(_tag text, OUT _tag_id int) AS
   $func$
   BEGIN
      SELECT tag_id  -- only if row existed before
      FROM   tag
      WHERE  tag = _tag
      INTO   _tag_id;
   
      IF NOT FOUND THEN
         INSERT INTO tag AS t (tag)
         VALUES (_tag)
         ON     CONFLICT (tag) DO NOTHING
         RETURNING t.tag_id
         INTO   _tag_id;
      END IF;
   END
   $func$ LANGUAGE plpgsql;
   

   경쟁 조건에 대한 작은 창은 여전히있다. 절대적으로하려면 확실히 당신은 ID를 얻을 :

   CREATE OR REPLACE FUNCTION f_tag_id(_tag text, OUT _tag_id int) AS
   $func$
   BEGIN
   LOOP
      SELECT tag_id
      FROM   tag
      WHERE  tag = _tag
      INTO   _tag_id;
   
      EXIT WHEN FOUND;
   
      INSERT INTO tag AS t (tag)
      VALUES (_tag)
      ON     CONFLICT (tag) DO NOTHING
      RETURNING t.tag_id
      INTO   _tag_id;
   
      EXIT WHEN FOUND;
   END LOOP;
   END
   $func$ LANGUAGE plpgsql;
   

   이것은 INSERT 또는 SELECT 중 하나가 성공할 때까지 반복 유지합니다. 요구:

   SELECT f_tag_id('possibly_new_tag');
   

   동일한 트랜잭션의 후속 명령이 행의 존재에 의존하고 다른 트랜잭션의 업데이트가 동시에 삭제하거나하는 것이 실제로 가능하다면, 당신은 공유에 대한과 SELECT 문에 기존 행을 잠글 수 있습니다. 행 대신 삽입됩니다 경우, 어쨌든 트랜잭션이 끝날 때까지 잠겨 있습니다.

   새로운 행이 대부분의 시간을 삽입하면 빨리를 만들기 위해 INSERT로 시작합니다.

   관련 :

   INSERT에 관련 (순수 SQL) 솔루션 또는 한 번에 여러 행 (세트)을 선택합니다 :

   나는 이전에이 SQL 함수를 제안했다 :

   CREATE OR REPLACE FUNCTION f_tag_id(_tag text, OUT _tag_id int) AS
   $func$
      WITH ins AS (
         INSERT INTO tag AS t (tag)
         VALUES (_tag)
         ON     CONFLICT (tag) DO NOTHING
         RETURNING t.tag_id
         )
      SELECT tag_id FROM ins
      UNION  ALL
      SELECT tag_id FROM tag WHERE tag = _tag
      LIMIT  1
   $func$ LANGUAGE sql;
   

   어떤 전적으로 잘못이지만, @FunctorSalad 그의 추가 답변에서 일처럼 허점을 밀봉하는 데 실패합니다. 동시 트랜잭션이 동시에 동일한 작업을 수행하려고하면이 함수는 빈 결과와 함께 올 수 있습니다. CTE를 사용하여 쿼리의 모든 문을 거의 동시에 실행됩니다. 수동 :

   동시 트랜잭션이 순간 이전 같은 새 태그를 삽입, 아직 커밋되지 않은 경우 :

   우리는 아무것도 얻을 수 없다. 로 적당하지 않습니다. 즉, (그리고 내가 거기에 잡힌) 순진한 논리에 반 직관적하지만 포스트 그레스의 MVCC 모델이 어떻게 작동하는지 그의는 - 일에 있습니다.

   여러 트랜잭션이 동시에 같은 태그를 삽입 할 수있는 경우에 따라서이를 사용하지 마십시오. 또는 루프 실제로 행을 얻을 때까지. 루프는 좀처럼 일반적인 작업 부하에 트리거되지 않습니다.

   이 (약간 단순화) 테이블을 감안할 때 :

   CREATE table tag (
     tag_id serial PRIMARY KEY
   , tag    text   UNIQUE
   );
   

   ... 실질적으로 100 % 보안 기능은 다음과 같을 수, 기존 하나를 선택 / 새 태그를 삽입합니다. 왜 100 %? 관련 UPSERT 예에 대한 설명서의주의 사항을 고려 :

   CREATE OR REPLACE FUNCTION f_tag_id(_tag text, OUT tag_id int) AS
   $func$
   BEGIN
   
   LOOP
      BEGIN
   
      WITH sel AS (SELECT t.tag_id FROM tag t WHERE t.tag = _tag FOR SHARE)
         , ins AS (INSERT INTO tag(tag)
                   SELECT _tag
                   WHERE  NOT EXISTS (SELECT 1 FROM sel)  -- only if not found
                   RETURNING tag.tag_id)  -- qualified so no conflict with param
      SELECT sel.tag_id FROM sel
      UNION  ALL
      SELECT ins.tag_id FROM ins
      INTO   tag_id;
   
      EXCEPTION WHEN UNIQUE_VIOLATION THEN     -- insert in concurrent session?
         RAISE NOTICE 'It actually happened!'; -- hardly ever happens
      END;
   
      EXIT WHEN tag_id IS NOT NULL;            -- else keep looping
   END LOOP;
   
   END
   $func$ LANGUAGE plpgsql;
   

   SQL 바이올린.

   당신이 대부분으로 Foreach 루프를 단순화 할 수있는이 기능을 사용 :

   ...
   FOREACH TagName IN ARRAY $3
   LOOP
      INSERT INTO taggings (PostId, TagId)
      VALUES   (InsertedPostId, f_tag_id(TagName));
   END LOOP;
   ...
   

   빠른하지만) (unnest 단일 SQL 문으로 :

   INSERT INTO taggings (PostId, TagId)
   SELECT InsertedPostId, f_tag_id(tag)
   FROM   unnest($3) tag;
   

   전체 루프를 대체합니다.

   이 변종은 LIMIT 절을 UNION ALL의 동작을 기반으로 : 충분한 행이 발견하는 즉시로, 나머지는 실행되지 않습니다 :

   이 바탕, 우리는 별도의 기능에 INSERT를 아웃소싱 할 수 있습니다. 만 우리는 예외 처리가 필요합니다. 그냥 첫 번째 해결 방법으로 안전뿐만.

   CREATE OR REPLACE FUNCTION f_insert_tag(_tag text, OUT tag_id int)
     RETURNS int AS
   $func$
   BEGIN
   INSERT INTO tag(tag) VALUES (_tag) RETURNING tag.tag_id INTO tag_id;
   
   EXCEPTION WHEN UNIQUE_VIOLATION THEN  -- catch exception, NULL is returned
   END
   $func$ LANGUAGE plpgsql;
   

   주요 기능에 사용되는 :

   CREATE OR REPLACE FUNCTION f_tag_id(_tag text, OUT _tag_id int) AS
   $func$
   BEGIN
      LOOP
         SELECT tag_id FROM tag WHERE tag = _tag
         UNION  ALL
         SELECT f_insert_tag(_tag)  -- only executed if tag not found
         LIMIT  1  -- not strictly necessary, just to be clear
         INTO   _tag_id;
   
         EXIT WHEN _tag_id IS NOT NULL;  -- else keep looping
      END LOOP;
   END
   $func$ LANGUAGE plpgsql;
   

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

   2.포스트 그레스 9.5에 소개 된 ON 충돌 절을 사용하는 경우에도 조심하는 것이 여전히있다. 우리가 할 경우, @Erwin Brandstetter 응답에서와 동일한 기능과 예를 들어 테이블을 사용 :

   포스트 그레스 9.5에 소개 된 ON 충돌 절을 사용하는 경우에도 조심하는 것이 여전히있다. 우리가 할 경우, @Erwin Brandstetter 응답에서와 동일한 기능과 예를 들어 테이블을 사용 :

   Session 1: begin;
   
   Session 2: begin;
   
   Session 1: select f_tag_id('a');
    f_tag_id 
   ----------
          11
   (1 row)
   
   Session 2: select f_tag_id('a');
   [Session 2 blocks]
   
   Session 1: commit;
   
   [Session 2 returns:]
    f_tag_id 
   ----------
           NULL
   (1 row)
   

   그래서 단일 스레드 세계에서 불가능한 것이다, 세션 2에서 반환 NULL을 f_tag_id!

   우리는 반복 읽기 (또는 강한 직렬화)에 대한 트랜잭션 격리 수준을 올릴 경우, 세션 2는 오류가 발생합니다 : 직렬화 액세스 할 수 없었다 인해 동시 갱신을 대신. 적어도 아니오 "불가능"결과 그래서,하지만 불행히도 우리는 이제 트랜잭션을 다시 시도 할 준비를해야합니다.

   편집 : 세션 1 삽입 태그 A는, 다음 세션이 삽입 B, 다음 세션 1 시도가를 삽입이 개 시도를 B와 세션을 삽입 할 경우 반복 읽기 또는 직렬화으로는, 하나 개의 세션은 교착 상태를 감지 :

   ERROR:  deadlock detected
   DETAIL:  Process 14377 waits for ShareLock on transaction 1795501; blocked by process 14363.
   Process 14363 waits for ShareLock on transaction 1795503; blocked by process 14377.
   HINT:  See server log for query details.
   CONTEXT:  while inserting index tuple (0,3) in relation "tag"
   SQL function "f_tag_id" statement 1
   

   다시 교착 상태 오류 롤을받은 세션 후, 다른 세션이 계속됩니다. 나는 우리가이 같은 상황에서, 단지 serialization_failure 및 재시도 같은 교착 상태 처리해야 추측 그래서?

   또한, 일관된 순서로 태그를 삽입하지만, 그들은 모두 한 곳에서 추가되지 않는 경우이 쉽지 않다.

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

   3.나는 태그가 이미 존재 때 트랜잭션이 그것을 발견 한 후 다른 트랜잭션에 의해 삭제 될 수있는 약간의 기회가있다 생각합니다. 는 SELECT FOR 업데이트를 사용하여 그 해결해야한다.

   나는 태그가 이미 존재 때 트랜잭션이 그것을 발견 한 후 다른 트랜잭션에 의해 삭제 될 수있는 약간의 기회가있다 생각합니다. 는 SELECT FOR 업데이트를 사용하여 그 해결해야한다.

from https://stackoverflow.com/questions/15939902/is-select-or-insert-in-a-function-prone-to-race-conditions by cc-by-sa and MIT license