Mysql - test the transaction step by step

■ Showing you which threads are running. ①

mysql> SHOW FULL PROCESSLIST;
+-----+------+-----------+------+---------+------+-------+-----------------------+
| Id  | User | Host      | db   | Command | Time | State | Info                  |
+-----+------+-----------+------+---------+------+-------+-----------------------+
|  32 | root | localhost | test | Sleep   |   20 |       | NULL                  |
| 161 | root | localhost | test | Sleep   |  961 |       | NULL                  |
| 246 | root | localhost | NULL | Query   |    0 | init  | SHOW FULL PROCESSLIST |
+-----+------+-----------+------+---------+------+-------+-----------------------+
3 rows in set (0.00 sec)

■ Check the process list⑥

mysql> SHOW FULL PROCESSLIST;
+-----+------+-----------+------+---------+------+-------+-----------------------+
| Id  | User | Host      | db   | Command | Time | State | Info                  |
+-----+------+-----------+------+---------+------+-------+-----------------------+
|  32 | root | localhost | test | Sleep   |   15 |       | NULL                  |
| 161 | root | localhost | test | Sleep   |    5 |       | NULL                  |
| 246 | root | localhost | NULL | Query   |    0 | init  | SHOW FULL PROCESSLIST |
+-----+------+-----------+------+---------+------+-------+-----------------------+
3 rows in set (0.00 sec)

■ Check the process list⑪

mysql> SHOW FULL PROCESSLIST;
+-----+------+-----------+------+---------+------+----------+-----------------------------------------+
| Id  | User | Host      | db   | Command | Time | State    | Info                                    |
+-----+------+-----------+------+---------+------+----------+-----------------------------------------+
|  32 | root | localhost | test | Sleep   |  531 |          | NULL                                    |
| 161 | root | localhost | test | Query   |    8 | updating | UPDATE table_one SET key_one='key_one3' |
| 246 | root | localhost | NULL | Query   |    0 | init     | SHOW FULL PROCESSLIST                   |
+-----+------+-----------+------+---------+------+----------+-----------------------------------------+
3 rows in set (0.00 sec)

■ Check the process list⑫

mysql> SHOW FULL PROCESSLIST;
+-----+------+-----------+------+---------+------+-------+-----------------------+
| Id  | User | Host      | db   | Command | Time | State | Info                  |
+-----+------+-----------+------+---------+------+-------+-----------------------+
|  32 | root | localhost | test | Sleep   |  616 |       | NULL                  |
| 161 | root | localhost | test | Sleep   |   93 |       | NULL                  |
| 246 | root | localhost | NULL | Query   |    0 | init  | SHOW FULL PROCESSLIST |
+-----+------+-----------+------+---------+------+-------+-----------------------+
3 rows in set (0.00 sec)

===============================This is A connection================================
■ Selected the data②

mysql> SELECT * FROM table_one;
+----+-------+----------+------------+
| id | name  | key_one  | value_one  |
+----+-------+----------+------------+
|  1 | name1 | key_one1 | value_one1 |
+----+-------+----------+------------+
1 row in set (0.00 sec)

■ Started TRANSACTION③

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

■ Selected the data④

mysql> SELECT * FROM table_one;
+----+-------+----------+------------+
| id | name  | key_one  | value_one  |
+----+-------+----------+------------+
|  1 | name1 | key_one1 | value_one1 |
+----+-------+----------+------------+
1 row in set (0.00 sec)

■ Updated the data⑤

mysql> UPDATE table_one SET key_one='key_one2';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

■ Selected the data for checking it⑦

mysql> SELECT * FROM table_one;
+----+-------+----------+------------+
| id | name  | key_one  | value_one  |
+----+-------+----------+------------+
|  1 | name1 | key_one2 | value_one1 |
+----+-------+----------+------------+
1 row in set (0.00 sec)

================================This is B connection===============================
■ Selected the data⑧

mysql> SELECT * FROM table_one;
+----+-------+----------+------------+
| id | name  | key_one  | value_one  |
+----+-------+----------+------------+
|  1 | name1 | key_one1 | value_one1 |
+----+-------+----------+------------+
1 row in set (0.00 sec)

■ Started TRANSACTION⑨

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

■ Selected the data for checking it⑩

mysql> SELECT * FROM table_one;
+----+-------+----------+------------+
| id | name  | key_one  | value_one  |
+----+-------+----------+------------+
|  1 | name1 | key_one1 | value_one1 |
+----+-------+----------+------------+
1 row in set (0.00 sec)

■ Showing the error message and rollback automatically after past the default 50 second.⑪

mysql> UPDATE table_one SET key_one='key_one3';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction