Autocommit은 언제 끄는 게 좋을까?

Autocommit은 말 그대로 사용자가 SQL statement를 날렸을 시 자동으로 트랜잭션을 시작하고 커밋하는 기능이다. SQL statement 실행 시 Autocommit이 켜진 상태로 오류가 발생했다면, 스토리지 엔진의 에러 핸들링에 따라 적절한 에러처리 과정을 진행한다.

Autocommit은 하나의 SQL statement가 실행될 때마다 자동으로 트랜잭션을 키고 종료한다는 점에서 MySQL 서버에 부하를 주는 행동이라고 볼 수 있다.

또한 가장 큰 문제점은 Commit 된 데이터는 Rollback이 불가하기 때문에, 데이터를 복구하려면 다른 방법을 써야 한다.(Data Recovery를 쓰면 된다고 합니다. 하지만 결과적으로 MySQL 서버 자체만으로는 해결이 어려운 거 같습니다.)

그러면 Autocommit을 끄면 되는 거 아니냐라고 생각할 수 있지만, Autocommit을 끄게 되면, 언제든지 트랜잭션이 켜져있는 상황이 되고, 이는 여러 버전의 MVCC를 만들게 되며, 여러 버전의 MVCC는 MySQL의 성능 부하로 이어질 수 있다.(MVCC는 INNODB만 만듭니다. 반대로 말하면 다른 스토리지 엔진은 괜찮다고 볼 수 있습니다.).

또한 Autocommit을 끄게 된다면, 사용자가 일일이 COMMIT을 통해 시작된 트랜잭션의 작업을 확정지어야 되고 이러한 점은 휴먼 에러가 나는 지점이라고 볼 수 있다.

가장 좋은 방법은 Autocommit이 켜진 상태로 START TRANSACTION을 활용하는 방법이라고 생각했다.

START TRANSACTION을 사용하게 되면, 그 직후 나오는 SQL Statement들은 바로 Commit 되지 않고 하나의 트랜잭션으로 묶이게 된다.

이렇게 하면 기존의 문제점을 방지할 수 있다. 더군다나 휴먼에러도 방지할 수 있다.

하지만, 대용량 데이터를 삽입할 경우에는 Autocommit을 꺼두는 게 좋다.(아니면 START TRANSACTION을 사용해도 됩니다.) 대용량 데이터를 삽입할 때 Autocommit이 켜진 상태로 그냥 INSERT 문을 쓰게 된다면 하나의 행마다 트랜잭션이 시작되고 종료되면서 데이터 삽입 속도에 영향을 줄 수 있기 때문이다.

MySQL :: MySQL 8.4 Reference Manual :: 17.7.2.2 autocommit, Commit, and Rollback

Autocommit Mode in MySQL Baeldung on SQL

iLiFO 아이리포 :: 실수로 데이터를 대량 업데이트했어요. 어떻게 해야하나요?OTL