【MySQL 06】트랜잭션 처리

1. 트랜잭션의 ACID 속성

트랜잭션에는 원자성, 일관성, 격리성, 내구성이라는 4가지 특성이 있습니다.

"은행 송금"을 예로 들어 보겠습니다.

• 원자성: 트랜잭션을 구성하는 문은 논리적 단위를 형성하며 그 중 일부만 실행할 수 없습니다. 즉, 거래는 분할할 수 없는 가장 작은 단위입니다. 예: 은행 송금 과정에서 한 계좌에서 이체 금액을 차감하고 동시에 다른 계좌에 추가해야 합니다. 하나의 계좌만 변경하는 것은 불합리합니다.

• 일관성: 트랜잭션 실행 전후에 데이터베이스가 일관됩니다. 즉, 트랜잭션은 시스템 상태를 올바르게 변환해야 합니다. 예: 은행 이체 과정에서 이체 금액이 한 계좌에서 다른 계좌로 이체되거나 두 계좌가 모두 변경되지 않고 그대로 유지되며 다른 상황은 없습니다.

• 격리: 하나의 트랜잭션이 다른 트랜잭션에 영향을 주지 않습니다. 즉, 어떤 트랜잭션에서도 불완전한 상태의 트랜잭션을 보는 것은 불가능합니다. 예를 들어, 은행 송금 중 이체 거래가 제출되기 전에는 다른 이체 거래가 대기 상태에만 있을 수 있습니다.

• 내구성: 트랜잭션 처리 효과를 영구적으로 보존할 수 있습니다. 반면에 트랜잭션은 서버, 프로세스, 통신, 미디어 오류 등을 포함한 모든 오류를 견딜 수 있어야 합니다. 예: 은행 이체 과정에서 이체 후 계좌 상태를 저장해야 합니다.

2. 거래 상태

SET AUTOCOMMIT = 0 , 禁止自动提交 SET AUTOCOMMIT = 1, 开启自动提交
START TRANSACTION：开始事务，autocommit设为0，如果已经有一个事务在运行，则会触发一个隐藏的COMMIT
COMMIT：提交事务，保存更改，释放锁
ROLLBACK：回滚本事务对数据库的所有更改，然后结束事务，释放锁
SAVEPOINT savepoint_name：创建一个savepoint识别符来ROLLBACK TO SAVEPOINT
ROLLBACK TO SAVEPOINT savepoint_name：回滚到从savepoint_name开始对数据库的所有更改，这样就允许回滚事务中的一部分，保证更改的一个子集被提交
SET TRANSACTION：允许设置事务的隔离级别
LOCK TABLES：允许显式的锁住一个或多个table，会隐式的关闭当前打开的事务，建议在执行LOCK TABLES语句之前显式的commit或rollback。
我们一般所以一般在事务代码里不会使用LOCK TABLES

3. 거래 작업

(1) 먼저 직원 데이터 테이블을 생성합니다:

