19장. 트랜잭션과 락(LOCK) 트랜잭션이란? MySQL의 트랜잭션 락(LOCK) 쉽게 배우는 MySQL 5.x 2018-12-30 쉽게 배우는 MySQL 5.x

19.1 트랜잭션이란? -트랜잭션(Transaction)은 작업 처리 전체를 하나의 단위로 묶어서 처리 -SQL에서는 쿼리 시작 전 상단부에 트랜잭션 사용을 선언하고 쿼리를 수행 -쿼리가 정상으로 종료되면 COMMIT으로 데이터베이스에 데이터를 저장 -실패할 경우 ROLLBACK하여 트랜잭션 사용 선언부 이하 쿼리를 모두 취소 -트랜잭션을 사용하려면 InnoDB와 BDB로 테이블 타입을 정의 -HEAP, ISAM, MERGE 그리고 MyISAM은 트랜잭션을 지원하지 않는다. 트랜잭션을 사용할 때의 장점 -서버가 깨지거나 하드웨어적인 문제가 발생하더라도 자동 복구 기능이나 백업된 트랜잭션 로그를 이용하여 데이터를 살릴 수 있다. -COMMIT 명령을 이용하여 여러 개의 SQL문을 하나로 합쳐서 실행 가능 트랜잭션을 사용하지 않았을 때의 단점 ∙속도가 빠르다. ∙디스크 용량과 메모리를 적게 차지 ∙옵션을 주어 트랜잭션 기능을 이용 2018-12-30 쉽게 배우는 MySQL 5.x

트랜잭션의 성격 4가지 ACID 성질 원자성(atomicity) 트랜잭션은 전체의 실행만이 있지 일부 실행으로 트랜잭션의 기능을 가질 수는 없다. 일관성(consistency) 트랜잭션이 그 실행을 성공적으로 완료하면 언제나 일관된 데이터베이스 상태로 된다. 즉, 이 트랜잭션의 실행으로 일관성이 깨지지 않는다. 격리성(isolation)  연산의 중간결과에 다른 트랜잭션이나 작업이 접근할 수 없다. 영속성(durability)  트랜잭션이 일단 그 실행을 성공적으로 끝내면 그 결과는 어떠한 경우에라도 보장받는다. 2018-12-30 쉽게 배우는 MySQL 5.x

19.2 MySQL에서의 트랜잭션 MySQL이 InnoDB 테이블이 지원되는지 확인해 보자. mysql> show variables like 'have_%'; +-----------------------+----------+ | Variable_name         | Value    | | have_archive          | YES      | | have_bdb              | NO       | | have_blackhole_engine | NO       | | have_compress         | YES      | | have_crypt            | NO       | | have_csv              | NO       | | have_example_engine   | NO       | | have_federated_engine | NO       | | have_geometry         | YES      | | have_innodb           | YES      | | have_isam             | NO       | | have_ndbcluster       | NO       | | have_openssl          | DISABLED | | have_query_cache      | YES      | | have_raid             | NO       | | have_rtree_keys       | YES      | | have_symlink          | YES      | have_innodb 의 값이 YES이므로 사용할 수 있다 값이 NO로 되어있다면 InnoDB 테이블 타입이 활성화 되지 않는 상태 2018-12-30 쉽게 배우는 MySQL 5.x

-트랜잭션을 사용하기 위해 자동으로 COMMIT되는 것을 막아 준다. -하나의 작업이 모두 끝났을 때 COMMIT를 해주어야 하기 때문에 SET 명령으로 AUTOCOMMIT를 설정 mysql> SET AUTOCOMMIT = 0; Query OK, 0 rows affected (0.08 sec) -AUTOCOMMIT가 0이되면 자동 커밋이 되지 않는다. -다시 AUTOCOMMIT를 사용하려면 값을 1로 주면 자동커밋 상태가 적용된다. -트랜잭션을 사용하기 위해서 BEGIN WORK 명령을 실행하면 이전에 실행되던 모든 SQL문을 COMMIT하게 된다. 트랜잭션의 사용 BEGIN WORK 명령어를 실행하여 하나의 트랜잭션을 시작 mysql> BEGIN WORK; Query OK, 0 rows affected (0.31 sec) 2018-12-30 쉽게 배우는 MySQL 5.x

