[MySQL][InnoDB]잠금(Locking)과 트랜잭션 격리수준(Isolation Level)

■ 트랜잭션 격리 수준 [Isolation Level]

트랜잭션 격리는 데이터베이스 처리의 기초 중 하나입니다. 격리는 약어 ACID의 I입니다. 격리 수준은 여러 트랜잭션이 동시에 변경하고 쿼리를 수행 할 때 결과의 성능과 안정성, 일관성 및 재현성 간의 균형을 미세 조정하는 설정입니다.

 

InnoDB는 SQL:1992 표준에 의해 기술 된 READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ 및 SERIALIZABLE의 4 가지 트랜잭션 격리 수준을 모두 제공합니다. InnoDB의 기본 격리 수준은 REPEATABLE READ입니다.

 

사용자는 단일 세션 또는 SET TRANSACTION문으로 모든 후속 연결에 대한 분리 레벨을 변경할 수 있습니다. 모든 연결에 대해 서버의 기본 격리 수준을 설정하려면 명령 줄 또는 옵션 파일에서 --transaction-isolation 옵션을 사용합니다. 격리 수준 및 수준 설정 구문에 대한 자세한 내용은 13.3.6 절“SET TRANSACTION 문”을 참조하십시오.

 

InnoDB는 다른 잠금 전략을 사용하여 여기에 설명된 각 트랜잭션 격리 수준을 지원합니다. ACID 준수가 중요한 중요한 데이터에 대한 작업을 위해 기본 REPEATABLE READ수준으로 높은 수준의 일관성을 유지할 수 있습니다. 또는 정확한 일관성과 반복 가능한 결과가 잠금 오버 헤드를 최소화하는 것보다 덜 중요한 대량보고와 같은 상황에서 READ COMMITTED또는 READ UNCOMMITTED로 일관성 규칙을 완화 할 수 있습니다. SERIALIZABLE은 REPEATABLE READ보다 더 엄격한 규칙을 적용하며 주로 XA트랜잭션과 같은 특수한 상황과 동시성 및 교착 상태 문제를 해결하는 데 사용됩니다.

 

다음 목록은 MySQL이 다른 트랜잭션 레벨을 지원하는 방법을 설명합니다. 목록은 가장 일반적으로 사용되는 수준에서 가장 적게 사용 된 수준으로 이동합니다.

 

+ REPEATABLE READ

이것이 InnoDB의 기본 격리 수준입니다. 동일한 트랜잭션 내에서 일관된 읽기는 첫 번째 읽기에서 설정된 스냅샷을 읽습니다. 즉, 동일한 트랜잭션 내에서 여러 일반 (비 잠금) SELECT 문을 발행하면 이 SELECT 문도 서로 일관성이 있습니다.

 

잠금 읽기(FOR UPDATE 또는 LOCK IN SHARE MODE를 사용하여 SELECT), UPDATE 및 DELETE 문의 경우 잠금은 명령문이 고유한 검색 조건을 가진 고유 인덱스를 사용하는지 또는 범위 유형 검색 조건을 사용하는지에 따라 다릅니다.

 

- 고유한 검색 조건을 가진 고유한 인덱스의 경우 InnoDB는 검색된 인덱스 레코드만 잠그지 않고 이전의 간격을 잠급니다.

- 다른 검색 조건의 경우 InnoDB는 Gap 잠금 또는 Next-Key 잠금을 사용하여 스캔된 인덱스 범위를 잠그고 다른 세션이 해당 범위에 포함된 Gap으로의 삽입을 차단합니다.

 

+ READ COMMITTED

동일한 트랜잭션 내에서도 각각의 읽기 일관성은 자신만의 고유한 새 스냅 샷을 설정하고 읽습니다.

 

잠금 읽기 (FOR UPDATE 또는 LOCK IN SHARE MODE를 사용하여 SELECT), UPDATE 문 및 DELETE 문의 경우 InnoDB는 이전의 공백이 아닌 인덱스 레코드 만 잠그므로 잠금 레코드 옆에 새 레코드를 자유롭게 삽입 할 수 있습니다. 갭 잠금은 외래 키 제약 조건 검사 및 중복 키 검사에만 사용됩니다.

 

