SQL Server로 업데이트하려면 선택

SQL Server로 업데이트하려면 선택

수준이 SQL Server 2005 데이터베이스READ_COMMITTED그리고.READ_COMMITTED_SNAPSHOT=ON.

이제 다음을 사용합니다.

SELECT * FROM <tablename> FOR UPDATE

...다른 데이터베이스 연결이 동일한 "FOR UPDATE" 행에 액세스하려고 할 때 차단되도록 합니다.

노력했습니다.

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

...하지만 이렇게 하면 "1"이 아닌 ID를 선택하는 경우에도 다른 모든 연결이 차단됩니다.

다음을 수행하기 위한 올바른 힌트는 무엇입니까?SELECT FOR UPDATE오라클, DB2, MySql에 대해 알려진 바와 같습니까?

EDIT 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 = ?

EDIT 2009-10-05:

더 나은 개요를 위해 다음 표에 사용해 본 모든 솔루션을 기록했습니다.

메커니즘 | 다른 행 블록에서 선택 | 동일한 행 블록에서 선택-----------------------+--------------------------------+--------------------------ROWLOCK | 아니요 | 아니요업록, 로우록 | 예 | 예xlock, 행락 | 예 | 예반복 가능한 읽기 | 아니요 | 아니요DBCC TRACEON (1211,-1) | 예 | 예rowlock,xlock,holdlock | yes | yes업블록, 홀드록 | 예 | 예UPDLOCK, 과거 읽기 | 아니요 | 아니요
저는 | 아니 | 네

최근에 SQL Server가 필요 이상으로 잠겨 교착 상태에 빠졌습니다(페이지).당신은 그것에 반대하는 어떤 것도 할 수 없습니다.지금 우리는 교착 상태 예외를 포착하고 있습니다.대신 오라클이 있었으면 좋겠어요

편집: 동시에 스냅샷 분리 기능을 사용하여 많은 문제를 해결하고 있지만 모든 문제를 해결하는 것은 아닙니다.스냅샷 분리를 사용하려면 데이터베이스 서버에서 스냅샷 분리를 허용해야 하므로 고객 사이트에서 불필요한 문제가 발생할 수 있습니다.이제 우리는 교착 상태 예외(물론 여전히 발생할 수 있음)를 포착할 뿐만 아니라 백그라운드 프로세스에서 반복 트랜잭션(사용자가 반복할 수 없음)을 위해 동시성 문제를 스냅샷으로 처리합니다.하지만 이것은 여전히 이전보다 훨씬 더 나은 성능을 발휘합니다.

저도 비슷한 문제가 있어서 한 줄만 잠그고 싶습니다.로는 가제알는과 함께 와께함로.UPDLOCKSQLSERVER 옵션은 행을 가져오기 위해 읽어야 하는 모든 행을 잠급니다.따라서 행에 직접 액세스할 인덱스를 정의하지 않으면 이전 행이 모두 잠깁니다.예를 들어, 다음과 같습니다.

에 TBL이라는 테이블이 합니다.id을 드필로 합니다. 행을 잠글 경우id=10필드 ID(또는 선택한 다른 필드)에 대한 인덱스를 정의해야 합니다.

CREATE INDEX TBLINDEX ON TBL ( id )

그런 다음 읽은 행만 잠그는 쿼리는 다음과 같습니다.

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

않는 는 INDEX(TBLINDEX)로 .id=10행이 잠기게 됩니다.

스냅샷 분리 및 읽기 차단을 동시에 수행할 수 없습니다.스냅샷 분리의 목적은 읽기 차단을 방지하는 것입니다.

아마도 mvcc를 영구적으로 만드는 것이 이 문제를 해결할 수 있을 것입니다(특정 배치에만 해당됨: SET TRANSACTION IISOLATION LEVEL SNAPHINT).

ALTER DATABASE yourDbNameHere SET READ_COMMITTED_SNAPSHOT ON;

[편집: 10월 14일]

