close
프로필 배경
프로필 로고

DBA 킴오리지날

Database/MYSQL ・2022. 10. 27. fullscreen 넓게보기

[MYSQL] Lock 구조 & Query

반응형
Transcation 과 Lock

1. Transcation

  • 논리적인 작업단위로 전부터리되거나 처리되지 않는 (Commit / Rollback) 원자성을 보장하기 위한 기능이다.
  • MySQL InnoDB의 격리 레벨(isolation level)이 REPEATABLE TABLE 이다. 
show session variables like '%isola%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.01 sec)

격리 수준 DIRTY READ NON-REPEATABLE READ PHANTOM READ
READ UNCOMMITTED O O O
READ COMMITTED   O O
REPEATABLE READ     O(InnoDB는 발생 X)
SERIALIZABLE      

참고 : https://zzang9ha.tistory.com/381

 

2. Lock

  • 서로 다른 작업에 같은 자원을 동시에 필요로 할 때 자원 경쟁이 일어나는데, 이때 순서대로 사용되는 동시성을 보장하기 위한 기능이다. 

-. MySQL에서 사용되는 Lock은 크게 MySQL 엔진 레벨의 Lock과 스토리지 엔진 레벨의 Lock이 있다.

 

InnoDB 스토리지 레벨의 락

-. 기본적으로 비관적 락(Perssimistic locking)을 사용한다.

  • 비관적 락
    Trascation에서 변경하려는 레코드에 대해 락을 획득하고 쿼리를 수행하는 방식
  • 낙관적 락
    Transcation에서 Lock 없이 일단 쿼리 수행을 하고 마칠 때 서로 다른 트랜잭션에서 충돌이 있었는지 확인하고 문제가 있으면 충돌이 난 트랜잭션을 롤백하는 방식.
    # 보통 대규모 트래픽을 처리하는 애플리케이션에서는 성능 이슈 떄문에 Lock을 최소화 해야하기 때문에 낙관적 락으로 변경하여 사용하기도 한다.

1. 락이 적용되는 상황에 따른 분류

1.1 로우 락 (Row Lock)
  • 테이블 Row에 걸리는 Lock을 의미한다.
  • 각 Row에 S-Lock, X-Lock을 사용할 수 있다.
1.1 레코드 락 (Record Lock)
  • select c1 from where c1=100 for update; 일때, c1=100인 인덱스 레코드에 락을 걸어서 수정 , 삽입, 삭제 등의 다른 트랜잭션을 막는다.
  • 실제 테이블의 레코드에 대해 락을 걸지 않고 익덱스 레코드에 락을 건다.
  • 따로 생선한 인덱스가 없는 테이블은 InnoDB가 자체적으로 생성한 클러스터 인덱스를 이용해 락을 건다.
  • 기본키나 유니크 키에 의한 변경 작업은 갭 락 없이 딱 인덱스 레코드에만 락을건다. 
1.2. 갭 락 (Gap Lock)
  • 인덱스 레코드와 인접한 앞/뒤 사이 공간에 락을 거는 것인데 개념적인 용어로 단독으로 사용되지 않고 넥스트키락에서 사용된다. 
1.3. 넥스트 키 락 (Next key lock)
  • 레코드 락과 갭 락을 합쳐놓은 형태이다. 인덱스 레코드도 잠그고 그 인덱스 레코드 앞, 뒤 갭도 잠근다.
  • select c1 from t where c1 between 10 and 20 for update; 일때 c1=15인 레코드를 insert하는 트랜잭션이 있다면 막는다. 반드시 인덱스 레코드 사이에 있는 갭만 락을 거는게 아니라 제일 앞 또는 뒤에있는 인덱스 레코드의 갭도 락을 건다. 예를들어 c1=10인 레코드 인덱스 바로앞에 c1=8인 레코드 인덱스가 있는 상태라면, c1 = 9 인 레코드를 insert하려고 하면 막힌다. (그 갭에도 락이 걸려 있기 때문이다. )
1.4. Insert Intentaion Lock
  • Insert 구뭉니 실행될 떄 InnoDB 엔진 내부적으로 Implicit(암시적)하게 획득하는 특수한 형태의 Gap Lock
  • 여러개의 트랜잭션이 Gap 안에 다른 위치에 Insert를 동시에 수행할 때 기다릴 필요가 없도록 하는 것이 목적
1.5 오토 인크리먼트 락 (Auto invrement lock)
  • MySQL에서 자동 증가하는 숫자 값을 채번하기 위해 AUTO_INCREMENT라는 속성을 정의할 떄가 있다. 이때 같이 INSERT하려는 요청이 올 떄 거는 락이다. 

 

