Trouble Shooting

%%sql

CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
)
;

%%sql

SAVEPOINT create_table;

INSERT INTO user (name)
VALUES ('말랑카우'), ('춘식이')
;

SAVEPOINT add_initial_user;

%%sql

DELETE FROM user
WHERE name = '말랑카우'
;

SAVEPOINT delete_malrangcow;

%%sql

ROLLBACK TO add_initial_user;

COMMIT;

SQL Magic을 통해서 TCL을 작성하던 중 SAVEPOINT를 탐색하지 못하는 문제가 발생했다.

원인 분석

error_msg

오류로 보아 SAVEPOINT 기능 자체를 지원하지 않는 것은 아니고 각각의 명령어가 실행된 이후에 트랜잭션이 종료되어 SAVEPOINT가 초기화되는 것으로 보인다.

명령어들의 하나의 셀로 다시 작성했지만…

SAVEPOINT create_table;

INSERT INTO user (name)
VALUES ('말랑카우'), ('춘식이')
;

SAVEPOINT add_initial_user;

DELETE FROM user
WHERE name = '말랑카우'
;

SAVEPOINT delete_malrangcow;

ROLLBACK TO add_initial_user;

COMMIT;

여전히 같은 오류가 발생했다.

BEGIN;
    
SAVEPOINT create_table;

INSERT INTO user (name)
VALUES ('말랑카우'), ('춘식이')
;

SAVEPOINT add_initial_user;

DELETE FROM user
WHERE name = '말랑카우'
;

SAVEPOINT delete_malrangcow;

ROLLBACK TO add_initial_user;

COMMIT;

이를 해결하기 위해서 트랜잭션의 시작을 명시하기 하고자 BEGIN;과 START TRANSACTION;을 사용해보았지만 셀 환경에서는 영향을 주지 못했다.

다음 방법으로 SET AUTOCOMMIT = 0;으로 설정하여 MySQL의 자동커밋 설정을 비활성화 하는 방법을 시도해보았다.

%%sql

SET AUTOCOMMIT = 0;

START TRANSACTION;

SAVEPOINT create_table;

INSERT INTO user (name)
VALUES ('말랑카우'), ('춘식이')
;

SAVEPOINT add_initial_user;

DELETE FROM user
WHERE name = '말랑카우'
;

SAVEPOINT delete_malrangcow;

ROLLBACK TO add_initial_user;

COMMIT;

하지만 여전히 Jupyter Notebook 환경에서는 트랜잭션이 종료되어 SAVEPOINT를 사용할 수 없었다.

포럼에서 관련 글을 찾아 보았지만 어디를 찾아보아도 Magic SQL을 통해 학습하는 나같은 변종은 극소수였기에 원하는 정보를 찾을 수 없었다.

해결책

어쩔 수 없이 결국 코드를 console 환경에서 작성하여 실행하였다.
console에서 전체 코드를 실행하더라도 BEGIN; 또는 START TRANSACTION;을 사용하여 트랜잭션의 시작을 반드시 명시해주어야 함에 주의하자.

CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
)
;

# BEGIN ;

START TRANSACTION;

SAVEPOINT create_table;

INSERT INTO user (name)
VALUES ('말랑카우'), ('춘식이')
;

SAVEPOINT add_initial_user;

DELETE FROM user
WHERE name = '말랑카우'
;

SAVEPOINT delete_malrangcow;

# ROLLBACK TO create_table;
ROLLBACK TO add_initial_user;

COMMIT;

직관적이지는 않지만 직접 Python 코드를 통해 pymysql 라이브러리를 사용해서 명령을 수행하는 방법도 있다.

import pymysql
conn = pymysql.connect(host='your_host', user='your_user', password='your_password', db='your_db', autocommit=False)
try:
    with conn.cursor() as cursor:
        cursor.execute("BEGIN;")
        cursor.execute("INSERT INTO user (name) VALUES ('말랑카우'), ('춘식이');")
        # 필요한 추가 SQL 명령 실행
        conn.commit() # 또는 필요한 지점에서 롤백
except Exception as e:
    print(e)
    conn.rollback()
finally:
    conn.close()