다른 세션에서 간격에 새 행을 삽입 할 수 있으므로 Gap 잠금이 비활성화되어 팬텀 문제가 발생할 수 있습니다.

 

READ COMMITTED 격리 수준에서는 row-level binary log 만 지원됩니다. binlog_format = MIXED와 함께 READ COMMITTED를 사용하면 서버는 자동으로 행 기반 로깅을 사용합니다.

 

READ COMMITTED를 사용하면 추가 효과가 있습니다.

- UPDATE 또는 DELETE 문의 경우 InnoDB는 업데이트하거나 삭제하는 행에 대해서만 잠금을 유지합니다. 일치하지 않는 행에 대한 레코드 잠금은 MySQL이 WHERE 조건을 평가한 후에 해제됩니다. 이는 교착 상태의 가능성을 크게 줄이지만 여전히 발생할 수 있습니다.

-UPDATE문의 경우 행이 이미 잠겨있을 때 InnoDB는 "반 일치"읽기를 수행하여 최근의 커밋된 버전을 MySQL로 반환하여 MySQL이 row가 UPDATE의 WHERE 조건과 일치하는지 확인할 수 있도록합니다. Row가 일치하면 (업데이트해야 함) MySQL은 Row을 다시 읽고 이번에는 InnoDB가 Row를 잠그거나 잠금을 기다립니다.

 

이 테이블로 시작하여 다음 예제를 평가해봅니다.

CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;

INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);

COMMIT;

이 경우 테이블에 인덱스가 없으므로 검색 및 인덱스 스캔은 인덱스 열 대신 레코드 잠금에 숨겨진 클러스터 인덱스를 사용합니다.

 

한 세션이 다음 명령문을 사용하여 UPDATE를 수행한다고 가정합니다.

# Session A

START TRANSACTION;

UPDATE t SET b = 5 WHERE b = 3;

 

또한 두 번째 세션이 첫 번째 세션의 다음 명령문을 실행하여 UPDATE를 수행한다고 가정합니다.

# Session B

InnoDB는 각 UPDATE를 실행할때 먼저 읽은 각 행에 대한 배타적 잠금을 획득한 후 수정할지 여부를 결정합니다. InnoDB가 행을 수정하지 않으면 잠금이 해제됩니다. 그렇지 않으면 InnoDB는 트랜잭션이 끝날 때까지 잠금을 유지합니다. 이는 다음과 같이 트랜잭션 처리에 영향을줍니다.

 

기본 REPEATABLE READ 격리 수준을 사용하는 경우 첫 번째 UPDATE는 각 행에서 x-lock을 획득하여 읽은 행을 해제하지 않습니다.

x-lock(1,2); retain x-lock

x-lock(2,3); update(2,3) to (2,5); retain x-lock

x-lock(3,2); retain x-lock

x-lock(4,3); update(4,3) to (4,5); retain x-lock

x-lock(5,2); retain x-lock

 

두 번째 UPDATE는 잠금을 획득하려고 시도하면 (첫 번째 업데이트가 모든 행에 대한 잠금을 유지하기 때문에) 차단하고 첫 번째 UPDATE가 커밋되거나 롤백 될 때까지 진행되지 않습니다.

x-lock (1,2); 첫 번째 UPDATE가 커밋 또는 롤백 될 때까지 차단 및 대기

 

READ COMMITTED를 대신 사용하면 첫 번째 UPDATE는 각 행에서 x-lock을 획득하고 수정하지 않은 행에 대해 x-lock을 획득합니다.

x-lock(1,2); unlock(1,2)

x-lock(2,3); update(2,3) to (2,5); retain x-lock

x-lock(3,2); unlock(3,2)

x-lock(4,3); update(4,3) to (4,5); retain x-lock

x-lock(5,2); unlock(5,2)

 

두 번째 UPDATE의 경우, InnoDB는 "반 일관성"읽기를 수행하여 MySQL에서 읽은 각 행의 최신 커밋된 버전을 반환하여 MySQL이 Row가 UPDATE의 WHERE 조건과 일치하는지 확인할 수 있도록 합니다.

x-lock(1,2); update(1,2) to (1,4); retain x-lock

x-lock(2,3); unlock(2,3)