2. 락 적용 요소에 따른 분류

2.1 Shared Lock (공유 잠금)
  • Row Level Lock
  • 특정 Row를 읽을 떄 사용되므로 다른말로 Read Lock라고한다.
  • 다른 트랜잭션이 해당 Row에 대해 베타 락
  • select .. lock in share mode (8.0 부터는 FOR SHARE로가능)

2.2 Exclusive Lock(베타적 잠금)

  • Row Level Lock
  • 특정 Row를 변경 혹은 삭제하고자 할때 사용되므로 다른 말고 Write Lock이라고도 한다.
  • 다른 트랜잭션이 해당 Row에 대해 공유 락 / 베타 락 모두 획득하지 못하게 대기하게 된다. 
  • select .. for update
2.3 Intention Lock (의도 잠금)

  • Table Level Lock
  • 해당 데이블 row 중 나중에 row-level lock이 걸릴 것이라는 걸 미리 알려주기 위해 사용된다.
방법
  1. Intention Shared Lock (IS)이 테이블에 걸린다.
  2. row-level에 Shared Lock (S)이 걸린다.
  1. Intention Exclusive Lock (IX)이 테이블에 걸린다.
  2. row-level에 Exclusive Lock (X)이 걸린다.
  • IS, IX 락은 여러 트랜잭션에서 동시에 접근이 가능하다. (서로 block하지 않는다.)
    • 하지만 row-level의 실제 Lock(S or X)에서 접근 제어를 하게 된다.
  • LOCK TABLES (or ALTER TABLE, DROP TABLE) 이 실행될 때는 IS, IX를 모두 block 하는 table-level 락이 걸린다.
    • 즉 IS, IX lock을 획득 하려는 트랜잭션은 대기 상태로 빠지게 된다.
※Table-level에서 한 번, Row-level에서 한 번, 2단계(2-phase)로 Lock을 적용하는 이유는?
  • A트랜잭션에서 이미 테이블에 대해 락이 걸려있는데, B 트랜잭션에서 해당 테이블의 특정 row에 lock을 거는 것을 원천적으로 방지할 수 있다.
    예) row-level의 write이 일어나고 있을 때 테이블 스키마가 변경되서는 안 된다. write query의 경우 이미 IX 락을 획득한 상태이기 때문에 해당 테이블의 스키마가 변경되는 것을 막을 수 있다.

Share Lock, Exclusive Lock, Intention Lock 호환 표

  X IX S IS
X 충돌 충돌 충돌 충돌
IX 충돌 호환 충돌 호환
S 충돌 충돌 호환 호환
IS 충돌 호환 호환 호환

 

 

 

MySQL 엔진 레벨의 락
  1. 글로벌 락
    • 모든 테이블에 잠금을 건다.
    • "FLUSH TABLES WITH READ LOCK" 명령으로 락을 걸 수 있는데 굳이 일부러 걸지 않는다. 특히나 운영 중에 전체를 멈추는 락을 건다는 건 서비스 장애로 이어지기 때문이다.
  2. 테이블 락
    • 각각 테이블에 테이블 단위로 락을 건다.
    • MySQL InnoDB는 기본적으로 스토리지에서 레코드 락을 사용하므로 DML에서는 주로 사용되지 않지만, DDL 같이 테이블의 스키마에 변화를 줄 때는 사용된다.
    • 마찬가지로 특정 명령어로 락을 걸 수 있으나 이 역시도 장애로 이어질 확률이 높아서 명시적으로 사용하지는 않는다.
  3. 네임 락
    • 테이블 또는 뷰등의 스키마 객체의 이름을 변경하는 경우(rename ...)에 자동으로 락을 건다.
  4. 유저 락
    • 사용자가 지정한 문자열에 락을 건다.

 

 

MySQL 5.7

-. 5.7 버전에서는 실제 lock wait가 발생해야 Lock관련 정보가 조회된다.
Lock 경합이 없는 상황에서는 실행중인 Transcation이 어떤 Lock를 잡고있는지 조회 불가

1. MySQL 5.7 Lock 조회 쿼리 

