복붙노트

[SQL] SQL 서버와 SELECT FOR UPDATE

SQL

SQL 서버와 SELECT FOR UPDATE

나는 격리 수준 READ_COMMITTED 및 READ_COMMITTED_SNAPSHOT = ON와 마이크로 소프트 SQL 서버 2005 데이터베이스를 사용하고 있습니다.

지금은 사용하려면 :

SELECT * FROM <tablename> FOR UPDATE

... "업데이트"같은 행에 액세스하려고 할 때 다른 데이터베이스 연결을 차단 그래서.

나는 시도했다 :

SELECT * FROM <tablename> WITH (updlock) WHERE id=1

심지어 "1"이 아닌 다른 ID를 선택하는 ...하지만,이 블록 다른 모든 연결.

오라클, DB2, MySQL은 알려진 바와 같이 SELECT FOR UPDATE를 할 수있는 올바른 힌트는 무엇입니까?

편집 2009-10-03 :

다음은 테이블과 인덱스를 만들 수있는 문장입니다 :

CREATE TABLE example ( Id BIGINT NOT NULL, TransactionId BIGINT, 
    Terminal BIGINT, Status SMALLINT );
ALTER TABLE example ADD CONSTRAINT index108 PRIMARY KEY ( Id )
CREATE INDEX I108_FkTerminal ON example ( Terminal )
CREATE INDEX I108_Key ON example ( TransactionId )

병렬 프로세스의 많은이 SELECT를 수행합니다

SELECT * FROM example o WITH (updlock) WHERE o.TransactionId = ?

편집 2009-10-05 :

나는 다음과 같은 테이블의 모든 노력 솔루션 아래로 작성한 개요 더 나은 경우 :

mechanism              | SELECT on different row blocks | SELECT on same row blocks
-----------------------+--------------------------------+--------------------------
ROWLOCK                | no                             | no
updlock, rowlock       | yes                            | yes
xlock,rowlock          | yes                            | yes
repeatableread         | no                             | no
DBCC TRACEON (1211,-1) | yes                            | yes
rowlock,xlock,holdlock | yes                            | yes
updlock,holdlock       | yes                            | yes
UPDLOCK,READPAST       | no                             | no

I'm looking for        | no                             | yes

