MySQL commit 기능 활성화, 사용 하는 방법

mysql은 기본 설정으로 autocommit이 활성화 되어 있기 때문에 자동으로 커밋을 완료합니다. 그래서 mysql은 커밋이 없다고 오해하실 수 있는데요. mysql에서도 commit 기능을 사용할 수 있습니다.

MySQL commit

autocommit 활성화 상태 확인

@@autocommit 값을 체크해봅니다.

MariaDB [(none)]> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.000 sec)

• 1 : 오토 커밋 활성화
• 0 : 오토 커밋 비활성화

1 이면 쿼리가 자동으로 커밋되는 상태입니다.

autocommit 비활성화

오토 커밋을 비활성화 하면 수동으로 commit 을 사용할 수 있습니다.

MariaDB [classicmodels]> set autocommit = 0;
Query OK, 0 rows affected (0.016 sec)

MariaDB [classicmodels]> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.004 sec)

테이블을 생성하고 데이터를 넣어보겠습니다.

MariaDB [classicmodels]> CREATE TABLE test_table (
    ->     id INT AUTO_INCREMENT PRIMARY KEY,
    ->     name VARCHAR(50)
    -> );

MariaDB [classicmodels]> INSERT INTO test_table (name) VALUES ('Alice');
Query OK, 1 row affected (0.027 sec)

MariaDB [classicmodels]> INSERT INTO test_table (name) VALUES ('Bob');
Query OK, 1 row affected (0.001 sec)

커밋을 하기 전 데이터를 확인해봅니다.

MariaDB [classicmodels]> select * from test_table;
+----+-------+
| id | name  |
+----+-------+
|  1 | Alice |
|  2 | Bob   |
+----+-------+
2 rows in set (0.013 sec)

뭐지? 커밋 안 했는데 왜 들어가 있지? 생각할 수 있지만 이건 현재 연결된 세션이기 때문에 보이는 겁니다.

로그아웃하고 다른 세션으로 접속해서 보면 데이터가 반영되지 않은 걸 알 수 있습니다.

MariaDB [classicmodels]> exit
Bye
[root@localhost ~]# mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.11.8-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use classicmodels;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [classicmodels]> select * from test_table;
Empty set (0.000 sec)

rollback 하기

commit을 사용하는 목적이 원할 때 rollback을 하는 것이니 이 부분에 대한 테스트도 해보겠습니다.

먼저 데이터를 넣고

ariaDB [classicmodels]> INSERT INTO test_table (name) VALUES ('Alice');
Query OK, 1 row affected (0.000 sec)

MariaDB [classicmodels]> INSERT INTO test_table (name) VALUES ('Bob');
Query OK, 1 row affected (0.000 sec)

조회하면 잘 조회됩니다.

MariaDB [classicmodels]> select * from test_table;
+----+-------+
| id | name  |
+----+-------+
|  5 | Alice |
|  6 | Bob   |
+----+-------+
2 rows in set (0.000 sec)

롤백하면 정상적으로 초기화된 걸 볼 수 있습니다.

MariaDB [classicmodels]> rollback;
Query OK, 0 rows affected (0.001 sec)

MariaDB [classicmodels]> select * from test_table;
Empty set (0.000 sec)

롤백하지 않고 변경 사항을 확정하려면 commit 하면 됩니다.

MariaDB [classicmodels]> commit;

!!autocommit 활성화 시 주의사항!!

set autocommit=0

으로 값을 설정하면 해당 세션에서만 오토 커밋이 비활성화 됩니다. 세션을 다시 연결하면 기본값인 0으로 돌아갑니다. 혹시 다른 세션에서 오토 커밋이 활성화 되어 있는 줄 착각하면 롤백을 못할 수 있으니 주의합니다.

다른 세션에서도 0 상태를 유지하고 싶으면 전역 설정을 변경해야 합니다.

set global autocommit=0

다만 이 방법은 mysql 서버를 재시작하면 다시 기본값인 0으로 돌아갑니다.

영구적으로 autocommit을 비활성화 하려면 my.cnf 설정에 값을 넣고 mysql 서비스를 재시작해야 합니다.

[mysqld]
autocommit=0