MySQL Transaction
MySQL의 저장엔진
MyISAM : MySQL 5.1까지의 기본 저장엔진. Transaction 지원안됨
InnoDB : MySQL 5.5까지의 기본 저장엔진. Transaction 지원함.
MySQL 5.5 이후에는 테이블을 디폴트로 설정으로 생성하면 저장엔진은 InnoDB 으로 설정된다
지원하는 모든 저장엔진 확인하기 ( MySQL8 )
SHOW ENGINES;
현재 생성된 테이블에 적용된 저장엔진의 종류 확인하기
SHOW CREATE TABLE [테이블 이름];
'emp',
'CREATE TABLE `emp` (\n
`id` int(11) NOT NULL AUTO_INCREMENT,\n
`ename` varchar(255) COLLATE utf8_bin DEFAULT NULL,\n
`parent_id` int(11) DEFAULT \'0\',\n
PRIMARY KEY (`id`)\n
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COLLATE=utf8_bin'
자동커밋 설정
SET AUtOCOMMIT=1; // 각 SQL 문장 실행시 마다 즉시 영구반영됨
SET AUTOCOMMIT=0; // 각 SQL 문장 실행시에 즉시 반영 안되고 COMMIT, ROLLBACK 명령시에 영구반영됨
MySQL은 디폴트로 SET AUTOCOMMIT=1; 상태이기 때문에 모든 문장마다 자동으로 COMMIT 이 실행되어 영구적으로 반영이 되고 있는 상태이다
현재의 AUTOCOMMIT 설정 상태 확인
SELECT @@AUTOCOMMIT;
MySQL에서 다수개의 SQL 문장으로 한개의 트랜잭션을 설정하려면 다음과 같이 할 수 있다
START TRANSACTION; 명령을 사용하는 경우
SET AUTOCOMMIT=1; 으로 설정된 디폴트 상태에서 아래처럼 특정 구간에서만 일시적인 트랜잭션을 설정할 수 있다
START TRANSACTION 이 실행되면, SET AUTOCOMMIT=0 으로 자동설정되고 마지막에 COMMIT이나, ROLLBACK을 호출하면 다시 자동으로 SET AUTOCOMMIT=1 으로 설정된다
START TRANSACTION; // 첫번째 트랜잭션
sql1, sql2, ... // 다수개의 SQL문장을 실행한다
COMMIT; // 혹은 ROLLBACK; // 첫번째 트랜잭션 완료
START TRANSACTION; // 두번째 트랜잭션
sql1, sql2, ... // 다수개의 SQL문장을 실행한다
COMMIT; // 혹은 ROLLBACK; // 두번째 트랜잭션 완료
위의 방법을 사용하여 트랜잭선을 실행하고 나면(COMMIT, ROLLBACK 할 때) 다시 자동커밋으로 복원된다
그러므로 이후에는 SQL 한문장마다 커밋이 실행되어 실수로 COMMIT 깜박 잊더라도 모든 SQL 문장이 영구적으로 반영된다
START TRANSACTOIN; 명령을 사용하지 않는 경우
SETAUTOCOMMIT=0; 으로 설정을 변경하여 각 문장이 반영되지 않도록 한 경우
SET AUTOCOMMIT=0; // 자동커밋을 해제한다
sql1 실행, sql2 실행, ...
COMMIT; // 혹은 ROLLBACK; // 첫번째 트랜잭션 완료
sql1 실행, sql2 실행, ...
COMMIT; // 혹은 ROLLBACK; // 두번째 트랜잭션 완료
SET AUTOCOMMIT=1; // 원래의 자동커밋으로 복원한다
위의 방법을 사용할 때 주의할 점은 마지막에 SET AUTOCOMMIT=1; 부분을 설정하지 않으면 이후의 SQL문장이 반영되지 않기 때문에 작업을 끝내기 전에 반드시 COMMIT; 명령을 해야만 실수로 작업내용을 잃지 않게 된다
ROLLBACK으로 회복할 수 없는 작업(DDL)
ROLLBACK명령은 DML에 해당하는 SQL 문장을 취소하므로 아래와 같은 DDL에는 효과가 없으므로 주의해야 한다
CREATE ~
ALTER ~
DROP ~
START TRANSACTION 명령으로 트랜잭션을 설정하는 예
START TRANSACTION; // 트랜잭션 시작
DELETE FROM emp WHERE id != -100; // 아이디가 음수가 아니면 모두 삭제, 다른 세션에서 emp 수정 불가
SELECT * FROM emp; // 모든 내용이 삭제된 것을 확인함
ROLLBACK; // 트랜잭션 취소
SELECT * FROM emp; // 데이터가 다시 복원된 것을 확인함
START TRANSACTION을 사용하지 않고 트랜잭션을 설정하는 예
SET AUTOCOMMIT=0;
DELETE FROM emp WHERE id != -100;
SELECT * FROM emp;
ROLLBACK;
SET AUTOCOMMIT=1;
SELECT * FROM emp;
MySQL Workbench에서 트랜잭션 테스트
START TRANSACTION;
SELECT @num:=num, @title:=title, @regdate:=regdate, @hit:=hit
FROM bbs3 WHERE num=4;
DELETE FROM bbs3 WHERE num=4;
INSERT INTO delete_history VALUES(@num,@title,@regdate,NOW(),@hit);
SELECT * FROM bbs3;
ROLLBACK; # COMMIT;
PHP에서 MySQL 트랜잭션을 사용하는 예
MySQL에서 테이블 생성
CREATE TABLE bbs3
(
num INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(50) NOT NULL,
regdate DATETIME,
hit INT DEFAULT 0
);
INSERT INTO bbs3 VALUES
(NULL, '1번째 글', '2005-10-24 12:25:12', 0),
(NULL, '2번째 글', '2006-11-23 13:24:24', 0),
(NULL, '3번째 글', '2007-12-22 14:23:36', 0),
(NULL, '4번째 글', '2008-01-21 15:22:45', 0),
(NULL, '5번째 글', '2009-02-20 16:21:58', 0);
SELECT * FROM bbs3;
CREATE TABLE delete_history
(
num INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(50) NOT NULL,
regdate DATETIME,
deldate DATETIME,
hit INT DEFAULT 0
);
transaction01.php
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>트랜잭션 테스트</title>
</head>
<body>
<?php
$host='fdb24.awardspace.net';
$user='2915954_sampledb';
$pass='2915954_sampledb_pwd';
$db='2915954_sampledb';
$conn = mysqli_connect($host,$user,$pass,$db);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
if($conn) {
echo "Connection successful";
echo "<br>";
}
mysqli_query($conn, "set session character_set_connection=utf8;");
mysqli_query($conn, "set session character_set_results=utf8;");
mysqli_query($conn, "set session character_set_client=utf8;");
// 트랜잭션 시작 (MYSQLI_TRANS_START_READ_ONLY 도 가능)
// 데이터 읽기만 할 때와 수정, 삭제할 때를 구분하여 지정함
mysqli_begin_transaction($conn, MYSQLI_TRANS_START_READ_WRITE);
$sql1 = "SELECT @num:=num, @title:=title, @regdate:=regdate, @hit:=hit
FROM bbs3 WHERE num=4";
$sql2 = "DELETE FROM bbs3 WHERE num=4";
$sql3 = "INSERT INTO delete_history VALUES(@num,@title,@regdate,NOW(),@hit)";
$result1 = mysqli_query($conn, $sql1);
$result2 = mysqli_query($conn, $sql2);
$result3 = mysqli_query($conn, $sql3);
if( mysqli_num_rows($result1) > 0 && $result2 && $result3)
{
mysqli_commit($conn);
echo "트랜잭션 성공<br>";
}else
{
mysqli_rollback($conn);
echo "트랜잭션 실패<br>";
}
// 위에서 실행된 트랜잭션이 정상적으로 작동했는지 확인해본다
$result_set = mysqli_query($conn, "SELECT * FROM bbs3");
if (mysqli_num_rows($result_set) > 0) {
while($row = mysqli_fetch_assoc($result_set)) {
echo $row["num"]."<br>";
}
} else {
echo "0 results";
}
mysqli_close($conn);
?>
</body>
</html>
트랜잭션과 LOCK IN SHARE MODE, FOR UPDATE
# SELECT 문장 오른쪽에 LOCK IN SHARE MODE, FOR UPDATE 를 사용할 수 있다
# LOCK IN SHARE MODE : 트랜잭션이 종료할 때까지 다른 세션에서 현재 행을 변경하지 못하고 읽기만 가능
# FOR UPDATE : 트랜잭션이 종료할 때까지 현재행을 다른 세션에서는 읽을 수도 없고 변경할 수도 없다
SELECT col FROM player WHERE id=1 LOCK IN SHARE MODE;
SELECT col FROM player WHERE id=1 FOR UPDATE;