x-lock(3,2); update(3,2) to (3,4); retain x-lock

x-lock(4,3); unlock(4,3)

x-lock(5,2); update(5,2) to (5,4); retain x-lock

 

그러나 WHERE 조건에 인덱스 열이 포함되어 있고 InnoDB가 인덱스를 사용하는 경우 레코드 잠금을 수행하고 유지할때 인덱스 열만 고려됩니다. 다음 예제에서 첫번째 UPDATE는 b=2인 각 행에서 x-lock을 가져와서 유지합니다. 두 번째 UPDATE는 b열에 정의된 인덱스도 사용하므로 동일한 레코드에서 x-lock을 획득하려고 할 때 차단됩니다.

CREATE TABLE t (a INT NOT NULL, b INT, c INT, INDEX (b)) ENGINE = InnoDB;

INSERT INTO t VALUES (1,2,3),(2,2,4);

COMMIT;

 

# Session A

START TRANSACTION;

UPDATE t SET b = 3 WHERE b = 2 AND c = 3;

 

# Session B

UPDATE t SET b = 4 WHERE b = 2 AND c = 4;

 

READ COMMITTED 격리 수준을 사용하면 다음과 같은 예외를 제외하고 더 이상 사용되지 않는 innodb_locks_unsafe_for_binlog 구성 옵션을 활성화하는 것과 같은 효과가 있습니다.

 

- innodb_locks_unsafe_for_binlog 활성화는 전역 설정이며 모든 세션에 영향을주는 반면, 격리 수준은 모든 세션에 대해 전체적으로 또는 세션별로 개별적으로 설정할 수 있습니다.

- innodb_locks_unsafe_for_binlog는 서버 시작시에만 설정할 수있는 반면 격리 수준은 시작시 설정하거나 런타임에 변경할 수 있습니다.

 

READ COMMITTED는 innodb_locks_unsafe_for_binlog보다 더 정교하고 유연한 제어 기능을 제공합니다.

 

+ READ UNCOMMITTED

SELECT 문은 비 잠금 방식으로 수행되지만 가능한 이전 버전의 행이 사용될 수 있습니다. 따라서 이 격리 수준을 사용하면 이러한 읽기가 일관되지 않습니다. 이를 더티 읽기라고도합니다. 그렇지 않으면이 격리 수준은 READ COMMITTED처럼 작동합니다.

 

+ SERIALIZABLE

이 수준은 REPEATABLE READ와 비슷하지만 자동 커밋이 비활성화 된 경우 InnoDB는 모든 일반 SELECT 문을 암시 적으로 SELECT ... LOCK IN SHARE MODE로 변환합니다. 자동 커밋이 활성화 된 경우 SELECT는 자체 트랜잭션입니다. 따라서 읽기 전용으로 알려져 있으며 일관된(비 잠금) 읽기로 수행되고 다른 트랜잭션을 차단할 필요가없는 경우 직렬화 할 수 있습니다. 다른 트랜잭션이 선택한 행을 수정 한 경우 일반 SELECT가 강제로 차단되도록하려면 자동 커밋을 비활성화합니다.

 

■ autocommit, Commit, 그리고 Rollback

InnoDB에서 모든 사용자 활동은 트랜잭션 내에서 발생합니다. auto commit 모드가 사용 가능한 경우 각 SQL 문은 자체적으로 단일 트랜잭션을 형성합니다. 기본적으로 MySQL은 자동 커밋이 활성화된 상태에서 새 연결마다 세션을 시작하므로 해당 명령문이 오류를 반환하지 않으면 MySQL은 각 SQL문 후에 커밋을 수행합니다. 명령문이 오류를 리턴하면, 커미트 또는 롤백 동작은 오류에 따라 다릅니다.

 

auto commit이 사용 가능한 세션은 명시적인 START TRANSACTION 또는 BEGIN 문으로 시작하고 COMMIT 또는 ROLLBACK 문으로 종료하여 다중 명령문 트랜잭션을 수행 할 수 있습니다.

 

SET autocommit = 0 인 세션에서 auto commit 모드를 비활성화하면 세션에 항상 트랜잭션이 열려 있습니다. COMMIT 또는 ROLLBACK 문은 현재 트랜잭션을 종료하고 새 트랜잭션을 시작합니다.

 