[예제 19-1] 학번 20041002의 학생이름(STU_NAME)과 반(CLASS)을 출력하라. mysql> SELECT STU_NAME, CLASS         -> FROM STUDENT     -> WHERE STU_NO = '20041002'; +----------+-------+ | STU_NAME | CLASS | | 김유미   |     2 | 위의 결과를 보고 김유미 학생의 반을 1반으로 변경하여라. mysql> UPDATE STUDENT                     -> SET CLASS = 1      -> WHERE STU_NAME = '김유미'; Query OK, 1 row affected (0.02 sec) 변경한 결과를 확인하자. mysql> SELECT STU_NAME, CLASS | 김유미   |     1 | 2018-12-30 쉽게 배우는 MySQL 5.x

트랜잭션을 종료하기 위해 ROLLBACK을 실행하면 변경된 작업이 취소된다. mysql> ROLLBACK; Query OK, 0 rows affected (0.05 sec) 김유미 학생의 반(CLASS)를 확인해보자. mysql> SELECT STU_NAME, CLASS     -> FROM STUDENT     -> WHERE STU_NO = '20041002'; +----------+-------+ | STU_NAME | CLASS | | 김유미   |     2 | 1 row in set (0.00 sec) 반(CLASS)이 변경되지 않은 것을 볼 수 있다. COMMIT과 함께 트랜잭션을 완료하는 과정을 보도록 하자. BEGIN WORK를 실행하여 트랜잭션의 시작을 알린다. mysql> BEGIN WORK; Query OK, 0 rows affected (0.00 sec) 2018-12-30 쉽게 배우는 MySQL 5.x

COMMIT를 이용해 데이터베이스에 정보의 변경 내용을 저장 김유미 학생의 반을 3반으로 변경한다. mysql> UPDATE STUDENT      -> SET CLASS = 3      -> WHERE STU_NAME = '김유미'; Query OK, 1 row affected (0.02 sec) Rows matched: 1  Changed: 1  Warnings: 0 COMMIT를 이용해 데이터베이스에 정보의 변경 내용을 저장 mysql> COMMIT; Query OK, 0 rows affected (0.00 sec) 변경된 내용을 확인해보자. mysql> SELECT STU_NAME, CLASS        -> FROM STUDENT     -> WHERE STU_NO = '20041002'; +----------+-------+ | STU_NAME | CLASS | | 김유미   |     3 | 1 row in set (0.00 sec) 트랜잭션이 성공했기 때문에 변경된 내용이 데이터베이스에 저장되었다. 2018-12-30 쉽게 배우는 MySQL 5.x

19.3 락(LOCK) -데이터베이스 백업, 테이블의 스키마 구조 변경, 기타 중요한 작업을 진행할 때 다른 사람이 해당 테이블에 작업을 하지 못하도록 막기 위해 locking을 한다. -트랜잭션이 지원되지 않는 MyISAM에는 트랜잭션과 비슷한 LOCK를 사용 형식 : LOCK TABLES <테이블명> [READ | WRITE] -READ LOCK을 사용하면 다른 사용자가 해당 테이블을 읽기만 가능하고 쓰기를 할 때에는 LOCK이 걸린다.(SELECT문은 실행 할 수 있지만 INSERT, UPDATE, DELETE를 실행할 수 없다.) -WRITE LOCK을 사용하면 다른 사용자가 해당테이블에 대해 읽기와 쓰기를 할 때 모두 LOCK이 걸린다.(READ LOCK를 포함한 SELECT문을 실행할 때 LOCK이 걸린다.) -UNLOCK TABLES은 LOCK를 해제할 때 사용한다. 2018-12-30 쉽게 배우는 MySQL 5.x

-학생(STUDENT)테이블과 교수(PROFESSOR)테이블 LOCK mysql> lock tables student read,      -> professor write; Query OK, 0 rows affected (0.00 sec) -LOCK이 걸린 테이블의 내용을 보려면 다음과 같은 select문을 사용 mysql> select stu_name     -> from student     -> where class = 2; +----------+ | stu_name | | 정인정   | | 최차영   | 2 rows in set (0.00 sec) -다른 사용자가 학적테이블에 READ LOCK을 걸었기 때문에 테이블의 정보를 SELECT 할 수는 있어도 INSERT, UPDATE, DELETE를 할 수 없다. -교수테이블은 SELECT도 허용하지 않는다. 또한 데이터베이스를 지우려고 할 때 LOCK 또는 트랜잭션이 걸려있으면 삭제되지 않는다. -LOCK를 해제하는 방법 mysql> unlock tables; 2018-12-30 쉽게 배우는 MySQL 5.x