InnoDB Locking and Transaction Model

MySQL lock

global lock

MySQL 서버 전체에 걸리는 잠금으로 보통 mysqldump 등에서 백업을 위해 사용한다.

FLUSH TABLES WITH READ LOCK;

위 구문은 실행과 동시에 데이터베이스에 구분없이 서버 전체를 대상으로 잠금이 획득된다.

table lock

개별 테이블 단위로 설정하는 장금이다.

FLUSH TABLES WITH READ LOCK;

명시적 잠금은  아래와 같이 구문으로 명시적으로 잠금을 획득할 수 있다.

LOCK TABLES table_name [READ | WRITE];
UNLOCK TABLES;

묵시적 잠금은  MyISAM 혹은 MEMORY 스토리지 엔진 등에서 데이터를 변경할때 테이블 단위 잠금을 지원하므로 자동으로 획득후 해제를 하게 된다.

 

user lock

GET_LOCK() 을 통해서 임의의 잠금을 획득할 수 있다. 특징은 데이터베이스의 특정 개체에 대한 잠금이 아니라 함수를 호출한 String 에 한해 잠금이 행해 진다. 잠금에 대한 획득, 확인, 해제에는 SELECT 구분이 사용된다.

즉 어플리케이션이 데이터베이스를 통해 동기화 요건을 처리하는 경우에 사용된다.

-- // "mylock" 이라는 문자열에 대해 잠금을 획득한다.
-- // 이미 잠금이 사용 중이면 2초 동안만 대기한다.
mysql> SELECT GET_LOCK('mylock', 2 );
 
 
-- // "mylock" 이라는 문자열에 대해 잠금이 설정돼 있는지 확인한다.
mysql> SELECT IS_FREE_LOCK('mylock');
 
 
-- // "mylock" 이라는 문자열에 대해 획득했던 잠금을 반납(해제)한다.
mysql> SELECT RELEASE_LOCK('mylock');
 
 
-- // 3개 함수 모두 정상적으로 락을 획득하거나 해제한 경우에는 1을, 아니면 NULL이나 0을 반환한다.

 

metadata lock

mysql 은 metadata lock 을 통해 데이터베이스 객체에 대한 동시성 제어를 수행한다.  metadata lock 은 다음에 적용된다.

• table
• schema
• stored program( procedure, function, trigger, event )
• tablespace
• GET_LOCK()

performance_schema 의  metadata_locks 테이블을 통해 자세한 내용을 확인할 수 있다.

metadata lock 잠금은 쓰기가 읽기보다 우선되는데 max_write_lock_count 시스템 변수를 초과하는 쓰기 잠금이 발생하는 경우에는

읽기가 우선되기도 한다. 하지만 기본값이 매우 크기 때문에 그런일은 거의 발생하지 않음

root@localhost:(none) 09:49:12> show variables like '%max_write_lock_count';
+----------------------+----------------------+
| Variable_name        | Value                |
+----------------------+----------------------+
| max_write_lock_count | 18446744073709551615 |
+----------------------+----------------------+
1 row in set (0.01 sec)

 

 

InnoDB Lock

Shared and Exclusive Locks

shared ( s ) lock 은 row에 대한 읽기 잠금이다.

exclusive ( x ) lock 은 row 에 대한 변경 잠금이다.

 

Intention Locks

인텐션 잠금은 테이블의 잠금과 행에 대한 장금이 공존하는 다중 세분화 잠금이다.  의미대로 잠금의 의향을 나타낸다.

-- client1
root@localhost:test 11:44:53> select * from t1 where i1 = 1 for update;
+----+------+
| i1 | i2   |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0.00 sec)
-- intention lock 확인
root@localhost:(none) 11:58:13> select object_schema, lock_type, lock_mode from performance_schema.data_locks;
+---------------+-----------+---------------+
| object_schema | lock_type | lock_mode     |
+---------------+-----------+---------------+
| test          | TABLE     | IX            |
| test          | RECORD    | X,REC_NOT_GAP |
+---------------+-----------+---------------+
-- client2
root@localhost:test 11:44:46> lock tables t1 write;
-- lock wait 확인
+-------------+---------------+-------------+----------------------+-------------+-----------+----------------+----------------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE            | LOCK_STATUS | THREAD_ID | PROCESSLIST_ID | PROCESSLIST_INFO     |
+-------------+---------------+-------------+----------------------+-------------+-----------+----------------+----------------------+
| TABLE       | test          | t1          | SHARED_WRITE         | GRANTED     |        47 |              7 | NULL                 |
| TABLE       | test          | t1          | SHARED_NO_READ_WRITE | PENDING     |        55 |             15 | lock tables t1 write |
+-------------+---------------+-------------+----------------------+-------------+-----------+----------------+----------------------+

