MySQL 트랜잭션 격리 수준 예제 튜토리얼

이 글의 실험을 위한 테스트 환경: Windows 10+cmd+MySQL5.6.36+InnoDB

1. 트랜잭션의 기본 요소(ACID)

　 1. 원자성: 트랜잭션이 시작된 후 모든 작업이 완료되거나 완료되지 않으며 중간에 정체될 수 없습니다. 트랜잭션 실행 중 오류가 발생하면 트랜잭션이 시작되기 전 상태로 롤백되어 모든 작업이 발생하지 않은 것처럼 처리됩니다. 즉, 물질의 기본 단위인 화학에서 배운 원자와 마찬가지로 사물은 분할할 수 없는 전체입니다. 2. 일관성(Consistency): 트랜잭션 시작 및 종료 전후에 데이터베이스의 무결성 제약 조건을 위반하지 않습니다. 예를 들어, A가 B에게 돈을 이체하면 A는 돈을 공제할 수 없지만 B는 돈을 받지 못합니다. 3. 격리: 동시에 하나의 트랜잭션만 동일한 데이터를 요청할 수 있으며 서로 다른 트랜잭션 간에 간섭이 없습니다. 예를 들어, A는 은행 카드에서 돈을 인출하고 있습니다. B는 A의 인출 절차가 완료되기 전에는 이 카드로 돈을 이체할 수 없습니다.

　 4. 내구성: 트랜잭션이 완료된 후 트랜잭션으로 인해 데이터베이스에 대한 모든 업데이트가 데이터베이스에 저장되며 롤백할 수 없습니다.
요약: 원자성은 트랜잭션 격리의 기본이고 격리와 내구성은 수단이며 궁극적인 목표는 데이터 일관성을 유지하는 것입니다.

2. 트랜잭션 동시성 문제

1. 더티 읽기: 트랜잭션 A가 트랜잭션 B에 의해 업데이트된 데이터를 읽은 다음 B가 작업을 롤백한 다음 A가 읽습니다. 수신된 데이터는 더티 데이터

　　2. 반복 불가능 읽기: 트랜잭션 A는 동일한 데이터를 여러 번 읽고, 트랜잭션 B는 트랜잭션 A를 여러 번 읽는 동안 데이터를 업데이트합니다. 트랜잭션 A가 동일한 데이터를 여러 번 읽을 때 발생합니다. 3. 팬텀 리딩(Phantom reading): 시스템 관리자 A가 데이터베이스 내 모든 학생의 성적을 특정 점수에서 ABCDE 등급으로 변경했는데, 시스템 관리자 B가 이때 특정 점수에 대한 기록을 삽입했는데, 시스템 관리자 A가 변경을 완료하고 오류가 있는 것을 발견했다. 여전히 변경되지 않은 기록이 하나 있는데, 이를 환각이라고 합니다.

요약: 비반복 읽기와 팬텀 읽기는 수정에 초점을 맞추고 팬텀 읽기는 추가 또는 삭제

에 중점을 둡니다. 반복 불가능한 읽기 문제를 해결하려면 조건에 맞는 행만 잠그면 됩니다. 팬텀 읽기 문제를 해결하려면 테이블만 잠그면 됩니다.

