Lock은 동시성을 제어하기 위한 기능
Transaction은 데이터의 정합성을 보장하기 위한 기능
Lock은 여러 커넥션에서 동시에 동일한 자원(레코드나 테이블)을 요청할 경우 순서대로 한 시점에는 하나의 커넥션만 변경할 수 있게 해주는 역할을 한다. Lock이 없다면 하나의 데이터를 여러 커넥션에서 동시에 변경해버릴 수 있게 되어 해당 레코드의 값은 예측할 수 없는 상태가 된다.
격리 수준이라는 것은 하나의 트랙잭션 내에서 또는 여러 트랜잭션 간의 작업 내용을 어떻게 공유하고 차단할 것인지를 결정하는 레벨을 의미한다.
트랜잭션을 지원하지 않는 MyISAM과 트랜잭션을 지원하는 InnoDB
트랜잭션은 여러 개의 변경 작업을 수행하는 쿼리가 조합됐을 때만 의미 있는 개념이 아니다. 트랜잭션은 하나의 논리적인 작업 셋에 하나의 쿼리가 있든 두 개 이상의 쿼리가 있든 관계없이 논리적인 작업 셋 자체가 100% 적용되거나(COMMIT을 실행했을 때) 또는 아무것도 적용되자 않아야(ROLLBACK 또는 트랜잭션을 ROLLBACK시키는 오류가 발생했을 때)함을 보장해 주는 것이다.
네임 락
데이터베이스 객체의 이름을 변경하는 경우 획득하는 잠금
mysql> RENAME TABLE rank TO rank_bakcup;
mysql> RENAME TABLE rank_new TO rank;
Table_locks_immediate : 다른 잠금이 풀리기를 기다리지 않고 바로 잠금을 획득한 횟수
Table_locks_waited: 다른 잠금이 이미 해당 테이블을 사용하고 있어서 기다렸던 횟수
잠금 대기 쿼리 비율 = Table_locks_waited / (Table_locks_immediate + Table_locks_waited) * 100;
InnoDB스토리지 엔진의 경우는 레코드 단위의 잠금을 사용하기 때문에 위의 집계에 포함되지 않는다.
집계된 수치는 MyISAM이나 MEMORY 또는 MERGE 스토리지 엔진을 사용하는 테이블이 대상이 된다.
테이블 수준의 잠금 확인 및 해제
[session1]
use employees;
lock tables employees READ;
[session2]
use employees;
update employees set hire_date=now() where emp_no = 100001;
[session3]
use employees;
update employees set birth_date = now(), hire_date=now() where emp_no = 100001;
[session4]
show open tables from employees;
show processlist ;
kill 10;
show processlist ;
InnoDB 스토리지 엔진의 잠금 정보를 진단
show engine innodb status
=====================================
2020-12-08 23:41:32 0x7f68ab4b6700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 57 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 3 srv_active, 0 srv_shutdown, 8223 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1
OS WAIT ARRAY INFO: signal count 1
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 11286
Purge done for trx's n:o < 11284 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421563432513776, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421563432517200, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421563432516344, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421563432515488, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421563432514632, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421563432512064, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421563432511208, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
932 OS file reads, 270 OS file writes, 87 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.07 writes/s, 0.07 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 1 merges
merged operations:
insert 0, delete mark 1, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 4 buffer(s)
0.00 hash searches/s, 0.04 non-hash searches/s
---
LOG
---
Log sequence number 395045126
Log buffer assigned up to 395045126
Log buffer completed up to 395045126
Log written up to 395045126
Log flushed up to 395045126
Added dirty pages up to 395045126
Pages flushed up to 395045126
Last checkpoint at 395045126
30 log i/o's done, 0.02 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137363456
Dictionary memory allocated 432691
Buffer pool size 8192
Free buffers 7146
Database pages 1037
Old database pages 402
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 895, created 142, written 189
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1037, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1941, Main thread ID=140087946848000 , state=sleeping
Number of rows inserted 0, updated 2, deleted 0, read 204
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.04 reads/s
Number of system rows inserted 0, updated 318, deleted 0, read 7739
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
트랜잭션의 잠금 상태 조회
INFORMATION_SCHEMA.INNODB_TRX
InnoDB의 잠금 방식
현재 트랜잭션에서 변경하고자 하는 레코드에 대해 잠금을 획득하고 변경 작업을 처리하는 방식의 비관적 잠금
InnoDB의 잠금 종류
InnoDB 스토리지 엔진은 레코드 기반의 잠금 기능을 제공하며, 잠금 정보가 상당히 작은 공간으로 관리되기 때문에 레코드 락이 페이지 락으로 또는 테이블 락으로 레벨업되는 경우(락 에스컬레이션)는 없다.
레코드와 레코드 사이의 간격을 잠그는 갭(GAP)락이 존재
그림] InnoDB 잠금의 종류
레코드 락(Recode lock, Record only lock)
레코드 자체만을 잠근다 InnoDB 스토리지 엔진은 특징은 레코드 자체가 아니라 인덱스의 레코드를 잠근다.
만약 인덱스가 하나도 없는 테이블이라 하더라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정한다. InnoDB에서는 대부분 보조 인덱스를 이용한 변경 작업은 넥스트 키락 또는 갭 락을 사용하지만, Primary Key 또는 Unique Index에 의한 변경 작업은 갭(Gap, 간격)에 대해서는 잠그지 않고 레코드 자체에만 락을 건다.
갭 락(Gap lock)
Gap lock은 레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것을 의미한다. Gap lock의 역할은 레코드와 레코드 사이의 간격에 새로운 레코드가 생성(INSERT)되는 것을 제어하는 것이다. 갭 락은 개념일 뿐이지 자체적으로 사용되지는 않고 넥스트 키 락의 일부로 사용된다.
넥스트 키 락(Next key lock)
레코드 락과 갭 락을 합쳐 놓은 형태의 잠금을 넥스크 키락 이라고 한다. STATEMENT 포맷의 바이너리 로그를 사용하는 MySQL 서버에서는 REPEATABLE READ 격리 수준을 사용해야 한다. 또한 innodb_locks_unsafe_for_ninlog 파라미터가 비활성화 되면(파라미터 값이 0으로 설정되면) 변경을 위해 검색하는 레코드에는 넥스트 키 락 방식으로 잠금이 걸린다. InnoDB의 갭 락이나 넥스트 키 락은 바이너리 로그에 기록되는 쿼리가 슬레이브에서 실행될 때 마스터에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주 목적이다.
자동 증가 락(Auto Increment lock)
Auto Increment lock은 테이블 수준의 락을 제공한다. AUTO_INCREMENT락은 명시적으로 획득하고 해제하는 방법이 없다.
innodb_autoinc_lock_mode 파라미터
SHOW VARIABLES LIKE '%innodb_autoinc%'
innodb_autoinc_lock_mode =0
모든 INSERT문장은 자동 증가 락을 사용한다.
innodb_autoinc_lock_mode =1
MySQL서버가 INSERT되는 레코드 건수를 예측할 수 있을때는 훨씬 가볍고 빠른 래치(뮤텍스)를 이용해서 처리한다. 하지만 INSERT SELECT 와 같이 건수를 예측할 수 없을 때는 자동 증가 락을 사용한다.
대량 INSERT가 수행될 때는 InnoDB 스토리지 엔진은 한번에 여러 개의 자동 증가 값을 한번에 할당받아서 INSERT되는 레코드에 사용되어 할당받은 자동 증가 값이 남아서 폐기하므로 대량 INSERT 문장의 실행 이후에 INSERT되는 레코드의 자동증가 값은 연속되지 않고 누락된 값이 발생할 수 있다.
하나의 INSERT문장으로 삽입되는 레코드는 연속된 자동 증가 값을 가지게 된다. 그래서 이설정 모드를 연속모드(Consecutive Mode) 라고도 한다.
innodb_autoinc_lock_mode =2
항상 경량화된 래치(뮤텍스)를 사용한다. 하나의 INSERT문장으로 삽입되는 레코드도 연속된 자동 증가 값을 보장하지 않는다. 그래서 이 설정 모드를 인터리빙 모들(Interleaved mode)라고도 한다. 다른 모드와 달리 대량 INSERT 문장이 실행되는 중에도 다른 커넥션에서 INSERT를 수행할 수 있게 되므로 동시 처리 성능이 높아지게 된다. 이 설정에서 작동하는 자동 증가 기능은 유니크한 값이 생성된다는 것만 보장하며, 복제를 사용하는 경우에는 마스터와 슬레이브의 자동 증가 값이 달라질 가능성도 있기 때문에 주의해야 한다.
더 자세한 내용은 MySQL메뉴얼의 Configurable InnoDB Auto-Increment Locking 을 참조
* 참고도서 - RealMysql
'MySQL > MySQL' 카테고리의 다른 글
| MySQL의 격리 수준 (0) | 2024.12.06 |
|---|---|
| 트랜잭션 격리 수준과 잠금 (0) | 2024.12.06 |
| 인덱스와 잠금 (0) | 2024.12.06 |
| InnoDB 아키텍처 요약 (0) | 2024.12.04 |
| sample script(emp, dept, salgrade) (0) | 2024.12.04 |