MySQL에 이런 속성이? AUTOCOMMIT

대호 Dorant·2024년 12월 10일

오늘은 지이이이이인짜 너무 바쁜 하루였습니다.

왜냐하면! 고객사에서 자사 솔루션에 버그 수정 요청이 들어왔기 때문이었죠...

이슈는 자사 솔루션 동작 후에 고객사 DB에 row가 추가가 되어야하는데, 솔루션 동작 후에 DB를 확인해보면 해당 row가 없다는 겁니다...! 예...?

일단! 뭘 먼저 해야할까요? 로그를 까봐야겠죠? 여기는 아무 문제가 없었고! 그 다음에는 DB에 row가 추가되기 전, 동작을 확인해봤는데 여기도 문제가 없었습니다... 끝없는 탐색 중, mysql의 AUTOCOMMIT 이라는 속성을 발견했고, 고객사에 문의해보니 고객사 데이터베이스의 해당 속성이 false로 설정되어있는 상태라는 것을 확인하게 되었습니다!

여기서 AUTOCOMMIT 은 무엇인가?

MySQL의 AUTO COMMIT 속성은 각 SQL 문이 실행된 후 자동으로 트랜잭션을 커밋할지 여부를 제어하는 속성입니다.
기본 값은 true로, 실행된 SQL 문이 자동으로 데이터베이스에 영구 저장됩니다. 만약, false로 설정을 한다면, 수동으로 트랜잭션을 제어할 수 있습니다.

MySQL AUTOCOMMIT 설정은 어떻게 할까?

1. SQL 쿼리문을 사용하는 방법

MySQL에서 세션 또는 전역(global) 수준에서 AUTO COMMIT을 설정할 수 있습니다!

세션 수준 설정 (현재 연결에만 적용)

SET AUTOCOMMIT = 0; -- AUTO COMMIT 비활성화
SET AUTOCOMMIT = 1; -- AUTO COMMIT 활성화 (Default)

전역 수준 설정 (서버 전체에 적용)

SET GLOBAL AUTOCOMMIT = 0; -- AUTO COMMIT 비활성화
SET GLOBAL AUTOCOMMIT = 1; -- AUTO COMMIT 활성화 (Default)

⚠️ 전역 설정을 변경하려면 관리자 권한이 필요하며, 이미 연결된 세션에는 적용되지 않습니다. 전역 설정을 변경한 후의 연결에만 적용이 됩니다.

2. MySQL 설정 파일 (my.cnf 또는 my.ini)을 수정하는 방법

설정 파일 열기 (my.cnf 또는 my.ini)
- 리눅스: /etc/my.cnf 또는 /etc/mysql/my.cnf
- 윈도우: MySQL 설치 디렉토리의 my.ini
💡현재 실행되고 있는 MySQL 서버의 데이터 디렉토리 경로 확인 방법💡
```
SHOW VARIABLES LIKE 'datadir';
```
- 위의 SQL 쿼리를 실행하여 MySQL 서버가 사용하고 있는 데이터 디렉토리를 확인할 수 있습니다!
- datadir 변수는 MySQL 서버가 사용하는 데이터 파일과 로그 파일이 저장되는 디렉토리 경로입니다.

[mysqld] 섹션에 AUTO COMMIT 설정 추가 또는 수정

제 경우에는 my.ini 파일에 해당 옵션이 없어서 추가를 해주었습니다. 없다면 기본 값인 1로 설정이 되어있는 것입니다.
```
[mysqld]
autocommit=0 	# AUTO COMMIT 비활성화
```

MySQL 서버를 재시작하여 변경 사항 적용
- 리눅스:
```
sudo systemctl restart mysql
```
- 윈도우: 서비스 관리자에서 MySQL 서비스 재시작

고객사의 AUTO COMMIT 설정값은 false 였기 때문에, 제 로컬 환경의 mysql의 my.ini 를 수정하여 해당 값을 false로 설정하여 동일한 환경을 셋팅했어요. 실제 코드는 보여드릴 수 없고! 아래 예시 코드와 같이 "쿼리 실행 -> commit" 을 시도해봤어요.

const mysql = require('mysql2/promise');

// 커넥션 풀 생성
const pool = mysql.createPool({
    host: 'localhost',      // MySQL 서버 호스트
    user: 'root',           // 사용자 이름
    password: 'yourpassword', // 비밀번호
    database: 'yourdatabase', // 사용할 데이터베이스
});

async function insertData() {
    let connection;
    try {
        // 풀에서 커넥션 가져오기
        connection = await pool.getConnection();
        
        // 트랜잭션 시작
        await connection.beginTransaction();

        // INSERT 쿼리 실행
        const [result] = await connection.execute(
            'INSERT INTO users (name, email) VALUES (?, ?)',
            ['John Doe', 'john.doe@example.com']
        );
        console.log('데이터 삽입 완료:', result);

        // 커밋
        await connection.commit();
        console.log('트랜잭션 커밋 완료');

    } catch (error) {
        // 오류 발생 시 롤백
        if (connection) {
            await connection.rollback();
        }
        console.error('트랜잭션 오류, 롤백 수행:', error);
        throw error;  // 오류를 상위로 전파

    } finally {
        // 커넥션을 풀로 반환
        if (connection) {
            connection.release();
        }
    }
}

// 함수 호출
insertData()
    .catch(console.error)
    .finally(() => {
        // 애플리케이션 종료 시 풀 종료
        pool.end();
    });