트랜잭션 격리 수준	더티 읽기	반복 불가능한 읽기	팬텀 읽기
읽기 커밋되지 않음	은	예	예
반복 불가능한 읽기(읽기 커밋)	No	예	예
반복 가능 -읽어 아니요	아니요	MySQL의 기본 트랜잭션 격리 수준은 반복 읽기입니다 IV. 예를 사용하여 각 격리 수준을 설명하세요 　1. 제출되지 않은 읽기 : ㅋㅋㅋ　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 내가 달러로 계산한 이후로- 그래서 너무 너무 너무 너무 너무 너무 너무 너무 너무 너무 너무 너무 너무 너무 너무 너무 너무 너무 너무 너무 너무 너무 너무 너무 너무 너무 너무너무너무너무너무너무너무너무너무너무너무너무너무너무너무너무너무너무너무너무너무너무너무너무너무너무너무너무너무너무너무 제출하기 전에 다른 클라이언트 B를 열고 테이블 계정을 업데이트하세요. (3) 이때 클라이언트 B의 트랜잭션이 아직 제출되지 않았더라도 클라이언트 A는 B의 업데이트된 데이터를 쿼리할 수 있습니다.　　　　　　　　　　　　　　　　　　　　　　　　　　　로그인 | 5) 업데이트 내역 실행 계정 설정 잔액 업데이트 = 잔액 - 50 클라이언트 A. lilei의 id = 1이 350이 아닌 실제로 400이 되었습니다. 이상하지 않나요? 데이터의 일관성을 묻지 않았습니까? 애플리케이션에서 이렇게 생각하는 것은 너무 순진합니다. 400-50=350을 사용할 것이며 다른 세션이 롤백되는지 알 수 없습니다. 이 문제를 해결하기 위해 읽기 커밋 격리 수준 　 2. 읽기 커밋 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　atchment >> Client A 클라이언트 B의 트랜잭션이 제출되기 전에 다른 클라이언트 B를 열고 테이블 계정을 업데이트합니다. 　　　 (3) 이때 클라이언트 B의 트랜잭션은 아직 제출되지 않았으며 클라이언트 A는 클라이언트 B의 업데이트된 데이터를 쿼리할 수 없습니다. 해결되었습니다. 더티 읽기 문제: 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 　 (4) 클라이언트 B의 거래 제출 신청서에서, , 우리가 다음 세션에 있다고 가정합니다. 클라이언트 A는 lilei의 잔액이 450이라고 쿼리했지만 다른 트랜잭션에서 lilei의 잔액 값을 400으로 변경합니다. 이 값 450을 다른 작업에 사용하는지 여부는 알 수 없습니다. 연산에 문제가 있지만 확률은 매우 낮습니다. .이 문제를 방지하려면 반복 읽기 격리 수준을 사용할 수 있습니다 3. 반복 읽기 (1) 클라이언트 A를 열고 현재 트랜잭션 모드를 반복 읽기로 설정하고 초기 값을 쿼리합니다. of table account: 　　　 (2) 클라이언트 A의 트랜잭션이 제출되기 전에 다른 클라이언트 B를 열고 테이블 계정을 업데이트하고 제출합니다. 클라이언트 B의 트랜잭션은 실제로 클라이언트 A의 트랜잭션에서 쿼리한 행을 수정할 수 있습니다. 즉, MySQL의 트랜잭션입니다. 반복 읽기는 트랜잭션에서 쿼리한 행을 잠그지 않습니다. 이는 예상을 벗어났습니다. SQL 표준의 트랜잭션 격리 수준은 반복 읽기 중에 읽기 및 쓰기 작업을 위해 행을 잠가야 하지만 mysql에는 잠금이 없습니다. . 혼란스러워요. 응용 프로그램에서 행을 잠그는 데 주의하세요. 그렇지 않으면 단계 (1)에서 lilei의 잔액 400을 다른 작업을 수행하기 위한 중간 값으로 사용하게 됩니다 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　, 그렇지 않으면 lilei의 잔액을 사용하게 됩니다. 다른 작업을 수행하기 위한 중간 값으로 400을 사용합니다. 쿼리: 　　 (4) 단계 (1)을 실행하면 lilei의 잔액은 여전히 ​​400이며 이는 단계 (1)의 쿼리 결과와 일치하며 반복 불가능 읽기 문제가 없습니다. 그런 다음 잔액 업데이트를 실행합니다. - 50 여기서 id = 1, 잔액은 400-50=350으로 변경되지 않았습니다. Lilei의 잔액 값은 (2) 단계에서 350을 사용하여 계산되므로 300입니다. 데이터의 일관성이 파괴되지 않았습니다. 약간 마술적이고 Bar mysql> select * from account;+------+--------+---------+| id   | name   | balance |+------+--------+---------+|    1 | lilei  |     400 ||    2 | hanmei |   16000 ||    3 | lucy   |    2400 |+------+--------+---------+3 rows in set (0.00 sec) mysql> update account set balance = balance - 50 where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from account;+------+--------+---------+| id   | name   | balance |+------+--------+---------+|    1 | lilei  |     300 ||    2 | hanmei |   16000 ||    3 | lucy   |    2400 |+------+--------+---------+3 rows in set (0.00 sec) 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 | 그리고 제출 mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from account;+------+--------+---------+| id | name | balance |+------+--------+---------+| 1 | lilei | 300 || 2 | hanmei | 16000 || 3 | lucy | 2400 |+------+--------+---------+3 rows in set (0.00 sec) 　　　 (7) 클라이언트 A의 잔액 합계를 계산하면 값은 300+16000+2400=18700입니다. , 클라이언트 B의 값은 포함되지 않으며 클라이언트 A가 제출한 후 잔액 합계를 계산하면 19300이 됩니다. 이는 클라이언트 B의 600이 포함되어 있기 때문입니다. B. 세상이 점점 사라지고 600개가 더 있다는 느낌이 들 것입니다. 블록, 이것은 개발자의 관점에서 볼 때 데이터의 일관성이 파괴되지 않습니다. 하지만 애플리케이션에서 우리 코드는 18700을 사용자에게 제출할 수 있습니다. 이러한 작은 확률 상황을 피해야 한다면 아래에 소개된 트랜잭션 격리 수준 "직렬화"를 채택해야 합니다. mysql> 계정에서 합계(잔액) 선택; +-------------+ | 합계(잔액) | +------------- + | -------+ 세트의 1개 행(0.00초) mysql> 커밋; 쿼리 확인, 0개 행이 영향을 받음(0.00초) mysql> 계정에서 합계(잔액) 선택; +-- ------------+ | 합계(잔액) | +------------- + | ----+ 1행 세트(0.00초) 　 　　4. 직렬화 　　　 (1) 클라이언트 A를 열고 현재 트랜잭션 모드를 직렬화 가능으로 설정하고 테이블 계정의 초기 값을 쿼리합니다. in mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into account values(4,'lily',600); Query OK, 1 row affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.01 sec) b, 현재 트랜잭션 모드를 직렬화 가능하게 설정하고 현재 트랜잭션 모드를 직렬화 가능하게 설정하면 레코드를 삽입 할 때 오류가 발생합니다 트랜잭션 격리 수준이 직렬화되면 팬텀 읽기가 발생하지 않습니다. 이 격리 수준은 동시성이 매우 낮습니다. 종종 하나의 트랜잭션이 테이블을 차지하고 수천 개의 다른 트랜잭션이 이를 수행할 수 있을 때까지 기다려야 합니다. 사용하기 전에 제출이 완료되므로 개발에서는 거의 사용되지 않습니다. mysql> set session transaction isolation level serializable; Query OK, 0 rows affected (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from account;+------+--------+---------+| id   | name   | balance |+------+--------+---------+|    1 | lilei  |   10000 ||    2 | hanmei |   10000 ||    3 | lucy   |   10000 ||    4 | lily   |   10000 |+------+--------+---------+4 rows in set (0.00 sec) 　보충사항: 　　1. SQL 사양에 규정된 표준, 다양한 데이터베이스의 구체적인 구현에는 약간의 차이가 있을 수 있습니다 　2. mysql 기본 트랜잭션 격리 수준이 반복 읽기인 경우 읽기 행은 잠기지 않습니다 　3. 트랜잭션 격리 수준이 직렬화인 경우 데이터를 읽으면 전체 테이블이 잠깁니다 　　　4. 이 글을 읽으시면 개발자 입장에서 보면 반복읽기와 팬텀읽기가 논리적으로 문제가 없다고 느끼실 수도 있겠지만, 최종 데이터는 여전히 일관적이지만, 사용자 입장에서는 그렇습니다. 클라이언트의 경우 일반적으로 하나의 트랜잭션(클라이언트 A만 볼 수 있고, 비밀 클라이언트 B의 존재는 알 수 없음)만 볼 수 있으며, 동일한 데이터를 여러 번 읽으면 트랜잭션이 동시에 실행되는 현상을 고려하지 않습니다. 결과가 다르거나 새로운 기록이 갑자기 나타나면 의심이 생길 수 있습니다. 이는 사용자 경험 문제입니다. 　5. mysql에서 트랜잭션이 실행되면 mysql이 작업을 수행하고 이전 작업의 중간 결과를 사용할 수 없기 때문에 최종 결과에는 데이터 일관성 문제가 발생하지 않습니다. . , 다른 동시 트랜잭션의 실제 상황을 기반으로 처리됩니다. 비논리적으로 보이지만 데이터 일관성을 보장하지만 응용 프로그램에서 트랜잭션이 실행되면 다음 작업에서 사용됩니다. 기타 계산. 이것이 우리가 주의해야 하는 이유입니다. 반복 읽기 중에는 행을 잠그고 직렬화 중에는 테이블을 잠가야 합니다. 그렇지 않으면 데이터의 일관성이 파괴됩니다. 6. mysql에서 트랜잭션이 실행될 때 mysql은 각 트랜잭션을 실제 상황에 맞게 종합적으로 처리하므로 데이터의 일관성이 파괴되지 않고 논리적 루틴에 따라 애플리케이션이 실행될 때 , MySQL은 스마트하지 않으며 데이터 일관성 문제는 필연적으로 발생합니다. 　7 데이터의 완전성과 일관성을 보장할 수 있지만 동시성 성능에 미치는 영향도 커집니다. . 대부분의 애플리케이션에서는 데이터베이스 시스템의 격리 수준을 커밋된 읽기로 설정하는 데 우선순위를 부여할 수 있습니다. 이렇게 하면 더티 읽기를 방지하고 동시성 성능이 향상됩니다. 반복 불가능 읽기 및 팬텀 읽기와 같은 동시성 문제가 발생하더라도 이러한 문제가 발생할 수 있는 개별 상황에서는 애플리케이션이 비관적 잠금 또는 낙관적 잠금을 사용하여 이를 제어할 수 있습니다.

위 내용은 MySQL 트랜잭션 격리 수준 예제 튜토리얼의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!