SQL Server로 업데이트 선택

SQL Server로 업데이트 선택

---

격리 수준 READ_COMMITTED과 .NET Framework를 사용하는 Microsoft SQL Server 2005 데이터베이스를 사용 하고 READ_COMMITTED_SNAPSHOT=ON있습니다.

이제 다음을 사용하고 싶습니다.

SELECT * FROM <tablename> FOR UPDATE

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

나는 시도했다 :

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

... 그러나 이것은 "1"이 아닌 다른 ID를 선택하더라도 다른 모든 연결을 차단합니다.

SELECT FOR UPDATEOracle, DB2, MySql에 대한 올바른 힌트는 무엇입니까?

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 수정 :

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

메커니즘 | 다른 행 블록에서 SELECT | 동일한 행 블록에서 SELECT
----------------------- + -------------------------- ------ + --------------------------
ROWLOCK | 아니오 | 아니
updlock, rowlock | 예 | 예
xlock, rowlock | 예 | 예
repeatableread | 아니오 | 아니
DBCC TRACEON (1211, -1) | 예 | 예
rowlock, xlock, holdlock | 예 | 예
updlock, holdlock | 예 | 예
UPDLOCK, READPAST | 아니오 | 아니

나는 찾고 있어요 | 아니오 | 예

---

최근에 Sql Server가 필요한 것보다 더 많이 잠기기 때문에 교착 상태 문제 가 발생했습니다 (페이지). 당신은 그것에 대해 아무것도 할 수 없습니다. 이제 교착 상태 예외를 포착하고 있습니다. 대신 Oracle이 있으면 좋겠습니다.

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

---

비슷한 문제가 있습니다. 1 행만 잠그고 싶습니다. 내가 아는 한, UPDLOCK옵션을 사용하면 SQLSERVER는 행을 가져 오기 위해 읽어야하는 모든 행을 잠급니다. 따라서 행에 대한 직접 액세스를위한 인덱스를 정의하지 않으면 선행하는 모든 행이 잠 깁니다. 귀하의 예에서 :

id필드 가있는 TBL이라는 테이블이 있다고 가정하십시오 . 로 행을 잠그고 싶습니다 id=10. 필드 ID (또는 선택한 관련 필드)에 대한 색인을 정의해야합니다.

CREATE INDEX TBLINDEX ON TBL ( id )

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

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

INDEX (TBLINDEX) 옵션을 사용하지 않는 경우 SQLSERVER는를 사용하는 행을 찾기 위해 테이블의 시작 부분부터 모든 행을 읽어야 id=10하므로 해당 행이 잠 깁니다.

---

스냅 샷 격리와 읽기 차단을 동시에 가질 수 없습니다. 스냅 샷 격리의 목적은 읽기 차단 을 방지 하는 것입니다.

---

시도 (updlock, rowlock)

---

완전한 대답은 DBMS의 내부를 파헤칠 수 있습니다. 쿼리 엔진 (SQL 최적화 프로그램에서 생성 된 쿼리 계획을 실행)이 작동하는 방식에 따라 다릅니다.

그러나 가능한 설명 중 하나 (MS SQL Server에 반드시 필요한 것은 아니지만 일부 DBMS의 일부 버전에 적용 가능)는 ID 열에 인덱스가 없으므로 ' WHERE id = ?'가 포함 된 쿼리를 처리하려는 모든 프로세스 가 테이블의 순차 스캔 및 해당 순차 스캔은 프로세스가 적용한 잠금에 도달합니다. DBMS가 기본적으로 페이지 수준 잠금을 적용하는 경우에도 문제가 발생할 수 있습니다. 한 행을 잠그면 전체 페이지와 해당 페이지의 모든 행이 잠 깁니다.

이것을 문제의 원인으로 폭로 할 수있는 몇 가지 방법이 있습니다. 쿼리 계획을보십시오. 색인을 연구하십시오. 1이 아닌 1000000의 ID로 SELECT를 시도하고 다른 프로세스가 여전히 차단되는지 확인하십시오.

---

mvcc를 영구적으로 만들면 문제를 해결할 수 있습니다 (특정 배치에만 해당 : SET TRANSACTION ISOLATION LEVEL SNAPSHOT).

ALTER DATABASE yourDbNameHere SET READ_COMMITTED_SNAPSHOT ON;