auto commit이 비활성화 된 세션이 최종 트랜잭션을 명시 적으로 커밋하지 않고 종료되면 MySQL은 해당 트랜잭션을 롤백합니다.

 

명령문을 실행하기 전에 COMMIT를 수행한 것처럼 일부 명령문은 트랜잭션을 암시적으로 종료합니다.

 

COMMIT는 현재 트랜잭션에서 작성된 변경 사항이 영구적이며 다른 세션에서 볼 수 있음을 의미합니다. 반면에 ROLLBACK 문은 현재 트랜잭션에 의해 작성된 모든 수정 사항을 취소합니다. COMMIT 및 ROLLBACK은 현재 트랜잭션 중에 설정된 모든 InnoDB 잠금을 해제합니다.

 

▶︎ 트랜잭션으로 DML 작업 그룹화

기본적으로 MySQL서버에 대한 연결은 자동 커밋 모드가 활성화된 상태에서 시작하여 실행시 모든 SQL 문을 자동으로 커밋합니다. 다른 데이터베이스 시스템에 대한 경험이 있는 경우 이 작동 모드는 익숙하지 않을 수 있습니다. 여기서 DML문 시퀀스를 발행하고 커미트하거나 모두 롤백하는 것이 표준 관행입니다.

 

다중 명령문 트랜잭션을 사용하려면 SQL명령으로 SET autocommit = 0으로 설정하여 자동 커미트를 끄고 각 트랜잭션을 적절하게 COMMIT 또는 ROLLBACK으로 종료합니다. 자동 커밋을 켜려면 START TRANSACTION으로 각 트랜잭션을 시작하고 COMMIT 또는 ROLLBACK으로 종료합니다. 다음 예제는 두 개의 트랜잭션을 보여줍니다. 첫 번째는 커밋되고 두 번째는 롤백됩니다.

shell> mysql test
mysql> CREATE TABLE customer (a INT, b CHAR (20), INDEX (a));
Query OK, 0 rows affected (0.00 sec)
mysql> -- Do a transaction with autocommit turned on.
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO customer VALUES (10, 'Heikki');
Query OK, 1 row affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> -- Do another transaction with autocommit turned off.
mysql> SET autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO customer VALUES (15, 'John');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO customer VALUES (20, 'Paul');
Query OK, 1 row affected (0.00 sec)
mysql> DELETE FROM customer WHERE b = 'Heikki';
Query OK, 1 row affected (0.00 sec)
mysql> -- Now we undo those last 2 inserts and the delete.
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM customer;
+------+--------+
| a    | b      |
+------+--------+
|   10 | Heikki |
+------+--------+
1 row in set (0.00 sec)
mysql>

 

# 클라이언트측 언어에서의 트랜잭션

PHP, Perl DBI, JDBC, ODBC 또는 MySQL의 표준 C 호출 인터페이스와 같은 API에서 COMMIT과 같은 트랜잭션 제어 명령문을 SELECT 또는 INSERT와 같은 다른 SQL 문과 마찬가지로 문자열로 MySQL 서버에 보낼 수 있습니다. 일부 API는 별도의 특수 트랜잭션 커밋 및 롤백 함수 또는 메소드를 제공합니다.

 

■ 일관된 비 잠금 읽기

일관된 읽기는 InnoDB가 다중 버전 관리를 사용하여 특정 시점에 데이터베이스의 스냅샷을 쿼리에 제공함을 의미합니다. 쿼리는 해당 시점 이전에 커밋된 트랜잭션의 변경 사항을 확인하며, 나중에 또는 커밋되지 않은 트랜잭션에 의한 변경 사항은 없습니다. 이 규칙의 예외는 쿼리가 동일한 트랜잭션 내에서 이전 명령문의 변경 사항을 보는 것입니다. 이 예외로 인해 다음과 같은 예외가 발생합니다. 테이블에서 일부 행을 업데이트하면 SELECT는 최신 버전의 업데이트 된 행을 볼 수 있지만 이전 버전의 행도 볼 수 있습니다. 다른 세션이 동시에 동일한 테이블을 업데이트하는 경우 예외적으로 테이블이 데이터베이스에 존재하지 않은 상태로 표시될 수 있음을 의미합니다.

 

