집 >데이터 베이스 >MySQL 튜토리얼 >업데이트 잠금(U) 및 배타적 잠금(X) 관련 지식을 설명합니다.

업데이트 잠금(U) 및 배타적 잠금(X) 관련 지식을 설명합니다.

jacklove원래의: 2018-06-15 09:32:595282검색

저는 UPDATE 작업의 잠금을 심각하게 이해한 적이 없습니다. 최근 MSDN 포럼에서 힙 테이블 업데이트의 교착 상태 문제에 대해 묻는 질문을 봤습니다. 테이블과 데이터는 다음과 같습니다.

CREATE TABLE dbo.tb(
     c1 int,
     c2 char(10),
     c3 varchar(10)
);
GO
DECLARE @id int;
SET @id = 0;
WHILE @id <5
BEGIN;
     SET @id = @id + 1;
     INSERT dbo.tb VALUES( @id, &#39;b&#39; + RIGHT(10000 + @id, 4), &#39;c&#39; + RIGHT(100000 + @id, 4) );
END;

쿼리 1에서 업데이트 작업을 실행합니다.

BEGIN TRAN
UPDATE dbo.tb SET c2 = &#39;xx&#39; WHERE c1 = 2;
WAITFOR DELAY &#39;00:00:30&#39;;
UPDATE dbo.tb SET c2 = &#39;xx&#39; WHERE c1 = 5;
ROLLBACK;

쿼리 1의 실행이 시작된 후 즉시 다음 작업을 실행합니다. in query two

BEGIN TRAN
UPDATE dbo.tb SET c2 = &#39;xx&#39; WHERE c1 = 1;
ROLLBACK;

왜 교착상태가 발생하나요? 조건을 c1=4로 바꾸면 교착상태가 발생하지 않습니다.

처음에는 비교적 간단하다고 생각했습니다. 교착 상태의 성능은 순환 대기를 형성하는 것입니다(두 쿼리의 경우 단순히 서로 잠긴 리소스가 해제되기를 기다리고 있다고 생각할 수 있습니다).

이 예에서는 첫 번째 쿼리가 두 번 업데이트됩니다. 먼저 레코드를 업데이트하고 잠근 다음 두 번째 업데이트를 기다리지만 두 번째 쿼리는 하나의 레코드만 업데이트합니다. 레코드를 잠그십시오. 쿼리가 충돌하여 잠금을 얻을 수 없습니다. 이 때 쿼리가 완료될 때까지 기다려야 합니다. 그렇지 않으면 잠금을 얻고 업데이트를 완료할 수 있습니다. 교착상태가 발생해서는 안되는 것 같습니다. 다른 이유로 인해 교착상태가 발생할 수 있습니까?

간단하게 컴퓨터로 테스트해봤는데 정말 교착상태는 없는 것 같습니다.

그런데 나중에 프로필을 통해 업데이트 작업의 잠금 상황을 추적한 결과 내 분석이 완전히 틀렸다는 것을 알게 되었습니다. 주된 이유는 업데이트 작업에서 잠금이 사용되는 방법을 올바르게 이해하지 못하기 때문입니다.

업데이트된 U(업데이트 잠금) 및 /msdn.microsoft.com/zh-cn/library/ms175519(v=sql.105).aspx에 대한 지침은 정말 모호합니다. S 잠금에 대해서도 언급하는데, 항상 데이터를 쿼리하는 것이라고 생각했는데, 조건에 맞는 레코드를 찾은 후, X 잠금으로 변환하는 과정에서 S 잠금이 사용됩니다. 업데이트를 위해.

Profile(프로파일러) 추적 결과에 따르면 프로필에서 새 추적을 생성하고 Lock in Locks:Acquired(잠금 추가)를 선택하세요. 잠금: Acquired(잠금 해제) 두 가지 이벤트를 해결하고, 테스트용 쿼리 창에 해당하는 spid만 추적하도록 필터를 설정합니다(PRINT @ 실행 가능) @ SPID을 얻은 다음 UPDATE dbo.tb SET c2 = '과 같은 업데이트 문을 실행합니다. xx' 어디 c1 = 3

프로필에서 각 레코드마다 U-lock 작업이 있음을 확인할 수 있습니다. 조건을 충족하지 않는 레코드의 경우 U-lock이 즉시 해제됩니다. 조건을 충족하는 레코드는 결국 X 잠금으로 변환됩니다. 아래 그림과 같습니다. ㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋ 이 추적 결과에는 S 잠금이 없습니다.

또한 몇 가지 테스트를 수행하는 방법을 배웠습니다.

업데이트 테스트를 위한 레코드 수를 늘리면 데이터 검색에 관련된 레코드에 U 잠금이 있음을 알 수 있습니다. , 업데이트 기록에만 국한되지 않습니다. 페이지가 있습니다. 이는 대형 시계에서는 스캔이 형편없다는 것을 또 다른 관점에서 보여줍니다.

인덱스 스캔을 사용할 때 스캔 중인 인덱스 리소스에 U 잠금이 있는지 추적을 통해 발견됩니다. 업데이트에 인덱스 변경이 포함되지 않은 경우 해당 레코드에만 U-X 잠금이 있습니다. . 인덱스의 U 잠금이 인덱스에 영향을 미치는 경우 잠금이 해제되고 인덱스의 U 잠금이 X 잠금으로 변환됩니다.

삭제 작업은 업데이트 작업과 유사합니다

UPDATE aSET c2 = 'xx' FROM dbo.을 사용하세요. tb AS a WITH( NOLOCK) WHERE c1 = 3 잠금 상황은 동일하며 U 또는 마지막으로 돌아가서 예제의 교착 상태 문제를 연구해 보겠습니다.

쿼리 1의 경우 첫 번째 업데이트에서는 각 레코드에 대해 U 잠금을 추가하여 업데이트 조건을 충족하는지 확인합니다. 조건이 충족되지 않으면 X 잠금입니다. 만났으면 U 잠금을 해제하세요. 첫 번째 업데이트가 완료되면 쿼리 1은 레코드를 잠그고(트랜잭션이 완료되지 않아 잠금이 유지됨) 두 번째 업데이트를 기다립니다

쿼리 2의 경우 테이블의 각 레코드를 스캔합니다. 회전(이전 업데이트와 동일), 업데이트된 레코드를 쿼리하기 전에 업데이트하는 레코드를 스캔하면 계속해서 X 잠금 레코드 쿼리의 영점으로 진행할 때 이 레코드도 X 잠금이 됩니다. 이전에 잠긴 레코드와 충돌하지만 두 번째로 잠긴 레코드를 쿼리할 때 U 잠금을 얻을 수 없습니다. 두 번째 쿼리가 리소스를 해제할 때까지 기다려야 합니다. 이때 교착상태 조건을 만족하는 상호 대기가 형성됩니다
질의 2가 업데이트해야 할 레코드가 질의 1의 첫 번째 업데이트 레코드 이후이면 질의 2가 해당 레코드를 스캔하므로 교착 상태가 발생하지 않습니다. 쿼리 첫 번째 업데이트된 레코드는 잠금 충돌로 인해 대기하게 됩니다. 이때 쿼리 1의 동작과 충돌하는 레코드에는 잠금을 설정하지 않습니다. 교착상태 없이 직접 테스트했을 때의 상황입니다.
여기서 언급한 순서는 데이터를 읽는 순서이며, 디스크에 있는 레코드의 순서가 반드시 INSERT 레코드의 순서와 동일하지는 않습니다. 같은 조건에서 테스트해보진 못했습니다. 교착상태가 발생하는 이유(제 환경에서는 읽는 순서가 INSERT 순서와 다른 경우가 발생합니다)

업데이트 시, 프로필에서 추적하는 Lock:Acquired (Lock) 이벤트를 통해 기록을 읽는 순서를 확인할 수 있습니다. 서버가 이를 지원하므로 동시 읽기도 가능합니다. 이는 교착상태를 분석할 때 고려해야 할 요소이기도 합니다

이 글에서는 업데이트 잠금(U)과 배타적 잠금(X)에 대한 관련 지식을 설명합니다. 자세한 내용은 PHP 중국어 홈페이지를 참고해주세요.

업데이트 잠금(U) 및 배타적 잠금(X) 관련 지식을 설명합니다.

관련 기사