SELECT straight_join
    w.trx_mysql_thread_id waiting_thread,
    w.trx_id waiting_trx_id,
    w.trx_query waiting_query,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_id blocking_trx_id,
    b.trx_query blocking_query,
    bl.lock_id blocking_lock_id,
    bl.lock_mode blocking_lock_mode,
    bl.lock_type blocking_lock_type,
    bl.lock_table blocking_lock_table,
    bl.lock_index blocking_lock_index,
    wl.lock_id waiting_lock_id,
    wl.lock_mode waiting_lock_mode,
    wl.lock_type waiting_lock_type,
    wl.lock_table waiting_lock_table,
    wl.lock_index waiting_lock_index
    FROM
    information_schema.INNODB_LOCK_WAITS ILW ,
    information_schema.INNODB_TRX b ,
    information_schema.INNODB_TRX w ,
    information_schema.INNODB_LOCKS bl ,
    information_schema.INNODB_LOCKS wl
    WHERE
    b.trx_id = ilw.blocking_trx_id
    AND w.trx_id = ilw.requesting_trx_id
    AND bl.lock_id = ilw.blocking_lock_id
    AND wl.lock_id = ilw.requested_lock_id;

 

MySQL 8.0

1. MySQL 8.0 Lock 조회 쿼리

1. 현재 접속 세션 확인
select * FROM information_schema.PROCESSLIST p
order by STATE ;


2. LOCK 조회에서 해당 thread 죽이기
-- MySQL row level
 SELECT straight_join
 		dl.ENGINE_TRANSACTION_ID, 
        dl.THREAD_ID
       , est.SQL_TEXT
       , dl.OBJECT_SCHEMA
       , dl.OBJECT_NAME
       , dl.INDEX_NAME
       , dl.LOCK_TYPE
       , dl.LOCK_MODE
       , dl.LOCK_STATUS
       , dl.LOCK_DATA
     FROM
       performance_schema.data_locks dl inner join performance_schema.events_statements_current est 
	   on dl.THREAD_ID = est.THREAD_ID
     ORDER BY est.TIMER_START,dl.OBJECT_INSTANCE_BEGIN;
     
 -- Mysql InnoDB level
  select 
 	*
 FROM information_schema.INNODB_TRX
 where trx_id = 'Thread level에서 조회 된 ENGINE_TRANSACTION_ID';
 
 -- Mysql Process level
 select * FROM information_schema.PROCESSLIST p
where id = 'InnoDB level에서 조회된 trx_mysql_thread_id' ;
 
 -- lock 잡고있는 process 죽이기
 kill 'trx_mysql_mysql_thread_id'; 
 
4. hierarchy lock
select * 
FROM information_schema.INNODB_TRX a, 
( SELECT straight_join
 		dl.ENGINE_TRANSACTION_ID trx_id, 
        dl.THREAD_ID
       , est.SQL_TEXT
       , dl.OBJECT_SCHEMA
       , dl.OBJECT_NAME
       , dl.INDEX_NAME
       , dl.LOCK_TYPE
       , dl.LOCK_MODE
       , dl.LOCK_STATUS
       , dl.LOCK_DATA
     FROM
       performance_schema.data_locks dl inner join performance_schema.events_statements_current est 
	   on dl.THREAD_ID = est.THREAD_ID
     ORDER BY est.TIMER_START,dl.OBJECT_INSTANCE_BEGIN) b
where a.trx_id = b.trx_id
order by a.trx_id, a.trx_state ;

5. plan
explain
 select * from test_tab1;

6. top 5 sql
SELECT * FROM mysql.slow_log
order by query_time desc
limit 5;   
     
3. LOCK mode 별 count
select LOCK_MODE,COUNT(*)
from performance_schema.data_locks
group by LOCK_MODE;

# Row Level Lock

SELECT T.PROCESSLIST_ID, T.PROCESSLIST_HOST, T.PROCESSLIST_USER
     , CONCAT(A.OBJECT_SCHEMA,'.',A.OBJECT_NAME) LOCKED_OBJ
     , (SELECT SEC_TO_TIME (SYSDATE() - TRX_STARTED)
        FROM INFORMATION_SCHEMA.INNODB_TRX
        WHERE TRX_MYSQL_THREAD_ID=T.PROCESSLIST_ID ) LOCK_TIME
     , LOCK_TYPE
     , LOCK_MODE
     , IFNULL(T.PROCESSLIST_INFO
         ,(SELECT SQL_TEXT FROM PERFORMANCE_SCHEMA.EVENTS_STATEMENTS_CURRENT WHERE THREAD_ID = T.THREAD_ID)
       ) SQLTEXT
FROM PERFORMANCE_SCHEMA.DATA_LOCKS A
  , PERFORMANCE_SCHEMA.THREADS T
WHERE A.THREAD_ID=T.THREAD_ID
;

