■ 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