해결법

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

    1.SQL Server가 더 후 필요한 (페이지)를 고정하기 때문에 최근에는 교착 상태에 문제가 있었다. 당신은 정말 대해 아무것도 할 수 없습니다. 이제 우리는 교착 상태 예외를 잡기 있습니다 ... 그리고 내가 대신 오라클이 있었으면 좋겠다.

    SQL Server가 더 후 필요한 (페이지)를 고정하기 때문에 최근에는 교착 상태에 문제가 있었다. 당신은 정말 대해 아무것도 할 수 없습니다. 이제 우리는 교착 상태 예외를 잡기 있습니다 ... 그리고 내가 대신 오라클이 있었으면 좋겠다.

    편집하다: 우리는하지만 모든 문제의 많은 해결 스냅 숏 격리 한편을 사용하고 있습니다. 불행하게도,이 고객 사이트에서 불필요한 문제가 발생할 수 있습니다 데이터베이스 서버에 의해 허용되어야 스냅 샷 격리를 사용할 수 있습니다. 이제 우리는 (물론 여전히 발생할 수 있습니다) 교착 상태 예외뿐만 아니라 (사용자가 반복 될 수 없습니다) 백그라운드 프로세스에서 반복 거래 스냅 샷 동시성 문제를 잡기되지 않습니다. 그러나이 여전히 수행하는 훨씬 더 이전보다.

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

    2.나는 단지 1 행을 잠글 비슷한 문제가 있습니다. 지금까지 내가 아는 한, UPDLOCK 옵션, SQLSERVER은 행을 얻기 위해 읽을 필요로하는 모든 행을 잠급니다. 당신이 행에 직접 액세스에 대한 인덱스를 정의하지 않는 경우에 따라서, 모든 선행 행이 잠 깁니다. 당신의 예에서 :

    나는 단지 1 행을 잠글 비슷한 문제가 있습니다. 지금까지 내가 아는 한, UPDLOCK 옵션, SQLSERVER은 행을 얻기 위해 읽을 필요로하는 모든 행을 잠급니다. 당신이 행에 직접 액세스에 대한 인덱스를 정의하지 않는 경우에 따라서, 모든 선행 행이 잠 깁니다. 당신의 예에서 :

    당신이 id 필드와 테이블라는 이름의 테이블이 있다고 가정합니다. 당신은 ID = 10 행을 잠급니다. 당신은 id 필드에 대한 인덱스 (또는에 관여하는 다른 필드 선택)를 정의해야합니다 :

    CREATE INDEX TBLINDEX ON TBL ( id )

    그리고, 쿼리는 당신이 읽을 만 행을 잠급니다 :

    SELECT * FROM TBL WITH (UPDLOCK, INDEX(TBLINDEX)) WHERE id=10.

    당신이 INDEX (TBLINDEX) 옵션을 사용하지 않으면, SQLSERVER 그 행이 고정 될 수 있도록, 식 (10)와 함께 행을 찾기 위해 테이블의 시작 부분에서 모든 행을 읽을 필요가있다.

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

    3.당신은 스냅 숏 격리를 가질 수 없습니다 및 차단이 동시에 읽습니다. 스냅 숏 격리의 목적은 차단 읽기를 방지하는 것입니다.

    당신은 스냅 숏 격리를 가질 수 없습니다 및 차단이 동시에 읽습니다. 스냅 숏 격리의 목적은 차단 읽기를 방지하는 것입니다.

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

    4.시도 (UPDLOCK, 노 걸이)

    시도 (UPDLOCK, 노 걸이)

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

    5.전체 대답은 DBMS의 내부 탐구 할 수있다. 은 (는 SQL 옵티 마이저에 의해 생성 된 쿼리 계획을 실행) 쿼리 엔진이 어떻게 작동하는지에 따라 달라집니다.

    전체 대답은 DBMS의 내부 탐구 할 수있다. 은 (는 SQL 옵티 마이저에 의해 생성 된 쿼리 계획을 실행) 쿼리 엔진이 어떻게 작동하는지에 따라 달라집니다.

    그러나 (일부 DBMS의 적어도 일부 버전에 적용 - 반드시 MS SQL Server에 대한) 하나의 가능한 설명은 모든 프로세스가? 'WHERE ID ='와 쿼리를 해결하려고 노력 있도록 ID 컬럼에 인덱스가 존재하지 않는다는 것이다 이 테이블의 순차 검색을하고 끝, 그 순차 검색 안타 프로세스가 적용되는 잠금에. DBMS에 기본적으로 페이지 수준 잠금을 적용하는 경우에도 문제가 실행할 수 있습니다; 하나 개의 행을 고정하면 전체 페이지 및 해당 페이지의 모든 행을 잠급니다.

    당신은 문제의 소스로이 정체를 폭로 수있는 몇 가지 방법이 있습니다. 쿼리 계획 봐; 인덱스를 연구; 1000000 대신에 하나의 ID로 SELECT를 시도하고 다른 프로세스가 여전히 차단되어 있는지 여부를 확인.

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

    6.아마도 (: SET의 TRANSACTION 격리 수준의 스냅 특정 배치 반대) 영구 MVCC 그것을 해결할 수 만들기 :

    아마도 (: SET의 TRANSACTION 격리 수준의 스냅 특정 배치 반대) 영구 MVCC 그것을 해결할 수 만들기 :

    ALTER DATABASE yourDbNameHere SET READ_COMMITTED_SNAPSHOT ON;

    [EDIT : 2008 년 10 월 14]

    SQL 서버보다 오라클에서 더 나은 동시성이를 읽고 나면? 이 : http://msdn.microsoft.com/en-us/library/ms175095.aspx

    난 당신이 주어진 데이터베이스에 영구적으로 활성화 MSSQL의 MVCC를 위해이 플래그를 설정해야한다는 결론에 도달했습니다 :

    ALTER DATABASE yourDbNameHere SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE yourDbNameHere SET READ_COMMITTED_SNAPSHOT ON;
  7. ==============================

    7.OK, 기본 사용에 의해 하나의 선택 줘야 그러므로 그 세트에 쓰기를 중지 잠금 및 트랜잭션 격리를 "읽기 커밋". 당신과 트랜잭션 격리 수준을 변경할 수 있습니다

    OK, 기본 사용에 의해 하나의 선택 줘야 그러므로 그 세트에 쓰기를 중지 잠금 및 트랜잭션 격리를 "읽기 커밋". 당신과 트랜잭션 격리 수준을 변경할 수 있습니다

    Set Transaction Isolation Level { Read Uncommitted | Read Committed | Repeatable Read | Serializable }
Begin Tran
  Select ...
