% mysql --user=a --password=hoge --prompt='m1> ' test

m1> create table test(
  id INT(11) AUTO_INCREMENT NOT NULL PRIMARY KEY,
  name varchar(32) not null unique
);
Query OK, 0 rows affected (0.01 sec)

m1> start transaction;
Query OK, 0 rows affected (0.00 sec)

-- トランザクションの分離レベル確認
m1> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.01 sec)

                                  % mysql --user=a --password=hoge --prompt='m2> ' test

                                  m2> start transaction;
                                  Query OK, 0 rows affected (0.00 sec)

                                  -- トランザクションの分離レベル確認
                                  m1> SELECT @@tx_isolation;
                                  +-----------------+
                                  | @@tx_isolation  |
                                  +-----------------+
                                  | REPEATABLE-READ |
                                  +-----------------+
                                  1 row in set (0.00 sec)

m1> insert into test (name) values ("aa");
Query OK, 1 row affected (0.01 sec)

                                  -- m1のトランザクションが終るまで待たされる
                                  m2> insert into test (name) values ("aa");

m1> commit;
Query OK, 0 rows affected (0.01 sec)

                                  -- エラーが返ってくる
                                  ERROR 1062 (23000): Duplicate entry 'aa' for key 'name'

                                  m2> rollback;
                                  Query OK, 0 rows affected (0.00 sec)

m1> start transaction;
Query OK, 0 rows affected (0.00 sec)

                                  m2> start transaction;
                                  Query OK, 0 rows affected (0.00 sec)

m1> insert into test (name) values ("b");
Query OK, 1 row affected (0.00 sec)

                                  m2> insert into test (name) values ("c");
                                  Query OK, 1 row affected (0.00 sec)

m1> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  3 | b    |
+----+------+
2 rows in set (0.00 sec)

                                  m2> select * from test;
                                  +----+------+
                                  | id | name |
                                  +----+------+
                                  |  1 | aa   |
                                  |  4 | c    |
                                  +----+------+
                                  2 rows in set (0.00 sec)

m1> commit;
Query OK, 0 rows affected (0.01 sec)

m1> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  3 | b    |
+----+------+
2 rows in set (0.00 sec)

                                  m2> commit;
                                  Query OK, 0 rows affected (0.01 sec)

-- 両方コミットしたら、両方がコミットしたデータが見える
m1> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  3 | b    |
|  4 | c    |
+----+------+
3 rows in set (0.00 sec)

                                  m2> select * from test;
                                  +----+------+
                                  | id | name |
                                  +----+------+
                                  |  1 | aa   |
                                  |  3 | b    |
                                  |  4 | c    |
                                  +----+------+
                                  3 rows in set (0.00 sec)

test=> set PROMPT1 'p1> '

-- トランザクションの分離レベル確認
p1> SHOW TRANSACTION ISOLATION LEVEL;
 transaction_isolation
-----------------------
 read committed
(1 row)

p1> create table test(
  id serial NOT NULL PRIMARY KEY,
  name char(32) not null unique
);

p1> BEGIN;
BEGIN

                                  test=> set PROMPT1 'p2> '

                                  -- トランザクションの分離レベル確認
                                  p2> SHOW TRANSACTION ISOLATION LEVEL;
                                   transaction_isolation
                                  -----------------------
                                   read committed
                                  (1 row)

                                  p2> BEGIN;
                                  BEGIN

p1> insert into test (name) values ('aa');
INSERT 0 1

                                  -- p1 のトランザクションが終るまで待たされる
                                  p2> insert into test (name) values ("aa");

p1> commit;
Query OK, 0 rows affected (0.01 sec)

                                  -- エラーが返ってくる
                                  ERROR:  duplicate key value violates unique constraint "test_name_key"
                                  DETAIL:  Key (name)=(aa                              ) already exists.

                                  p2> ABORT;
                                  ROLLBACK

p1> select * from test;
 id |               name
----+----------------------------------
  2 | aa
(1 row)

                                  p2> select * from test;
                                   id |               name
                                  ----+----------------------------------
                                    2 | aa
                                  (1 row)

p1> BEGIN;
BEGIN

                                  p2> BEGIN;
                                  BEGIN

p1> insert into test (name) values ('b');
INSERT 0 1

                                  p2> insert into test (name) values ('c');
                                  INSERT 0 1

p1> select * from test;
 id |               name
----+----------------------------------
  2 | aa
  4 | b

                                  p2> select * from test;
                                   id |               name
                                  ----+----------------------------------
                                    2 | aa
                                    5 | c
                                  (2 rows)

p1> COMMIT;
COMMIT
p1> select * from test;
 id |               name
----+----------------------------------
  2 | aa
  4 | b
(2 rows)

                                  p2> COMMIT;
                                  COMMIT
                                  p2> select * from test;
                                   id |               name
                                  ----+----------------------------------
                                    2 | aa
                                    4 | b
                                    5 | c
                                  (3 rows)

p1> select * from test;
 id |               name
----+----------------------------------
  2 | aa
  4 | b
  5 | c
(3 rows)

INSERT INTO ... SELECT