mysql> create table employee(
    -> employeeID char(4),
    -> name varchar(20) not null,
    -> job varchar(20),
    -> departmentID int
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql> insert into employee value ('7513' , 'Nora Edwar' , 'Programmer', 128);
mysql> insert into employee value ('9006' , 'Candy Burn' , 'Systems Ad',128 );
mysql> insert into employee value ( '9842' , 'Ben Smith' ,  'DBA' , 42);
mysql> insert into employee value ('9843',  'Pert Park'  , 'DBA' , 42 );
mysql> insert into employee value ('9845' , 'Ben Patel'  , 'DBA' , 128 );
mysql> insert into employee value ('9846' , 'Red Right' ,  null, 128 );
mysql> insert into employee value ('9847' , 'Run Wild'  ,  null , 128 );
mysql> insert into employee value ('9848' , 'Rip This J' , null , 128 );
mysql> insert into employee value ('9849' , 'Rip This J' , null  , 128 );
mysql> insert into employee value ( '9850' , 'Reader U' ,   null , 128 );
mysql> insert into employee value ('6651',  'Ajay Patel' , 'Programmer', 128 );

mysql> select * from employee;
+------------+------------+------------+--------------+
| employeeID | name       | job        | departmentID |
+------------+------------+------------+--------------+
| 6651       | Ajay Patel | Programmer |          128 |
| 7513       | Nora Edwar | Programmer |          128 |
| 9006       | Candy Burn | Systems Ad |          128 |
| 9842       | Ben Smith  | DBA        |           42 |
| 9843       | Pert Park  | DBA        |           42 |
| 9845       | Ben Patel  | DBA        |          128 |
| 9846       | Red Right  | NULL       |          128 |
| 9847       | Run Wild   | NULL       |          128 |
| 9848       | Rip This J | NULL       |          128 |
| 9849       | Rip This J | NULL       |          128 |
| 9850       | Reader U   | NULL       |          128 |
| 6651       | Ajay Patel | Programmer |          128 |
+------------+------------+------------+--------------+

( 2) SET AUTOCOMMIT=0：

mysql> set autocommit = 0;//禁止自动提交

mysql> insert into employee values(null,'test1',null,128);

mysql> savepoint s1;//创建一个savepoint识别符

mysql> insert into employee values(null,"test2",null,128);

mysql> savepoint s2;//创建一个savepoint识别符

mysql> insert into employee values(null,"test3",null,128);

mysql> savepoint s3;//创建一个savepoint识别符mysql> select * from employee;
+------------+------------+------------+--------------+| employeeID | name       | job        | departmentID |
+------------+------------+------------+--------------+| 6651       | Ajay Patel | Programmer |          128 |
| 7513       | Nora Edwar | Programmer |          128 |
| 9006       | Candy Burn | Systems Ad |          128 |
| 9842       | Ben Smith  | DBA        |           42 |
| 9843       | Pert Park  | DBA        |           42 |
| 9845       | Ben Patel  | DBA        |          128 |
| 9846       | Red Right  | NULL       |          128 |
| 9847       | Run Wild   | NULL       |          128 |
| 9848       | Rip This J | NULL       |          128 |
| 9849       | Rip This J | NULL       |          128 |
| 9850       | Reader U   | NULL       |          128 |
| 6651       | Ajay Patel | Programmer |          128 |
| NULL       | test1      | NULL       |          128 |
| NULL       | test2      | NULL       |          128 || NULL       | test3      | NULL    |    128 |
+------------+------------+------------+--------------+

(3) 저장점으로 롤백：

mysql> rollback to savepoint s1;//回滚到s1标签处：mysql> select * from employee;
+------------+------------+------------+--------------+| employeeID | name       | job        | departmentID |
+------------+------------+------------+--------------+| 6651       | Ajay Patel | Programmer |          128 |
| 7513       | Nora Edwar | Programmer |          128 |
| 9006       | Candy Burn | Systems Ad |          128 |
| 9842       | Ben Smith  | DBA        |           42 |
| 9843       | Pert Park  | DBA        |           42 |
| 9845       | Ben Patel  | DBA        |          128 |
| 9846       | Red Right  | NULL       |          128 |
| 9847       | Run Wild   | NULL       |          128 |
| 9848       | Rip This J | NULL       |          128 |
| 9849       | Rip This J | NULL       |          128 |
| 9850       | Reader U   | NULL       |          128 |
| 6651       | Ajay Patel | Programmer |          128 || NULL       | test1      | NULL       |      128 |
+------------+------------+------------+--------------+

(4) COMMIT：

mysql> commit;//提交事务
mysql> rollback to savepoint s2;
//一旦事务提交了，就不能再回滚ERROR 1305 (42000): SAVEPOINT s2 does not exist

( 5) SET AUTOCOMMIT=1:

mysql> set autocommit = 1;//自动提交事务

mysql>  insert into employee values(null,"test4",null,128);

mysql> savepoint s4;//一旦创建，自动提交mysql> select * from employee;
+------------+------------+------------+--------------+| employeeID | name       | job        | departmentID |
+------------+------------+------------+--------------+| 6651       | Ajay Patel | Programmer |          128 |
| 7513       | Nora Edwar | Programmer |          128 |
| 9006       | Candy Burn | Systems Ad |          128 |
| 9842       | Ben Smith  | DBA        |           42 |
| 9843       | Pert Park  | DBA        |           42 |
| 9845       | Ben Patel  | DBA        |          128 |
| 9846       | Red Right  | NULL       |          128 |
| 9847       | Run Wild   | NULL       |          128 |
| 9848       | Rip This J | NULL       |          128 |
| 9849       | Rip This J | NULL       |          128 |
| 9850       | Reader U   | NULL       |          128 |
| 6651       | Ajay Patel | Programmer |          128 |
| NULL       | test1      | NULL       |          128 |
| NULL       | test2      | NULL       |          128 |
| NULL       | test3      | NULL       |          128 || NULL       | test4      | NULL       |          128 |
+------------+------------+------------+--------------+mysql> rollback to s4;//此时就无法回滚了
ERROR 1305 (42000): SAVEPOINT s4 does not exist

4. 잠금

공유 잠금, 배타적 잠금, 비관적 잠금, 낙관적 잠금, 행 수준 잠금, 테이블 수준 잠금

• 공유 잠금: 데이터를 읽을 때 데이터에 공유 잠금을 추가합니다. 공유는 직접 공유와 충돌하지 않지만 배타적 잠금과 충돌합니다.

• 배타적 잠금: 데이터 업데이트 시 배타적 잠금을 설치하고 다른 모든 작업을 금지합니다.

• 비관적 잠금: 업데이트가 많고 쿼리가 적을 때 사용됩니다. 비관적 잠금은 데이터베이스의 실제 잠금이 아니라 트랜잭션에 대한 사람들의 태도입니다.

• 낙관적 잠금: 업데이트가 거의 없으며 많은 쿼리에 사용됩니다. 낙관적 잠금은 데이터베이스의 실제 잠금이 아니라 트랜잭션에 대한 사람들의 태도입니다.

5. 동시 처리

• 더티 읽기: 한 트랜잭션이 다른 트랜잭션에서 제출하지 않은 데이터를 읽습니다.
  트랜잭션 1: 업데이트 데이터 조각
  ————>트랜잭션 2: 트랜잭션 1이 업데이트한 레코드 읽기
  트랜잭션 1: 제출을 위한 커밋 호출
  이때 트랜잭션 2가 읽은 데이터는 트랜잭션 1에 저장된 데이터입니다. 데이터베이스 메모리, 더티 읽기(dirty reading)라고 합니다.
  읽은 데이터는 더티 데이터입니다
  자세한 설명:
  더티 읽기는 트랜잭션이 데이터에 액세스하고 데이터를 수정했지만 이 수정 사항이 아직 데이터베이스에 제출되지 않은 경우를 의미합니다. ,
  다른 트랜잭션도 이 데이터에 액세스한 후 이 데이터를 사용합니다. 이 데이터는 아직 제출되지 않았기 때문에 다른
  트랜잭션에서 읽은 데이터는 더티 데이터이며 더티 데이터를 기반으로 한 작업이 올바르지 않을 수 있습니다.

• 반복 불가능한 읽기: 동일한 트랜잭션에서 동일한 데이터를 두 번 읽고 내용이 다릅니다
  트랜잭션 1: 레코드 쿼리
  ————- > 트랜잭션 2: 트랜잭션 1이 쿼리한 레코드 업데이트
  ————->트랜잭션 2: 제출을 위해 커밋 호출
  트랜잭션 1: 마지막 레코드 다시 쿼리
  이때 트랜잭션 1이 쿼리 동일한 데이터를 두 번 읽어도 사용 가능한 내용이 달라지는 것을 반복 불가능 읽기라고 합니다

• 幻读：同一事务中，用同样的操作读取两次，得到的记录数不相同 
  事务1：查询表中所有记录 
  ———->事务2：插入一条记录 
  ———->事务2：调用commit进行提交 
  事务1：再次查询表中所有记录 
  此时事务1两次查询到的记录是不一样的，称为幻读 
  详细解释： 
  幻读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改， 
  这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表 
  中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行， 
  就好象发生了幻觉一样。

6、事务隔离

事务隔离五种级别：

TRANSACTION_NONE 不使用事务。 
TRANSACTION_READ_UNCOMMITTED 允许脏读。 
TRANSACTION_READ_COMMITTED 防止脏读，最常用的隔离级别,并且是大多数数据库的默认隔离级别 
TRANSACTION_REPEATABLE_READ 可以防止脏读和不可重复读， 
TRANSACTION_SERIALIZABLE 可以防止脏读，不可重复读取和幻读，（事务串行化）会降低数据库的效率

以上的五个事务隔离级别都是在Connection接口中定义的静态常量，

使用setTransactionIsolation(int level) 方法可以设置事务隔离级别。 
如:con.setTransactionIsolation(Connection.REPEATABLE_READ);

注意：事务的隔离级别受到数据库的限制，不同的数据库支持的的隔离级别不一定相同

summary： 
（1）Serializable：可避免脏读、不可重复读、虚读情况的发生。 
（2）Repeatable read：可避免脏读、不可重复读情况的发生。（可重复读，是 MySQL 默认的事务隔离级别） 
（3）Read committed：可避免脏读情况发生。（读取已提交的数据） 
（4）Read uncommitted：最低级别，以上情况均无法保证。（读取到了未提交的数据）

以上就是 【MySQL 06】事务处理的内容，更多相关内容请关注PHP中文网（www.php.cn）！