트랜잭션 분리 레벨이 REPEATABLE READ (기본 레벨) 인 경우, 동일한 트랜잭션 내의 모든 일관된 읽기는 해당 트랜잭션에서 처음으로 읽은 스냅 샷을 읽습니다. 현재 트랜잭션을 커밋 한 후 새 쿼리를 실행하여 쿼리에 대한 새로운 스냅 샷을 얻을 수 있습니다.

 

READ COMMITTED 격리 수준을 사용하면 트랜잭션 내에서 각각의 일관된 읽기가 설정되고 자체 새 스냅샷이 읽 힙니다.

 

일관된 읽기는 InnoDB가 READ COMMITTED 및 REPEATABLE READ 격리 수준에서 SELECT문을 처리하는 기본 모드입니다. 일관된 읽기는 액세스하는 테이블에 대한 잠금을 설정하지 않으므로 다른 세션은 테이블에서 일관된 읽기가 수행되는 동시에 해당 테이블을 자유롭게 수정할 수 있습니다.

 

기본 REPEATABLE READ 격리 레벨에서 실행중이라고 가정합니다. 일관된 읽기(일반적인 SELECT 문)를 발행하면 InnoDB는 쿼리가 데이터베이스를 보는 시점을 트랜잭션에 제공합니다. 다른 트랜잭션이 행을 삭제하고 시점이 지정된 후에 커밋하면 해당 행이 삭제된 것으로 표시되지 않습니다. Insert문과 update문은 비슷하게 취급됩니다.

 

노트

데이터베이스 상태의 스냅샷은 트랜잭션 내의 SELECT문에 적용되며 DML문에는 필수적으로 적용되지는 않습니다. 일부 행을 삽입하거나 수정한 다음 해당 트랜잭션을 커밋하면 다른 동시 REPEATABLE READ 트랜잭션에서 발행된 DELETE 또는 UPDATE 문은 세션이 쿼리 할 수 ​​없어도 커밋된 행에 영향을 줄 수 있습니다. 트랜잭션이 다른 트랜잭션이 커밋한 행을 업데이트하거나 삭제하면 해당 변경 내용이 현재 트랜잭션에 표시됩니다. 예를 들어 다음과 같은 상황이 발생할 수 있습니다.

SELECT COUNT(c1) FROM t1 WHERE c1 = 'xyz';
-- Returns 0: no rows match.
DELETE FROM t1 WHERE c1 = 'xyz';
-- Deletes several rows recently committed by other transaction.
--다른 트랜잭션에서 최근 커밋 한 여러 행을 삭제합니다.

SELECT COUNT(c2) FROM t1 WHERE c2 = 'abc';
-- Returns 0: no rows match.
UPDATE t1 SET c2 = 'cba' WHERE c2 = 'abc';
-- Affects 10 rows: another txn just committed 10 rows with 'abc' values.
SELECT COUNT(c2) FROM t1 WHERE c2 = 'cba';
-- Returns 10: this txn can now see the rows it just updated.

 

 

트랜잭션을 커밋하고 일관성있는 스냅 샷으로 다른 SELECT 또는 START TRANSACTION을 수행하여 시점을 앞당길 수 있습니다.

이를 다중 버전 동시성 제어라고합니다.

다음 예에서 세션 A는 B가 삽입을 커밋하고 A도 커밋 한 경우에만 B가 삽입 한 행을 확인하므로 시점이 B 커밋을 지나서 진행됩니다.

 

             Session A              Session B

           SET autocommit=0;      SET autocommit=0;
time
|          SELECT * FROM t;
|          empty se
|                                 INSERT INTO t VALUES (1, 2);
|
v          SELECT * FROM t;
           empty set
                                  COMMIT;

           SELECT * FROM t;
           empty set

           COMMIT;

           SELECT * FROM t;
           ---------------------
           |    1    |    2    |
           ---------------------

 

데이터베이스의 최근상태를 보려면 READ COMMITTED 격리 수준 또는 잠금 읽기를 사용합니다.

SELECT * FROM t FOR SHARE;

