MySQL
트랜잭션
수색…
거래 시작
트랜잭션은 select, insert, update 또는 delete와 같은 순차적 인 SQL 문의 그룹이며 하나의 단일 작업 단위로 수행됩니다.
즉, 그룹 내의 개별 작업이 성공하지 않으면 트랜잭션이 완료되지 않습니다. 트랜잭션 내에서 조작이 실패하면 전체 트랜잭션이 실패합니다.
은행 거래가 이것을 설명하는 가장 좋은 예입니다. 두 계정 간의 이전을 고려하십시오. 이를 수행하려면 다음을 수행하는 SQL 문을 작성해야합니다.
- 첫 번째 계정에서 요청 된 금액의 가용성 확인
- 첫 번째 계정에서 요청한 금액을 차감합니다.
- 두 번째 계정에 입금하십시오.
이러한 프로세스가 실패하면 전체를 이전 상태로 되돌려 야합니다.
ACID : 거래의 속성
거래에는 다음 네 가지 표준 속성이 있습니다.
- Atomicity : 작업 단위 내의 모든 작업이 성공적으로 완료되었는지 확인합니다. 그렇지 않으면 트랜잭션은 실패 지점에서 중단되고 이전 작업은 이전 상태로 롤백됩니다.
- 일관성 : 성공적으로 커밋 된 트랜잭션이 발생할 때 데이터베이스가 상태를 올바르게 변경하도록합니다.
- 격리 : 트랜잭션이 서로 독립적으로 작동하고 투명하게 작동 할 수 있습니다.
- 내구성 : 시스템 오류가 발생해도 커밋 된 트랜잭션의 결과 또는 결과가 유지되도록합니다.
트랜잭션은 START TRANSACTION 또는 BEGIN WORK 문으로 START TRANSACTION 하고 COMMIT 또는 ROLLBACK 문으로 끝납니다. 시작 및 종료 문 사이의 SQL 명령은 대량의 트랜잭션을 구성합니다.
START TRANSACTION;
SET @transAmt = '500';
SELECT @availableAmt:=ledgerAmt FROM accTable WHERE customerId=1 FOR UPDATE;
UPDATE accTable SET ledgerAmt=ledgerAmt-@transAmt WHERE customerId=1;
UPDATE accTable SET ledgerAmt=ledgerAmt+@transAmt WHERE customerId=2;
COMMIT;
START TRANSACTION 사용하면 COMMIT 또는 ROLLBACK 하여 트랜잭션을 종료 할 때까지 자동 커밋이 비활성화 된 상태로 유지됩니다. 자동 확약 모드는 이전 상태로 되돌아갑니다.
FOR UPDATE 는 트랜잭션 기간 동안 행을 나타내며 잠금합니다.
트랜잭션이 커밋되지 않은 상태로 유지되는 동안이 트랜잭션은 다른 사용자가 사용할 수 없습니다.
거래와 관련된 일반 절차
- SQL 명령
BEGIN WORK또는START TRANSACTION을 실행하여 트랜잭션을 시작하십시오. - 모든 SQL 문을 실행하십시오.
- 귀하의 요구 사항에 따라 모든 것이 실행되었는지 확인하십시오.
- 예인 경우,
COMMIT명령을 발행하십시오. 그렇지 않으면ROLLBACK명령을 발행하여 모든 것을 이전 상태로 되돌립니다. - Galera / PXC를 사용 중이거나 결국 사용할 경우
COMMIT이후에도 오류가 있는지 확인하십시오.
COMMIT, ROLLBACK 및 AUTOCOMMIT
AUTOCOMMIT
MySQL은 트랜잭션의 일부가 아닌 명령문을 자동으로 커밋합니다. BEGIN 또는 START TRANSACTION 이없는 UPDATE , DELETE 또는 INSERT 문의 결과는 모든 연결에서 즉시 볼 수 있습니다.
AUTOCOMMIT 변수는 기본적으로 true 로 설정됩니다. 이것은 다음과 같은 방법으로 변경할 수 있습니다.
--->To make autcommit false
SET AUTOCOMMIT=false;
--or
SET AUTOCOMMIT=0;
--->To make autcommit true
SET AUTOCOMMIT=true;
--or
SET AUTOCOMMIT=1;
AUTOCOMMIT 상태를 보려면
SELECT @@autocommit;
범하다
AUTOCOMMIT 가 false로 설정되고 트랜잭션이 확약되지 않은 경우, 변경 사항은 현재 연결에 대해서만 표시됩니다.
COMMIT .이 테이블에 대한 변경 사항을 확약하면, 결과는 모든 연결에서 볼 수 있습니다.
우리는 이것을 설명하기 위해 두 개의 연결을 고려합니다.
연결 1
--->Before making autocommit false one row added in a new table
mysql> INSERT INTO testTable VALUES (1);
--->Making autocommit = false
mysql> SET autocommit=0;
mysql> INSERT INTO testTable VALUES (2), (3);
mysql> SELECT * FROM testTable;
+-----+
| tId |
+-----+
| 1 |
| 2 |
| 3 |
+-----+
연결 2
mysql> SELECT * FROM testTable;
+-----+
| tId |
+-----+
| 1 |
+-----+
---> Row inserted before autocommit=false only visible here
연결 1
mysql> COMMIT;
--->Now COMMIT is executed in connection 1
mysql> SELECT * FROM testTable;
+-----+
| tId |
+-----+
| 1 |
| 2 |
| 3 |
+-----+
연결 2
mysql> SELECT * FROM testTable;
+-----+
| tId |
+-----+
| 1 |
| 2 |
| 3 |
+-----+
--->Now all the three rows are visible here
롤백
쿼리 실행에 문제가 발생하면 ROLLBACK 을 사용하여 변경 내용을 되돌립니다. 아래 설명을 참조하십시오.
--->Before making autocommit false one row added in a new table
mysql> INSERT INTO testTable VALUES (1);
--->Making autocommit = false
mysql> SET autocommit=0;
mysql> INSERT INTO testTable VALUES (2), (3);
mysql> SELECT * FROM testTable;
+-----+
| tId |
+-----+
| 1 |
| 2 |
| 3 |
+-----+
이제 ROLLBACK
--->Rollback executed now
mysql> ROLLBACk;
mysql> SELECT * FROM testTable;
+-----+
| tId |
+-----+
| 1 |
+-----+
--->Rollback removed all rows which all are not committed
COMMIT 가 실행되면 ROLLBACK 은 아무 것도 발생시키지 않습니다.
mysql> INSERT INTO testTable VALUES (2), (3);
mysql> SELECT * FROM testTable;
mysql> COMMIT;
+-----+
| tId |
+-----+
| 1 |
| 2 |
| 3 |
+-----+
--->Rollback executed now
mysql> ROLLBACk;
mysql> SELECT * FROM testTable;
+-----+
| tId |
+-----+
| 1 |
| 2 |
| 3 |
+-----+
--->Rollback not removed any rows
AUTOCOMMIT 가 true 로 설정된 경우 COMMIT 및 ROLLBACK 은 쓸모가 없습니다.
JDBC 드라이버를 사용한 트랜잭션
JDBC 드라이버를 사용하는 트랜잭션은 트랜잭션이 커밋 및 롤백해야하는 방법과시기를 제어하는 데 사용됩니다. MySQL 서버 연결은 JDBC 드라이버를 사용하여 생성됩니다.
MySQL 용 JDBC 드라이버는 여기에서 다운로드 할 수 있습니다.
JDBC 드라이버를 사용하여 데이터베이스에 연결하는 것으로 시작하자.
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection(DB_CONNECTION_URL,DB_USER,USER_PASSWORD);
--->Example for connection url "jdbc:mysql://localhost:3306/testDB");
문자 집합 : 클라이언트가 SQL 문을 서버에 보낼 때 사용할 문자 집합을 나타냅니다. 또한 서버가 결과를 클라이언트에 보내기 위해 사용해야하는 문자 집합을 지정합니다.
서버에 연결하는 동안 언급해야합니다. 그래서 연결 문자열은 같아야합니다.
jdbc:mysql://localhost:3306/testDB?useUnicode=true&characterEncoding=utf8
문자 집합 및 데이터 정렬 에 대한 자세한 내용은이 부분을 참조하십시오.
연결을 열면 AUTOCOMMIT 모드가 기본적으로 true 로 설정되며 트랜잭션을 시작하려면 false 로 변경해야합니다.
con.setAutoCommit(false);
연결을 연 직후에는 항상 setAutoCommit() 메소드를 호출해야합니다.
그렇지 않으면 START TRANSACTION 또는 BEGIN WORK 를 사용하여 새 트랜잭션을 시작하십시오. START TRANSACTION 또는 BEGIN WORK 를 사용하면 AUTOCOMMIT false 로 변경할 필요가 없습니다. 자동으로 사용 중지됩니다.
이제 거래를 시작할 수 있습니다. 아래의 전체 JDBC 트랜잭션 예를 참조하십시오.
package jdbcTest;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class accTrans {
public static void doTransfer(double transAmount,int customerIdFrom,int customerIdTo) {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
String DB_CONNECTION_URL = "jdbc:mysql://localhost:3306/testDB?useUnicode=true&characterEncoding=utf8";
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(DB_CONNECTION_URL,DB_USER,USER_PASSWORD);
--->set auto commit to false
con.setAutoCommit(false);
---> or use con.START TRANSACTION / con.BEGIN WORK
--->Start SQL Statements for transaction
--->Checking availability of amount
double availableAmt = 0;
pstmt = con.prepareStatement("SELECT ledgerAmt FROM accTable WHERE customerId=? FOR UPDATE");
pstmt.setInt(1, customerIdFrom);
rs = pstmt.executeQuery();
if(rs.next())
availableAmt = rs.getDouble(1);
if(availableAmt >= transAmount)
{
---> Do Transfer
---> taking amount from cutomerIdFrom
pstmt = con.prepareStatement("UPDATE accTable SET ledgerAmt=ledgerAmt-? WHERE customerId=?");
pstmt.setDouble(1, transAmount);
pstmt.setInt(2, customerIdFrom);
pstmt.executeUpdate();
---> depositing amount in cutomerIdTo
pstmt = con.prepareStatement("UPDATE accTable SET ledgerAmt=ledgerAmt+? WHERE customerId=?");
pstmt.setDouble(1, transAmount);
pstmt.setInt(2, customerIdTo);
pstmt.executeUpdate();
con.commit();
}
--->If you performed any insert,update or delete operations before
----> this availability check, then include this else part
/*else { --->Rollback the transaction if availability is less than required
con.rollback();
}*/
} catch (SQLException ex) {
---> Rollback the transaction in case of any error
con.rollback();
} finally {
try {
if(rs != null) rs.close();
if(pstmt != null) pstmt.close();
if(con != null) con.close();
}
}
}
public static void main(String[] args) {
doTransfer(500, 1020, 1021);
-->doTransfer(transAmount, customerIdFrom, customerIdTo);
}
}
JDBC 트랜잭션은 트랜잭션 블록 내의 SQL 문 중 하나가 실패하면 트랜잭션 블록 내의 모든 SQL 문을 성공적으로 실행하고, 트랜잭션 블록 내의 모든 것을 중단하고 롤백합니다.
