트랜잭션과 잠금

트랜잭션과 잠금

트랜잭션은 작업의 완전성을 보장해 주는 것. 논리적인 작업 셋을 모두 완벽하게 처리하거나 또는 처리하지 못할

경우에는 원 상태로 복구해서 작업의 일부만 적용되는 현상이 발생하지 않게 만들어주는 기능.

잠금: 동시성을 제어하기 위한 기능. 여러 커넥션에서 동시에 동일한 자원(레코드나 테이블)을 요청할 경우

           순서대로 한 시점에는 하나의 커넥션만 변경할 수 있게 해주는 역할을 수행.

트랜잭션: 데이터의 정합성을 보장하기 위한 기능.

격리수준: 하나의 트랜잭션 내에서 또는 여러 트랜잭션 간의 작업 내용을 어떻게 공유하고 차단할 것인지를

           결정하는 레벨.

 

트랜잭션

트랜잭션은 하나의 논리적인 작업 셋에 하나의 쿼리가 있든 두 개 이상의 쿼리가 있든 관계없이 논리적인 작업

셋 자체가 100% 적용되거나 또는 아무것도 적용되지 않아야 함을 보장해 주는 것이다.

주의사항

-처리시작

-사용자 로그인 여부 확인

-사용자의 글쓰기 내용의 오류 발생 여부 확인

-첨부로 업로드 된 파일 확인 및 저장

=> 데이터베이스 커넥션 생성

=> 트랜잭션 시작

-사용자의 입력 내용을 DBMS에 저장

-첨부 파일 정보를 DBMS에 저장

=> 트랜잭션 종료

-저장된 내용 또는 기타 정보를 DBMS에서 조회

-게시물 등록에 대한 알림 메일 발송

=> 트랜잭션 시작

-알림 메일 발송 이력을 DBMS에 저장

=> 트랜잭션 종료

=> 데이터베이스 커넥션 종료

-처리 완료

 

MySQL 엔진의 잠금

스토리지 엔진 레벨의 잠금, MySQL엔진 레벨의 잠금.

MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미치게 되지만, 스토리지 엔진 레벨의 잠금은 스토리지

엔진 간 상호 영향을 미치지 않는다.

글로벌 락

            FLUSH TABLES WITH READ LOCK – 획득

가장 범위가 큰 잠금. 한 세션에서 글로벌 락을 획득하면 다른 세션에서 SELECT를 제외한 대부분의 DDL 문장이나

DML 문장을 실행하는 경우, 글로벌 락이 해제될 때까지 해당 문장이 대기 상태로 남는다.

MySQL 서버 전체에 영향을 미치며, 작업 대상 테이블이나 데이터베이스가 다르다 하더라도 동일하게 영향 적용.

여러 데이터베이스에 존재하는 MyISAM이나 MEMORY 테이블에 대해 mysqldump로 일관된 백업을 받아야 할 때는

글로벌 락을 사용해야 한다.

mysqldump와 같은 백업 프로그램은 우리가 알지 못하는 사이에 이 명령을 내부적으로 실행하고 백업할 수도 있다.

mysqldump를 이용해 백업을 수행한다면 옵션을 확인 후 백업을 진행해야 한다.

테이블 락

개별 테이블 단위로 설정되는 잠금. 명시적 또는 묵시적으로 특정 테이블의 락을 획득할 수 있다.

            LOCK TABLES table_name [READ | WRITE] – 명시적 획득

            UNLOCK TABLES – 반납

묵시적 테이블 락은 MyISAM이나 MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 발생한다.

MySQL 서버가 데이터가 변경되는 테이블에 잠금을 설정하고 데이터를 변경한 후, 즉시 잠금을 해제하는 형태로

사용.  InnoDB 테이블의 경우 스토리지 엔진 차원에서 레코드 기반의 잠금을 제공하기 때문에 단순 데이터 변경

쿼리로 인해 묵시적인 테이블 락이 설정되지는 않는다. InnoDB 테이블에도 테이블 락이 설정되지만 대부분의

데이터 변경(DML) 쿼리에서는 무시되고 스키마를 변경하는 쿼리(DDL)의 경우에만 영향을 미친다.

유저 락

            GET_LOCK() – 잠금 설정

대상이 테이블이나 레코드 또는 AUTO_INCREMENT와 같이 데이터베이스 객체가 아니다.

단순히 사용자가 지정한 문자열에 대해 획득하고 반납하는 잠금이다.

네임 락