이 문서를 읽은 후:Oracle에서 SQL Server보다 우수한 동시성을 제공합니다. 이는 http://msdn.microsoft.com/en-us/library/ms175095.aspx 에서 확인할 수 있습니다.

READ_일 때COMMITED_SNAPSHOT 데이터베이스 옵션이 설정되어 있으면 옵션을 지원하는 데 사용되는 메커니즘이 즉시 활성화됩니다.READ 설정 시_COMMITED_SNAPSHOT 옵션은 ALTER DATABAST 명령을 실행하는 연결만 데이터베이스에서 허용됩니다.ALTER DATABASE가 완료될 때까지 데이터베이스에 열려 있는 다른 연결이 없어야 합니다.데이터베이스가 단일 사용자 모드일 필요는 없습니다.

주어진 데이터베이스에서 mssql의 MVCC를 영구적으로 활성화하려면 두 개의 플래그를 설정해야 한다는 결론에 도달했습니다.

ALTER DATABASE yourDbNameHere SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE yourDbNameHere SET READ_COMMITTED_SNAPSHOT ON;

시도(updlock, rowlock)

전체 답변은 DBMS의 내부를 조사할 수 있습니다. 이는 쿼리 엔진(SQL Optimizer에 의해 생성된 쿼리 계획을 실행)이 작동하는 방식에 따라 다릅니다.

한 한 설명 Server에 은 ID에 인덱스가 '서버'로가 있다는 것입니다.WHERE id = ?테이블을 순차적으로 스캔하고, 순차적인 스캔이 프로세스가 적용한 잠금에 도달합니다.DBMS가 기본적으로 페이지 수준 잠금을 적용하는 경우에도 문제가 발생할 수 있습니다. 한 행을 잠그면 전체 페이지와 해당 페이지의 모든 행이 잠깁니다.

이 문제를 문제의 원인으로 밝힐 수 있는 몇 가지 방법이 있습니다.쿼리 계획을 확인하고 인덱스를 검토한 후 1 대신 ID가 1000000인 SELECT를 사용하여 다른 프로세스가 여전히 차단되어 있는지 확인합니다.

한 번 선택하면 기본적으로 "Read Committed" 트랜잭션 분리가 사용되므로 해당 집합에 대한 쓰기가 중지됩니다.트랜잭션 격리 수준을 변경할 수 있습니다.

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

자세한 내용은 SQL Server BOL에서 설명합니다.

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

"추적 플래그" 1211t를 설정하여 이 에스컬레이션을 끌 수 있습니다. 자세한 내용은 BOL을 참조하십시오.

이 특정 쿼리가 실행되는 동안 다른 세션에서 행을 읽을 수 없도록 해야 합니다.

WITH(XLOCK, READ PAST) 잠금 힌트를 사용하면서 트랜잭션에서 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

가짜 업데이트를 만들어 행 잠금을 적용합니다.

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

만약 그게 당신의 싸움을 막지 않는다면, 무슨 일이 일어날지는 신이 아실 겁니다.

후에이▁"" 이후에.UPDATE당신은 당신의 것을 할 수 있습니다.SELECT (ROWLOCK)및 이후 업데이트.

질문 - 이 사례가 잠금 에스컬레이션의 결과로 입증되었습니까? (즉, 잠금 에스컬레이션 이벤트를 프로파일러로 추적할 경우, 차단이 확실히 발생합니다.)그런 경우에는 잠금 에스컬레이션을 방지하기 위해 인스턴스 수준에서 추적 플래그를 활성화하여 전체적인 설명과 (좀 더 극단적인) 해결 방법이 있습니다.http://support.microsoft.com/kb/323630 추적 플래그 1211 참조

하지만, 그것은 의도하지 않은 부작용을 일으킬 가능성이 높습니다.

행을 의도적으로 잠그고 장기간 잠그는 경우에는 트랜잭션에 내부 잠금 메커니즘을 사용하는 것이 가장 좋은 방법은 아닙니다(적어도 SQL Server에서는).SQL Server의 모든 최적화는 출입, 업데이트, 퇴장과 같은 짧은 트랜잭션에 맞춰져 있습니다.그것이 애초에 잠금 에스컬레이션의 이유입니다.