client1 은 

• intention ( table level lock )
• exclusive ( row level lock )

client2 는

• exclusive ( table level lock )

이때 intention lock 이 걸려 있다면 record level lock 에 대한 확인을 하지 않아도 되기 때문에 경합을 줄일 수 있다.

Table-level lock typ 호환표

	X	IX	S	IS
X	Conflict	Conflict	Conflict	Conflict
IX	Conflict	Compatible	Conflict	Compatible
S	Conflict	Conflict	Compatible	Compatible
IS	Conflict	Compatible	Compatible	Compatible

conflict 는 lock wait 를 하게 된다.

 

Record Locks

인데스 행에 행하는 잠금을 말한다.  테이블에 인덱스가 없더라고 임의의 clustered index 가 생성되면 해당 인덱스에 잠금을 행한다.

-- client1
root@localhost:test 11:44:53> select * from t1 where i1 = 1 for update;
+----+------+
| i1 | i2   |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0.00 sec)
-- intention lock 확인
root@localhost:(none) 11:58:13> select object_schema, lock_type, lock_mode from performance_schema.data_locks;
+---------------+-----------+---------------+
| object_schema | lock_type | lock_mode     |
+---------------+-----------+---------------+
| test          | TABLE     | IX            |
| test          | RECORD    | X,REC_NOT_GAP |
+---------------+-----------+---------------+
-- client2
root@localhost:test 11:44:46> lock tables t1 write;
-- lock wait 확인
+-------------+---------------+-------------+----------------------+-------------+-----------+----------------+----------------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE            | LOCK_STATUS | THREAD_ID | PROCESSLIST_ID | PROCESSLIST_INFO     |
+-------------+---------------+-------------+----------------------+-------------+-----------+----------------+----------------------+
| TABLE       | test          | t1          | SHARED_WRITE         | GRANTED     |        47 |              7 | NULL                 |
| TABLE       | test          | t1          | SHARED_NO_READ_WRITE | PENDING     |        55 |             15 | lock tables t1 write |
+-------------+---------------+-------------+----------------------+-------------+-----------+----------------+----------------------+

 

Gap Locks

갭잠금은 인덱스 레코드 사이에서 발생하는 잠금이다.  유니크 인덱스가 없는 경우에는 범위에 해당하지 않는 다른 transaction 에도

갭잠금에 의해 잠금 대기가 발생할 수 있다. 

-- client1 gap lock 발생
root@localhost:test 10:45:59>  select * from t1 where i1 between 1 and 4 for update;
+----+------+
| i1 | i2   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  4 |    4 |
+----+------+
3 rows in set (0.00 sec)
-- client2 gqp lock wait
root@localhost:test 10:45:27>  insert into t1 values( 3,3 );
 
-- gap lock 확인
---TRANSACTION 3798, ACTIVE 3 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 5765, OS thread handle 140698940647168, query id 654 localhost root update
insert into t1 values( 3,3 )
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 8 page no 4 n bits 72 index PRIMARY of table `test`.`t1` trx id 3798 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

아래는 primary  key 또는 unique index 가 없는 경우이다.

-- client 1 gqp lock 발생
root@localhost:test 15:40:21>  select * from t2 where i1 between 1 and 4 for update;
+------+------+
| i1   | i2   |
+------+------+
|    1 |    1 |
|    2 |   22 |
|    4 |    4 |
+------+------+
3 rows in set (0.00 sec)
 
 
-- client 2 lock 대기
root@localhost:test 15:40:26> insert into t2 values( 3,3 );
 
 
-- lock 확인
---TRANSACTION 3402, ACTIVE 2 sec inserting ( lock 대기하는 transaction )
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 15, OS thread handle 140698940942080, query id 237 localhost root update
insert into t2 values( 3,3 )
------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 9 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`t2` trx id 3402 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;
 
------------------
---TRANSACTION 3401, ACTIVE 5 sec   ( lock 발생시킨 transaction )
2 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 7, OS thread handle 140698941236992, query id 236 localhost root

반대로 primary key  혹은 unique index 를 사용하는 gap lock 의 경우 범위를 벚어난다면 잠금대기가 발생하지 않는다.

isolation level 을 "READ COMMITTED" 로 설정할 경우 gap lock 은 비활성화 된다.