Commit Tran

    이들은 SQL 서버 BOL에 자세히 설명되어 있습니다

    다음 문제는 이상 ~ 2,500 잠금이있는 경우 기본적으로 SQL 서버 2K5가 잠금을 확대거나 잠금 트랜잭션에 '정상'메모리의 40 % 이상을 사용하는 것입니다. 에스컬레이션 페이지로 이동, 다음 테이블 잠금

    당신은 "추적 플래그"1,211t을 설정하여이 에스컬레이션을 전환 할 수 있습니다, 자세한 내용은 BOL을 참조

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

    8.노 걸이를 시행 가짜 업데이트를 만듭니다.

    노 걸이를 시행 가짜 업데이트를 만듭니다.

    UPDATE <tablename> (ROWLOCK) SET <somecolumn> = <somecolumn> WHERE id=1

    즉 당신의 행 잠금 있지 않다면, 하나님은 어떤 것 알고있다.

    이 "UPDATE"후 당신은 당신의 SELECT (ROWLOCK) 이후 업데이트를 할 수 있습니다.

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

    9.난 당신이 다른 세션이 특정 쿼리가 실행되는 동안 행을 읽을 수 싶지 않아 있으리라 믿고있어 ...

    난 당신이 다른 세션이 특정 쿼리가 실행되는 동안 행을 읽을 수 싶지 않아 있으리라 믿고있어 ...

    당신이 원하는 결과를 얻을 것이다 (XLOCK, READPAST) 잠금 힌트와 함께 사용하는 동안 트랜잭션에서 SELECT 포장. 그냥 확실히 그 다른 동시가 WITH (NOLOCK)를 사용하지 않는 읽고 확인하십시오. READPAST는 다른 세션이 동일한 SELECT를 수행하지만 다른 행에 할 수 있습니다.

    BEGIN TRAN
  SELECT *
  FROM <tablename> WITH (XLOCK,READPAST) 
  WHERE RowId = @SomeId

  -- Do SOMETHING

  UPDATE <tablename>
  SET <column>=@somevalue
  WHERE RowId=@SomeId