따라서 장기간 행을 "체크아웃"하려면 트랜잭션 잠금 대신 값과 일반 ol' 업데이트 문이 있는 열을 사용하여 행에 잠금 여부를 표시하는 것이 좋습니다.

응용프로그램 잠금은 "도움이 되는" 잠금 에스컬레이션을 방지하면서 사용자 지정 세분화로 자체 잠금을 롤할 수 있는 한 가지 방법입니다.sp_getapplock을 참조하십시오.

사용해 보십시오.

SELECT * FROM <tablename> WITH ROWLOCK XLOCK HOLDLOCK

이렇게 하면 잠금이 배타적으로 되고 트랜잭션 기간 동안 잠금이 유지됩니다.

이 기사에 따르면 해결책은 WITH(반복 가능 읽기) 힌트를 사용하는 것입니다.

모든 쿼리를 다시 방문하십시오. 업데이트를 위해 선택한 동일한 테이블에서 ROWLOCK/FORUPDATE 힌트 없이 선택한 쿼리가 있을 수 있습니다.

MSSQL은 종종 이러한 행 잠금을 페이지 수준 잠금으로 에스컬레이션합니다(쿼리 중인 필드에 인덱스가 없는 경우 테이블 수준 잠금도 마찬가지). 이 설명을 참조하십시오.당신이 업데이트를 요청하기 때문에, 나는 당신이 거래 수준(예: 재무, 재고 등)의 견고성이 필요하다고 추측할 수 있습니다.그래서 그 사이트의 조언은 당신의 문제에 적용되지 않습니다.MSSQL이 잠금을 확대하는 이유에 대한 통찰력일 뿐입니다.

MSSQL 2005 이상을 이미 사용하고 있다면 MVCC 기반이므로 ROWLOCK/UPDLOCK 힌트를 사용한 행 수준 잠금에 문제가 없을 것으로 생각합니다.그러나 MSSQL 2005 이상을 이미 사용하고 있는 경우, 업데이트를 위해 동일한 테이블을 쿼리하는 일부 쿼리에 색인이 있는 경우 WHERE 절의 필드를 확인하여 잠금을 에스컬레이션합니다.

추신.
저는 Postgre를 사용하고 있습니다.SQL, MVCC Have FOR UPDATE 입니다. 저는 같은 문제가 발생하지 않습니다.잠금 에스컬레이션은 MVCC가 해결하는 문제이므로, MSSQL 2005가 필드에 색인이 없는 WHERE 절로 테이블의 잠금을 여전히 에스컬레이션한다면 놀랄 것입니다.MSSQL 2005의 경우에도 이 경우(잠금 에스컬레이션)가 적용되는 경우, 색인이 있는 경우 WHERE 절의 필드를 확인해 보십시오.

고지 사항: MSSQL을 마지막으로 사용한 것은 버전 2000뿐입니다.

당신은 커밋 시간에 예외를 처리하고 거래를 반복해야 합니다.

저는 완전히 다른 방법으로 rowlock 문제를 해결했습니다.저는 sql server가 그러한 잠금을 만족스러운 방식으로 관리할 수 없다는 것을 깨달았습니다.저는 뮤텍스를 사용하여 프로그램적인 관점에서 이 문제를 해결하기로 결정했습니다...잠금 대기...잠금 해제...

READ PAST 해봤어요?

테이블을 큐처럼 처리할 때 UPDLOCK과 READ PAST를 함께 사용한 적이 있습니다.

데이터를 실제로 변경하지 않고 먼저 이 행에 대한 간단한 업데이트를 시도해 보는 것은 어떻습니까?그런 다음 업데이트를 위해 에서 선택한 것처럼 행을 계속 진행할 수 있습니다.

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

편집: 당연히 거래로 포장해야 합니다.

편집 2: 다른 솔루션은 직렬화된 분리 레벨을 사용하는 것입니다.

언급URL : https://stackoverflow.com/questions/1483725/select-for-update-with-sql-server