# Lock(Blocking, Waiting)

SELECT
  (SELECT PROCESSLIST_USER FROM PERFORMANCE_SCHEMA.THREADS WHERE THREAD_ID=A.BLOCKING_THREAD_ID) BLOCKING_USER
, (SELECT PROCESSLIST_USER FROM PERFORMANCE_SCHEMA.THREADS WHERE THREAD_ID=A.REQUESTING_THREAD_ID) WAITING_USER
, (SELECT PROCESSLIST_ID FROM PERFORMANCE_SCHEMA.THREADS WHERE THREAD_ID=A.BLOCKING_THREAD_ID) BLOCKING_THR
, (SELECT PROCESSLIST_ID FROM PERFORMANCE_SCHEMA.THREADS WHERE THREAD_ID=A.REQUESTING_THREAD_ID) WAITING_THR
, (SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME) FROM PERFORMANCE_SCHEMA.DATA_LOCKS WHERE ENGINE_LOCK_ID=A.REQUESTING_ENGINE_LOCK_ID) LOCKED_OBJ
, (SELECT LOCK_TYPE FROM PERFORMANCE_SCHEMA.DATA_LOCKS WHERE ENGINE_LOCK_ID = A.REQUESTING_ENGINE_LOCK_ID) REQUEST_MODE
, (SELECT LOCK_MODE FROM PERFORMANCE_SCHEMA.DATA_LOCKS WHERE ENGINE_LOCK_ID = A.BLOCKING_ENGINE_LOCK_ID) HOLDING_MODE
, (SELECT PROCESSLIST_INFO FROM PERFORMANCE_SCHEMA.THREADS WHERE THREAD_ID = A.REQUESTING_THREAD_ID) SQL_TEXT
FROM PERFORMANCE_SCHEMA.DATA_LOCK_WAITS A
;

 

 

2. Mysql 서버에서 지정 시간 간격으로 Processlist 자동 조회

mysqladmin -u root -p processlist -i 5

3. 현재 Auto commit 값 확인

select @@AUTOCOMMIT;

4. Auto commit 해제

SET AUTOCOMMIT = 1; -- 1이면 auto commin이 켜져있는 것이다.
SET AUTOCOMMIT = 0;

 

MySQL SQL 처리 과정

1. Query Cache

먼저  Query를 분석하기 전에 MySQL Server는 Cache를 확인합니다. Query Cache는 Select문에 대한 전체 결과 집합을 저장하고 있습니다. Client가 Query 수행 시 제일 먼저 거치는 단계로, Query를 분석하기 전에 MySQL Server는 Query Cache를 참고합니다. Client가 이미 캐시에 있는 것과 동일한 Query를 실행하면 Server는 SQL Parser, Optimizer 단계를 건너 뛰고 결과를 직접 반환합니다.

테이블의 Data가 변경되면 Query Cache의 내용 역시 달라지므로 변경된 테이블과 관련된 정보를 모두 삭제해야 했습니다. 이는 심각가한 동시 처리 기능 저하를 유발하고 많은 버그의 원인이 되었기에 MySQL 8.0으로 올라오면서 Query Cache는 MySQL SEerver의 기능에서 완전히 제거 되었습니니다.

 

ISAM 엔진 vs INNODB LOCK 구조
Details MyISAM InnoDB
Transaction - 테이블 락을 기본으로 insert, update, delete가 이루어진다. - ib_logfileN log 파일을 통하여 대량의 트랜잭션을 버퍼링하고 serialization
- 로그파일은 최소 2개의 그룹이며, 서로 rotate 스위칭되며 갱신되어 데이터를 파일로 적용한다.
- Transation 이외에 Foreign Key, Trigger를 지원
Lock - Table 단위 Lock 사용
- COUNT(*) 정보를 별도 저장		 - 행(Row)단위 Lock을 사용

 

 

 

#참고

https://jeong-pro.tistory.com/241
https://blog.ex-em.com/1683
https://kdhyo98.tistory.com/101
반응형

'Database > MYSQL' 카테고리의 다른 글

[MYSQL] 실행된 SQL 쿼리 확인  (0) 2022.10.30
[MYSQL] Cluster Index  (0) 2022.10.30
[MYSQL] PMM 모니터링  (0) 2022.10.21
[MYSQL] MYSQL 아키텍처  (0) 2022.10.21
[MYSQL] 모니터링 쿼리  (0) 2022.10.19
Database/MYSQL 관련 글
더 보기
댓글

티스토리툴바