COMMIT
  10. ==============================

    10.질문 -이 경우는 잠금 에스컬레이션의 결과로 입증 (당신이 잠금 에스컬레이션 이벤트에 대한 프로파일 러 추적 경우 즉, 확실히 무엇을 차단 원인이 일어나고 있다는 것입니다)? 그렇다면, 자세한 설명 및 잠금 에스컬레이션을 방지하기 위해 인스턴스 수준에서 추적 플래그를 활성화하여 (오히려 극단적 인) 해결 방법이 있습니다. 보기 http://support.microsoft.com/kb/323630 추적 플래그 1211

    질문 -이 경우는 잠금 에스컬레이션의 결과로 입증 (당신이 잠금 에스컬레이션 이벤트에 대한 프로파일 러 추적 경우 즉, 확실히 무엇을 차단 원인이 일어나고 있다는 것입니다)? 그렇다면, 자세한 설명 및 잠금 에스컬레이션을 방지하기 위해 인스턴스 수준에서 추적 플래그를 활성화하여 (오히려 극단적 인) 해결 방법이 있습니다. 보기 http://support.microsoft.com/kb/323630 추적 플래그 1211

    그러나, 그 가능성이 의도하지 않은 부작용이있을 것입니다.

    당신이 의도적으로 행을 고정하고 유지하는 경우 그 다음 거래 (적어도 SQL Server의) 가장 좋은 방법은 아닙니다 내부 잠금 메커니즘을 사용하여, 오랜 기간 동안 고정. SQL 서버의 모든 최적화는 짧은 거래를 위해 개발된다 -에서 얻을 업데이 트를 만들어 나가. 즉, 처음에 잠금 에스컬레이션에 대한 이유입니다.

    의도가 있다면 그래서이 잠겨 여부로 플래그 행을 값으로 열 및 일반 팔자 '업데이트 문을 사용하는 것이 가장 좋습니다 대신 트랜잭션 잠금, 장기간 행을 "확인".

  11. ==============================

    11.응용 프로그램 잠금은 "도움"잠금 에스컬레이션을 피하면서 사용자 정의 단위로 자신의 잠금을 출시하는 한 방법입니다. sp_getapplock를 참조하십시오.

    응용 프로그램 잠금은 "도움"잠금 에스컬레이션을 피하면서 사용자 정의 단위로 자신의 잠금을 출시하는 한 방법입니다. sp_getapplock를 참조하십시오.

  12. ==============================

    12.사용해보십시오 :

    사용해보십시오 :

    SELECT * FROM <tablename> WITH ROWLOCK XLOCK HOLDLOCK

    이 잠금 독점을하고 트랜잭션 (transaction)의 기간 동안 유지해야한다.

  13. ==============================

    13.이 문서에 따르면, 용액은 WITH (REPEATABLEREAD) 힌트를 사용하는 것이다.

    이 문서에 따르면, 용액은 WITH (REPEATABLEREAD) 힌트를 사용하는 것이다.

  14. ==============================

    14.모든 쿼리를 다시 방문, 어쩌면 당신은 당신이 SELECT FOR UPDATE가 같은 테이블에서 UPDATE의 힌트 / ROWLOCK없이 선택 일부 쿼리가 있습니다.

    모든 쿼리를 다시 방문, 어쩌면 당신은 당신이 SELECT FOR UPDATE가 같은 테이블에서 UPDATE의 힌트 / ROWLOCK없이 선택 일부 쿼리가 있습니다.

    (당신은 당신이 쿼리하는 필드에 인덱스가없는 경우에도 테이블 수준 잠금)이 설명을 참조 MSSQL은 종종 페이지 수준 잠금에 그 행 잠금을 에스컬레이션합니다. 이 업데이트를 요구하기 때문에, 난 당신이 transacion 수준 (예를 들면. 금융, 재고 등) 견고성을 필요로한다고 가정 할 수있다. 그래서 사이트의 조언은 당신의 문제에 적용 할 수 없습니다. 그것은 MSSQL은 잠금을 고조 이유는 바로 통찰력입니다.

    이미 그들은 MVCC 기반이다 (위) MSSQL 2005를 사용하는 경우, 난 당신이 ROWLOCK / UPDLOCK 힌트를 사용하여 행 수준 잠금 아무런 문제가 없을한다고 생각합니다. 이미 MSSQL 2005를 사용하고 최대 경우 그들이 색인이있는 경우 자신의 WHERE 절에 필드를 확인하여 잠금을 확대한다면, 당신은 업데이트하려는 동일한 테이블을 쿼리 쿼리의 일부를 확인하려고합니다.

    추신 내가 PostgreSQL을 사용하고, 또한 MVCC는 UPDATE위한 한 사용, 난 같은 문제가 발생하지 않습니다. MSSQL 2005 여전히 조항이 필드에 대한 인덱스를하지 않는 WHERE 테이블에 잠금을 확대하면 내가 놀랄 것 때문에 잠금 에스컬레이션은 무엇 MVCC 해결할 수있는 문제입니다. 그 (잠금 에스컬레이션)이 여전히 MSSQL 2005의 경우 경우, 인덱스가있는 경우 WHERE 절에있는 필드를 확인하려고합니다.

    면책 조항 : MSSQL의 마지막 사용 버전 2000 전용입니다.

  15. ==============================

    15.당신은 시간을 커밋에서 예외를 처리해야하고 트랜잭션을 반복합니다.

    당신은 시간을 커밋에서 예외를 처리해야하고 트랜잭션을 반복합니다.

  16. ==============================

    16.나는 완전히 다른 방식으로 노 걸이의 문제를 해결했다. 나는 그 SQL 서버가 만족하는 방법으로 그러한 잠금을 관리 할 수 ​​없습니다 깨달았다. 나는 뮤텍스 ... waitForLock ... releaseLock를 사용하여보기의 프로그램 적 관점에서이 문제를 해결하기 위해 choosed 할 ...

    나는 완전히 다른 방식으로 노 걸이의 문제를 해결했다. 나는 그 SQL 서버가 만족하는 방법으로 그러한 잠금을 관리 할 수 ​​없습니다 깨달았다. 나는 뮤텍스 ... waitForLock ... releaseLock를 사용하여보기의 프로그램 적 관점에서이 문제를 해결하기 위해 choosed 할 ...

  17. ==============================

    17.당신은 READPAST를 시도?

    당신은 READPAST를 시도?

    큐 같은 테이블을 치료 때 함께 UPDLOCK 및 READPAST를 사용했습니다.

  18. ==============================

    18.방법 (정말 모든 데이터를 변경하지 않고) 먼저이 행에 대한 간단한 갱신을하려고 어떻습니까? 당신이 같은 행 진행할 수 후 업데이트를 위해 선정되었다.

    방법 (정말 모든 데이터를 변경하지 않고) 먼저이 행에 대한 간단한 갱신을하려고 어떻습니까? 당신이 같은 행 진행할 수 후 업데이트를 위해 선정되었다.

    UPDATE dbo.Customer SET FieldForLock = FieldForLock WHERE CustomerID = @CustomerID
/* do whatever you want */

    편집 : 당신은 물론 트랜잭션에 포장한다

    편집 2 : 또 다른 해결책은 SERIALIZABLE 격리 수준을 사용하는 것입니다

    19. from https://stackoverflow.com/questions/1483725/select-for-update-with-sql-server by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 하나 개의 SQL 쿼리에서 모든 상위 행을 얻기  (0) 2020.04.18
[SQL] PL / pgSQL의와 PostgreSQL을의 기록으로 여러 필드를 반환  (0) 2020.04.18
[SQL] T-SQL : MAX에 열을 기반으로 선택 (기타 열)  (0) 2020.04.18
[SQL] SQL - 쿼리 서버의 IP 주소를 얻을 수 있습니다  (0) 2020.04.18
[SQL] 삽입, 업데이트 SQL Server에서 proc 디렉토리에 저장  (0) 2020.04.18
,

티스토리툴바