데이터베이스 객체(테이블이나 뷰 등)의 이름을 변경하는 경우 획득하는 잠금.

RENAME TABLE … 같이 테이블의 이름을 변경하는 경우 자동으로 획득하는 잠금.

MyISAM과 MEMORY 스토리지 엔진 잠금

MyISAM과 MEMORY 스토리지 엔진은 자체적인 잠금을 가지지 않고 MySQL 엔진에서 제공하는 테이블 락을 그대로

사용한다. MyISAM과 MEMORY 스토리지 엔진에서는 쿼리 단위로 필요한 잠금을 한꺼번에 모두 요청해서 획득하기

때문에 데드락이 발생할 수 없다.

잠금 획득

읽기 잠금:

쓰기 잠금:

잠금 튜닝

            mysql> SHOW STATUS LIKE ‘%Table%’;

테이블 잠금 대기 빈도가 높으면 테이블 잠금 때문에 경합(Lock Connection)이 많이 발생하고 있으면 처리 성능이

영향을 받고 있음. 테이블을 분리하거나 InnoDB 스토리지 엔진으로 변환을 고려.

InnoDB 스토리지 엔진의 경우에는 레코드 단위의 잠금을 사용하기 때문에 집계에 포함되지 않는다.

테이블 수준의 잠금 확인 및 해제

MyISAM이나 MEMORY 등과 같은 스토리지 엔진을 사용하는 테이블은 모두 테이블 단위의 잠금이므로 테이블을

해제하지 않으면 다른 클라이언트에서 그 테이블을 사용할 수 없다. 하나의 테이블에서 전혀 다른 레코드라

하더라도 동시에 변경하는 것은 불가능하기 때문에 쿼리 처리의 동시성이 떨어진다.

            mysql> SHOW OPEN TABLES FROM database_name LIKE ‘%table_name%’;

            mysql> SHOW PROCESSLIST;

 

InnoDB 스토리지 엔진의 잠금

InnoDB의 잠금 방식

비관적 잠금: 현재 트랜잭션에서 변경하고 하는 레코드에 잠금을 획득하고 변경 작업을 처리하는 방식

           현재 변경하고자 하는 레코드를 다른 트랜잭션에서도 변경할 수 있다. 라는 가정을 하기 때문에 먼저

           잠금을 획득한 것. 높은 동시성 처리에 유리. InnoDB에서 채택

낙관적 잠금: 각 트랜잭션이 같은 레코드를 변경할 가능성은 상당히 희박할 것이라고 가정한다.

           우선 변경 작업을 수행하고 마지막에 잠금 충돌이 있는지 확인하여 ROLLBACK 처리.

 

InnoDB의 잠금 종류

레코드 기반의 잠금 기능을 제공. 잠금 정보가 상당히 작은 공간으로 관리되기 때문에 레코드 락이 페이지 락으로

또는 테이블 락으로 레벨업 되는 경우는 없다.

레코드 락

레코드 자체만을 잠그는 락. InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레크도를 잠근다.

인덱스가 하나도 없는 테이블이라 하더라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정.

갭 락

레코드 그 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것을 의미.

레코드와 레코드 사이의 간격에 새로운 레코드가 생성(INSERT)되는 것을 제어한다. (개념적 문제)

넥스트 키 락

레코드 락과 갭 락을 합쳐 놓은 형태의 잠금을 넥스트 키 락이라고 한다.

InnoDB의 갭 락이나 넥스트 키 락은 바이너리 로그에 기록되는 쿼리가 슬레이브에서 실행될 때 마스터에서

만들어낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주 목적이다. …..??

자동 증가 락

InnoDB 스토리지 엔진에서는 AUTO_INCEREMENT 컬럼 속성을 위해서 AUTO_INCREMENT 락 이라고 하는 테이블

수준의 잠금을 사용. INSERT와 REPLACE 쿼리 문장과 같이 새로운 레코드를 저장하는 쿼리에서만 사용.

명시적으로 락을 획득하고 해제하는 방법은 없음.

innodb_autoinc_lock_mode = 0; - 자동 증가 락

innodb_autoinc_lock_mode = 1; - INSERT 되는 레코드 건수를 정확히 예측할 수 있을 때 사용

래치(뮤텍스)를 이용하여 처리

innodb_autoinc_lock_mode = 2; - 자동 증가 락을 사용하지 않고, 항상 래치(뮤텍스)를 사용

대량의 INSERT 실행 도중 다른 커넥션에서 INSERT를 수행할 수 있음. 동시 처리 성능이 높다.