READ COMMITTED 격리 수준을 사용하면 트랜잭션 내에서 각각의 일관된 읽기가 설정되고 자체 새 스냅샷이 읽힙니다. LOCK IN SHARE MODE를 사용하면 대신 잠금 읽기가 발생합니다. SELECT는 최신 행을 포함하는 트랜잭션이 끝날 때까지 차단합니다.

 

일관된 읽기는 특정 DDL 문에서 작동하지 않습니다.

- MySQL이 삭제된 테이블을 사용할 수 없고 InnoDB가 테이블을 삭제하므로 DROP TABLE에서 일관된 읽기가 작동하지 않습니다.

- ALTER TABLE에서 일관된 읽기가 작동하지 않습니다. 해당 명령문은 임시 테이블이 작성될 때 원래 테이블의 임시 사본을 작성하고 원래 테이블을 삭제하기 때문입니다. 트랜잭션 내에서 일관된 읽기를 다시 발행하면 트랜잭션의 스냅샷을 작성할 때 해당 행이 없었기 때문에 새 테이블의 행이 표시되지 않습니다. 이 경우 트랜잭션은 ER_TABLE_DEF_CHANGED, “Table definition has changed, please retry transaction(테이블 정의가 변경되었습니다. 트랜잭션을 다시 시도하십시오.).”라는 오류를 반환합니다.

 

읽기 유형은 FOR UPDATE 또는 LOCK IN SHARE MODE를 지정하지 않은 INSERT INTO ... SELECT, UPDATE ... (SELECT) 및 CREATE TABLE ... SELECT와 같은절에서 selects에 따라 다릅니다.

- 기본적으로 InnoDB는 더 강력한 잠금을 사용하고 SELECT파트는 READ COMMITTED와 같은 역할을합니다. 여기서 동일한 트랜잭션 내에서도 각각의 일관된 읽기가 자체 새 스냅샷을 설정하고 읽습니다.

- 이러한 경우에 일관된 읽기를 사용하려면 innodb_locks_unsafe_for_binlog 옵션을 활성화하고 트랜잭션 격리 수준을 READ UNCOMMITTED, READ COMMITTED 또는 REPEATABLE READ (즉, SERIALIZABLE 이외의 것)로 설정하십시오. 이 경우 선택한 테이블에서 읽은 행에 잠금이 설정되지 않습니다.

 

■ Locking Reads

동일한 트랜잭션 내에서 데이터를 쿼리한 다음 관련 데이터를 삽입하거나 업데이트하면 일반 SELECT문이 충분한 보호를 제공하지 않습니다. 다른 트랜잭션은 방금 쿼리한 동일한 행을 업데이트하거나 삭제할 수 있습니다. InnoDB는 추가 안전을 제공하는 두 가지 유형의 잠금 읽기를 지원합니다.

 

+ SELECT ... LOCK IN SHARE MODE

읽은 모든 행에 공유 모드 잠금을 설정합니다. 다른 세션은 행을 읽을 수 있지만 트랜잭션이 커밋 될 때까지 행을 수정할 수 없습니다. 아직 커밋되지 않은 다른 트랜잭션에 의해 이러한 행이 변경된 경우 쿼리는 해당 트랜잭션이 끝날 때까지 기다린 다음 최신 값을 사용합니다.

 

+ SELECT ... FOR UPDATE

인덱스 레코드의 경우, 검색에서 발생하는 행과 연관된 색인 항목을 잠급니다 (해당 행에 대해 UPDATE 문을 발행 한 것과 동일). 다른 트랜잭션은 해당 행을 업데이트하거나 SELECT ... LOCK IN SHARE 모드를 수행하거나 특정 트랜잭션 격리 수준의 데이터를 읽지 못하도록 차단됩니다. 일관된 읽기는 특정 레코드에 설정된 잠금을 무시합니다. (오래된 버전의 레코드는 잠글 수 없으며 레코드의 메모리 사본에 실행 취소 로그를 적용하여 재구성됩니다.)

 

SELECT ... FOR UPDATE 절은 주로 단일 테이블에서 또는 여러 테이블로 분할된 트리-구조 또는 그래프-구조 데이터를 처리 할 때 유용합니다. 한 지점에서 다른 지점으로 가장 끝자리나 트리 브랜치(특정지점의 가지치기)를 가로 지르면서 돌아와서 이러한“포인터”값을 변경할 권리를 보유합니다.

 