gap share lock 과 gap exclusive lock 은 공존할 수 있다는데 재현이 안됨

Next-Key Locks

next-key 잠금은 record 잠금과 gap 잠금의 조합으로 수행된다. 잠금시 지정한 gap , 이전 gap, 다음 gap 총 3개의 gap 에 대해 잠금을 수행하며

repeatable-read 에서 는 phandom read 를 방지할 목적으로 next-key 잠금을 사용한다.

next-key 잠금은 secondary index 에서만 발생한다.

 

다음과 같이 데이터가 있다고 하자.

root@localhost:test 16:22:09> select * from t3;
+----+------+
| i1 | i2   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  4 |    4 |
|  5 |    5 |
|  7 |    7 |
|  9 |    9 |
+----+------+
6 rows in set (0.00 sec)

이 상태에서 i2 between 4 and 5 로 gap lock 을 수행한다.  다른 세션에서는 (6,6) 을 넣으려고 한다.

-- client 1
root@localhost:test 16:26:40> select i2 from t3 where i2 between 4 and 5 for update;
+------+
| i2   |
+------+
|    4 |
|    5 |
+------+
2 rows in set (0.00 sec)
 
root@localhost:test 16:26:44>
-- client 2
root@localhost:test 16:24:53> insert into t3 values( 6,6 );
-- lock 확인
---TRANSACTION 3456, ACTIVE 8 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 15, OS thread handle 140698940942080, query id 316 localhost root update
insert into t3 values( 6,6 )
------- TRX HAS BEEN WAITING 8 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10 page no 5 n bits 80 index idx1 of table `test`.`t3` trx id 3456 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000007; asc     ;;
 1: len 4; hex 80000007; asc     ;;

이 처럼 lock wait 가 발생하게 된다. 하지만 다음 gap 보다 다음 범위로 insert 를 수행하는 경우 lock wait 가 발생하지 않는다.

root@localhost:test 16:27:36> insert into t3 values( 10,10 );
Query OK, 1 row affected (0.00 sec)
 
root@localhost:test 16:27:47>

 

Insert Intention Locks

gap lock 의 일종으로 레코드 insert 이전에 발생하며 의도를 나타내는 잠금이다. 이 잠금은 서로 다른 트랜잭션이

같은 인덱스 레코드에 대해 서로 다른 위치에 레코드를 insert 할때 서로 기다릴 필요가 없음을 나타낸다.

 

아래 예제는 intention lock 을 발생하지만 위치가 다를 경우 wait 를 발생하지 않음을 나타낸다.

-- client1
root@localhost:test 12:01:37> select * from t3;           
+----+------+
| i1 | i2   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  4 |    4 |
|  7 |    7 |
|  9 |    9 |
+----+------+
5 rows in set (0.00 sec)
 
root@localhost:test 12:01:39> insert into t3 values( 5,5 );
Query OK, 1 row affected (0.00 sec)
 
 
-- client2
root@localhost:test 12:01:34>  insert into t3 values(6,6 );
Query OK, 1 row affected (0.00 sec)
 
 
-- lock 조회
root@localhost:(none) 12:01:52> select thread_id, object_schema, object_name, lock_type, lock_mode from performance_schema.data_locks;
+-----------+---------------+-------------+-----------+-----------+
| thread_id | object_schema | object_name | lock_type | lock_mode |
+-----------+---------------+-------------+-----------+-----------+
|        63 | test          | t3          | TABLE     | IX        |
|        64 | test          | t3          | TABLE     | IX        |
+-----------+---------------+-------------+-----------+-----------+

 

하지만 한쪽에서 for update 를 할 경우 lock wait 가 발생한다.

-- client1
root@localhost:test 12:04:22> select i1 from t3 where i1 > 4 for update;
+----+
| i1 |
+----+
|  7 |
|  9 |
+----+
2 rows in set (0.00 sec)
 
 
-- client2
root@localhost:test 12:04:25>  insert into t3 values(6,6 );
 
 
-- lock 조회
+-----------+---------------+-------------+-----------+------------------------+------------------------+
| thread_id | object_schema | object_name | lock_type | lock_mode              | lock_data              |
+-----------+---------------+-------------+-----------+------------------------+------------------------+
|        63 | test          | t3          | TABLE     | IX                     | NULL                   |(insert)
|        63 | test          | t3          | RECORD    | X,GAP,INSERT_INTENTION | 7                      |(insert)
|        64 | test          | t3          | TABLE     | IX                     | NULL                   |
|        64 | test          | t3          | RECORD    | X                      | supremum pseudo-record |
|        64 | test          | t3          | RECORD    | X                      | 7                      |
|        64 | test          | t3          | RECORD    | X                      | 9                      |
+-----------+---------------+-------------+-----------+------------------------+------------------------+
6 rows in set (0.00 sec)
6 rows in set (0.00 sec)
 
 
... ...
---TRANSACTION 3573, ACTIVE 50 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 23, OS thread handle 140698940352256, query id 436 localhost root update
insert into t3 values(6,6 )
------- TRX HAS BEEN WAITING 50 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10 page no 4 n bits 80 index PRIMARY of table `test`.`t3` trx id 3573 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

 