복제를 사용하는 경우 마스터와 슬레이브의 자동 증가 값이 달라질 가능성도 있다. (주의.)

 

인덱스와 잠금

InnoDB의 잠금과 인덱스는 상당히 중요한 연관 관계가 있다.

InnoDB의 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리된다. 즉, 변경해야 할 레코드를

찾기 위해 검색한 인덱스의 레코드를 모두 잠가야 한다.

만약 테이블에 인덱스가 없다면 테이블을 풀 스캔하면서 작업을 하는데 이 과정에서 테이블에 있는 모든 레코드를

잠그게 된다. 이것의 MySQL의 방식이며, MySQL의 InnoDB에서 인덱스 설계가 중요한 이유 또한 이 때문이다.

 

트랜잭션 격리 수준과 잠금

 

MySQL의 격리 수준

트랜잭션의 격리 수준이란 동시에 여러 트랜잭션이 처리될 때, 특정 트랜잭션이 다른 트랜잭션에서 변경하거나

조회하는 데이터를 볼 수 있도록 허용할지 말지를 결정하는 것.

READ UNCOMMITED (= DIRTY READ): 일반적인 DBMS에서는 거의 사용하지 않음.

READ COMMITED

REPEATABLE READ

SERIALIZABLE: 동시성이 중요한 DBMS에서는 거의 사용하지 않음.

순서대로 뒤로 갈수록 각 트랜잭션 간의 데이터 격리(고립) 정도가 높아지며, 동시에 동시성도 떨어진다.

READ UNCOMMITED

각 트랜잭션에서의 변경 내용이 COMMIT, ROLLBACK 여부에 상관없이 다른 트랜잭션에서 보여진다.

COMMIT되지 않은 상태에서도 조회할 수 있다.

어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있게 되는 현상을 DIRTY READ라

하고, DIRTY READ가 허용되는 격리 수준이 READ UNCOMMITED이다.

READ COMMITED

오라클 DBMS에서 기본적으로 사용되는 격리 수준. 온라인 서비스에서 가장 많이 선택되는 격리 수준.

어떤 트랜잭션에서 데이터를 변경했더라도 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있다.

(Undo 영역 사용)

READ COMMITED 격리 수준에서도 NON-REPEATABLE READ라는 부정합 문제가 발생: 하나의 트랜잭션 내에서

          똑같은 SELECT 쿼리를 실행했을 때는 항상 같은 결과를 가져와야 한다는 “REPEATABLE READ” 정합성에

          어긋난다. 이러한 부정합 현상은 일반적인 웹 프로그램에서는 크게 문제가 되지 않지만 하나의 트랜잭션

           에서 동일한 데이터를 여러 번 읽고 변경하는 작업이 금전적인 처리와 연결되면 문제가 발생.

READ COMMITED 격리 수준에서는 트랜잭션 내에서 실행되는 SELECT 문장과 트랜잭션 외부에서 실행되는 SELECT

문장의 차이가 별로 없다. 하지만, REPEATABLE READ 격리 수준에서는 기본적으로 SELECT 문장도 트랜잭션 범위

내에서만 작동하는 것이다. 즉, BEGIN TRANSACTION으로 트랜잭션을 시작한 상태에서는 언제나 동일한 결과를

보여준다. 다른 트랜잭션에서 값을 변경하고 COMMIT을 실행해도 동일한 값을 보여준다.

REPEATABLE READ

MySQL의 InnoDB 스토리지 엔진에서 기본적으로 사용되는 격리 수준. 바이너리 로그를 가진 MySQL 장비에서는

최소 REPEATABLE READ 격리 수준 이상을 사용해야 한다. InnoDB 스토리지 엔진은 트랜잭션이 ROLLBACK될

가능성에 대비해 변경되기 전 레코드를 Undo 공간에 백업해두고 실제 레코드 값을 변경한다. (MVCC 방식)

REPEATABLE READ는 MVCC를 위해 Undo 영역에 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서 동일한

결과를 보여줄 수 있도록 보장한다.

SERIALIZABLE

가장 단순한 격리 수준이지만 가장 엄격한 격리 수준이다. 동시 처리 성능도 다른 트랜잭션 격리 수준보다 떨어진다.

SERIALIZABLE 격리 수준에서는 SELECT 작업도 공유 잠금(읽기 잠금)을 획득해야만 하며, 동시에 다른 트랜잭션에서

레코드를 변경하지 못한다.