[편집 : 10 월 14 일]

이 글을 읽은 후 : SQL Server보다 Oracle에서 더 나은 동시성? 그리고 이것 : http://msdn.microsoft.com/en-us/library/ms175095.aspx

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

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

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

---

좋습니다. 단일 선택은 기본적으로 "커밋 된 읽기"트랜잭션 격리를 사용하여 해당 집합에 대한 쓰기를 잠그고 중지합니다. 다음을 사용하여 트랜잭션 격리 수준을 변경할 수 있습니다.

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을 참조하십시오.

---

행 잠금을 적용하는 가짜 업데이트를 만듭니다.

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

그것이 당신의 행을 잠그지 않는다면, 신은 무엇을 할 것인지 알고 있습니다.

이 " UPDATE" 후에 귀하 SELECT (ROWLOCK)와 후속 업데이트를 수행 할 수 있습니다.

---

I'm assuming you don't want any other session to be able to read the row while this specific query is running...

Wrapping your SELECT in a transaction while using WITH (XLOCK,READPAST) locking hint will get the results you want. Just make sure those other concurrent reads are NOT using WITH (NOLOCK). READPAST allows other sessions to perform the same SELECT but on other rows.

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

  -- Do SOMETHING

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

---

Application locks are one way to roll your own locking with custom granularity while avoiding "helpful" lock escalation. See sp_getapplock.

---

Try using:

SELECT * FROM <tablename> WITH ROWLOCK XLOCK HOLDLOCK

This should make the lock exclusive and hold it for the duration of the transaction.

---

According to this article, the solution is to use the WITH(REPEATABLEREAD) hint.

---

Revisit all your queries, maybe you have some query that select without ROWLOCK/FOR UPDATE hint from the same table you have SELECT FOR UPDATE.

MSSQL often escalates those row locks to page-level locks (even table-level locks, if you don't have index on field you are querying), see this explanation. Since you ask for FOR UPDATE, i could assume that you need transacion-level(e.g. financial, inventory, etc) robustness. So the advice on that site is not applicable to your problem. It's just an insight why MSSQL escalates locks.

If you are already using MSSQL 2005(and up), they are MVCC-based, i think you should have no problem with row-level lock using ROWLOCK/UPDLOCK hint. But if you are already using MSSQL 2005 and up, try to check some of your queries which query the same table you want to FOR UPDATE if they escalate locks by checking the fields on their WHERE clause if they have index.

P.S.
I'm using PostgreSQL, it also uses MVCC have FOR UPDATE, i don't encounter same problem. Lock escalations is what MVCC solves, so i would be surprised if MSSQL 2005 still escalate locks on table with WHERE clauses that doesn't have index on its fields. If that(lock escalation) is still the case for MSSQL 2005, try to check the fields on WHERE clauses if they have index.

Disclaimer: my last use of MSSQL is version 2000 only.

---

You have to deal with the exception at commit time and repeat the transaction.

---

Question - is this case proven to be the result of lock escalation (i.e. if you trace with profiler for lock escalation events, is that definitely what is happening to cause the blocking)? If so, there is a full explanation and a (rather extreme) workaround by enabling a trace flag at the instance level to prevent lock escalation. See http://support.microsoft.com/kb/323630 trace flag 1211

But, that will likely have unintended side effects.

If you are deliberately locking a row and keeping it locked for an extended period, then using the internal locking mechanism for transactions isn't the best method (in SQL Server at least). All the optimization in SQL Server is geared toward short transactions - get in, make an update, get out. That's the reason for lock escalation in the first place.

So if the intent is to "check out" a row for a prolonged period, instead of transactional locking it's best to use a column with values and a plain ol' update statement to flag the rows as locked or not.

---

I solved the rowlock problem in a completely different way. I realized that sql server was not able to manage such a lock in a satisfying way. I choosed to solve this from a programatically point of view by the use of a mutex... waitForLock... releaseLock...

---

Have you tried READPAST?

I've used UPDLOCK and READPAST together when treating a table like a queue.

---

How about trying to do a simple update on this row first (without really changing any data)? After that you can proceed with the row like in was selected for update.

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

Edit: you should wrap it in a transaction of course

Edit 2: another solution is to use SERIALIZABLE isolation level

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