AUTO-INC Locks

auto_increment 컬럼에 테이블에 레코드를 삽입하는 트랜잭션에 의해 발생하는 table 수준의 잠금이다.

innodb_autoinc_lock_mode 를 통해 auto_increment 잠금에 사용되는 알고리즘을 제어할 수 있다.

auto_increment 의 잠금 모드

0 : traditional

1: consecutive ( MySQL 5.7 default )

2: interleaved ( MySQL 8.0 default)

0,1 은 bulk insert, insert ... select 등 대량의 insert 작업에서 auto_inc table lock 을 시도하지만 2 는 table lock 을 잡지 않음

단점 : 복수의 tx 에 의해 autoinc 가 발생하는 경우 대량의 insert 의 autoinc 값에 갭이 발생할 수 있으며 SQL based replication 에서 안전하지 않음

장점 : table lock 회피로 동시성이 향상됨 row based replication 에서는 영향을 주지 않는다.

 

Predicate Locks for Spatial Indexes

생략

MySQL Transaction

InnoDB Transaction Model

mysql 은 SQL:1992 표준에 기술된 모든 isolation 을 지원한다.

READ UNCOMMITTED 

• 기본 동작은 READ COMMITTED 
• dirty read 가 발생할 수 있다.

READ COMMITTED 

• index reocrd 에만 잠금을 행하므로 gap lock 은 disable 된다.
• 이는 phantom row 문제를 발생 시킬 수 있다.

특이 예제 index(b) 로 인해 session b 에서 lock 대기가 발생한다.

CREATE TABLE t6 (a INT NOT NULL, b INT, c INT, INDEX (b));
INSERT INTO t6 VALUES (1,2,3),(2,2,4);
COMMIT;
 
# Session A
UPDATE t6 SET b = 3 WHERE b = 2 AND c = 3;
 
# Session B (lock wait 발생 )
UPDATE t6 SET b = 4 WHERE b = 2 AND c = 4;

 

REPEATABLE READ

• transaction 내에서 처음 read 기준으로 transaction 이 끝날때까지 snapshot 이 유지된다.
• search condition 에 unique index 가 사용되면 found record 만 잠금을 시도한다.
• 다른 search condition 에서 index range scan 을 시도할때 gap lock 혹은 next key lock 을 통해 다른 세션에 의해 gap 에 데이터가 삽입되는것을 방지 한다.

SERIALIZABLE

• 기본 동작은 REPEATABLE READ 과 유사
• REPEATABLE READ 가 안정성과 유연성 사이의 균형이라면
• SERIALIZABLE 좀 더 안정성에 치우침
• SELECT 는 전부 SELECT ... FOR SHARE 로 변경되어 실행된다.

Phandom Rows

같은 트랜잭션내에서 동일 SELECT 를 두번 했을때 처음과 두번째의 result set 이 다른 현상을 말한다.

create table t10( id int, name varchar(10) );
insert into t10 values( 5, 'chlee5'),( 10 , 'chlee10' );
commit;
 
 
-- session1
select * from t10 where id > 1;
+------+---------+
| id   | name    |
+------+---------+
|    5 | chlee5  |
|   10 | chlee10 |
+------+---------+
 
 
-- session2
insert into t10 values( 4, 'chlee4' );
commit;
 
 
-- session1
select * from t10 where id > 1;
+------+---------+
| id   | name    |
+------+---------+
|    5 | chlee5  |
|   10 | chlee10 |
|    4 | chlee4  |
+------+---------+

 

Deadlocks in InnoDB

• innodb_deadlock_detect 로 on/off 할 수 있다.
• deadlock 감지시 자동으로 rollback 된다.
• show engine innodb status 에서 latest 로그만 볼 수 있다.
• innodb_print_all_deadlocks 변수로 모든 deadlock 로그를 error log 파일에 출력 가능하다.

Transaction Scheduling

• FIFO algorithm 
• CAST algorithm