트랜잭션이 커밋되거나 롤백될 때 LOCK IN SHARE MODE 및 FOR UPDATE 쿼리에 의해 설정된 모든 잠금이 해제됩니다.

 

노트

잠금 읽기는 자동 커밋이 비활성화된 경우에만 가능합니다 (START TRANSACTION으로 트랜잭션을 시작하거나 자동 커밋을 0으로 설정한 경우).

 

외부 쿼리의 잠금 읽기 절은 잠금 쿼리 절이 하위 쿼리에도 지정되어 있지 않으면 중첩된 하위 쿼리의 테이블 행을 잠그지 않습니다. 예를 들어, 다음 명령문은 테이블 t2의 행을 잠그지 않습니다.

SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2) FOR UPDATE;

 

테이블 t2에서 행을 잠그려면 부속 조회에 잠금 읽기 절을 ​​추가해야 합니다.

SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2 FOR UPDATE) FOR UPDATE;

 

▶︎ 잠금 예제 읽기

테이블 행에 새로운 행을 삽입하고 테이블 행에 자식 행이 있는지 확인합니다. 응용 프로그램 코드는 이 일련의 작업에서 참조 무결성을 보장 할 수 있습니다.

 

먼저, 일관된 읽기를 사용하여 테이블 PARENT를 쿼리하고 상위 행이 존재하는지 확인합니다. 예를 들면 하위 행을 테이블 CHILD에 안전하게 삽입 할 수 있을까요? 그렇지 않습니다. 다른 세션에서 SELECT와 INSERT 사이에 부모 행을 알지 못하는 사이에 부모 행을 삭제할 수 있기 때문입니다.

 

이 잠재적인 문제를 피하려면 공유 모드에서 잠금을 사용하여 선택을 수행합니다.

SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;

 

LOCK IN SHARE MODE 조회가 상위 'Jones'를 리턴하면 하위 레코드를 CHILD테이블에 안전하게 추가하고 트랜잭션을 커미트 할 수 있습니다. PARENT 테이블의 해당 행에서 독점 잠금을 확보하려고 시도하는 트랜잭션은 완료될 때까지, 즉 모든 테이블의 데이터가 일관된 상태가 될때까지 대기합니다.

 

다른 예를 들어, 테이블 CHILD_CODES에서 정수 카운터 필드를 고려합니다. 테이블 CHILD에 추가된 각 자식에 고유 식별자를 할당하는데 사용됩니다. 데이터베이스의 두 사용자가 카운터에 대해 동일한 값을 볼 수 있고 두 트랜잭션이 CHILD 테이블과 동일한 식별자인 행을 추가하려고 하면 중복키 오류가 발생하므로 카운터의 현재 값을 읽는데 일관된 읽기 또는 공유 모드 읽기를 사용하면 안됩니다.

 

여기서는 두 명의 사용자가 동시에 카운터를 읽는 경우, 카운터 업데이트를 시도할때 둘중 하나 이상이 교착 상태에 빠지기 때문에 공유 모드 잠금은 좋은 솔루션이 아닙니다.

 

카운터 읽기 및 증가를 구현하려면 먼저 FOR UPDATE를 사용하여 카운터의 잠금 읽기를 수행 한 다음 카운터를 증가시킵니다. 예를 들면 다음과 같습니다.

SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;

SELECT ... FOR UPDATE는 사용 가능한 최신 데이터를 읽고 읽은 각 행에 독점 잠금을 설정합니다. 따라서 검색된 SQL UPDATE가 행에 설정 한 것과 동일한 잠금을 설정합니다.

 

위의 설명은 SELECT ... FOR UPDATE 작동 방식의 예일뿐입니다. MySQL에서 고유 식별자를 생성하는 특정 작업은 실제로 테이블에 대한 단일 액세스만 사용하여 수행 할 수 있습니다.

UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1);
SELECT LAST_INSERT_ID();

SELECT 문은 식별자 정보(현재 연결에만 특정하여)만 검색합니다. 어떤 테이블에 액세스하지 않습니다.