+-----------+ +-----------+
| Session A | <-------------> | Session B |
+-----------+ +-----------+
테이블생성
데이터입력(noTransation)
데이터확인 데이터확인
데이터입력(withTransaction)
데이터확인 데이터확인
commit
데이터확인 데이터확인
데이터입력(withTransaction)
데이터확인 데이터확인
rollback
데이터확인 데이터확인
1. Session A : InnoDB 테이블 생성
mysql> create table test_table (
-> col_a int not null primary key,
-> col_b varchar(64) not null
-> ) engine=InnoDB;
Query OK, 0 rows affected (0.09 sec)
2. Session A : 데이터 입력 (noTransation)
mysql> insert into test_table (col_a, col_b) values (1, 'a');
Query OK, 1 row affected (0.09 sec)
mysql> insert into test_table (col_a, col_b) values (2, 'b');
Query OK, 1 row affected (0.03 sec)
2-1. Session A : 데이터 확인
mysql> select * from test_table;
+-------+-------+
| col_a | col_b |
+-------+-------+
| 1 | a |
| 2 | b |
+-------+-------+
2 rows in set (0.00 sec)
2-2. Session B : 데이터 확인
mysql> select * from test_table;
+-------+-------+
| col_a | col_b |
+-------+-------+
| 1 | a |
| 2 | b |
+-------+-------+
2 rows in set (0.00 sec)
3. Session A : 데이터 입력 (withTransaction)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test_table (col_a, col_b) values (3, 'c');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_table (col_a, col_b) values (4, 'd');
Query OK, 1 row affected (0.00 sec)
3-1. Session A : 데이터 확인
mysql> select * from test_table;
+-------+-------+
| col_a | col_b |
+-------+-------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+-------+-------+
4 rows in set (0.02 sec)
3-2. Session B : 데이터 확인
mysql> select * from test_table;
+-------+-------+
| col_a | col_b |
+-------+-------+
| 1 | a |
| 2 | b |
+-------+-------+
2 rows in set (0.00 sec)
4. Session A : Commit
mysql> commit;
Query OK, 0 rows affected (0.09 sec)
4-1. Session A : 데이터 확인
mysql> select * from test_table;
+-------+-------+
| col_a | col_b |
+-------+-------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+-------+-------+
4 rows in set (0.02 sec)
4-2. Session B : 데이터 확인
mysql> select * from test_table;
+-------+-------+
| col_a | col_b |
+-------+-------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+-------+-------+
4 rows in set (0.02 sec)
5. Session A : 데이터 입력 (withTransaction)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test_table (col_a, col_b) values (5, 'e');
Query OK, 1 row affected (0.00 sec)
5-1. Session A : 데이터 확인
mysql> select * from test_table;
+-------+-------+
| col_a | col_b |
+-------+-------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+-------+-------+
5 rows in set (0.00 sec)
5-2. Session B : 데이터 확인
mysql> select * from test_table;
+-------+-------+
| col_a | col_b |
+-------+-------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+-------+-------+
4 rows in set (0.02 sec)
6. Session A : Rollback
mysql> rollback;
Query OK, 0 rows affected (0.05 sec)
6-1. Session A : 데이터 확인
mysql> select * from test_table;
+-------+-------+
| col_a | col_b |
+-------+-------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+-------+-------+
4 rows in set (0.02 sec)
6-2. Session B : 데이터 확인
mysql> select * from test_table;
+-------+-------+
| col_a | col_b |
+-------+-------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+-------+-------+
4 rows in set (0.02 sec)
트랜잭션은 InnoDB 밖에 안된다.
테이블 생성시 engine=InnoDB; 옵션을 주거나,
alter table 로 engine 을 InnoDB 로 바꿔야만 트랜잭션이 동작한다.
아 졸 귀차나...
EOF : Zany (2009-05-15)
|
|
|
