This page looks best with JavaScript enabled

【SQL】トランザクションの切り方

 ·   ·  ☕ 2 分で読めます
✏️

RSpecをctrl + Cで中断したら、その後こんなエラーが。

1
2
3
4
5
6
7
Failures:

  1) なんちゃらかんちゃら
     Failure/Error: create(:user)

     ActiveRecord::TransactionIsolationConflict:
       Transaction isolation conflict detected: Lock wait timeout exceeded; try restarting transaction

どうやらRSpecのトランザクション中に切ってしまったらしい。
そんなジャストで止めちゃうことある?って感じですが、運が良かった(?)ということで、止まってしまったトランザクションを強制終了させる。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
$ mysql -u root -p                                                                                                                                                130
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3348
Server version: 5.6.47 Homebrew

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW ENGINE INNODB STATUS;
+--------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Type   | Name | Status

...

------------
TRANSACTIONS
------------
Trx id counter 1171695
Purge done for trx's n:o < 1171511 undo n:o < 0 state: running but idle
History list length 685
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 3348, OS thread handle 0x700006acf000, query id 1244553 localhost root init
SHOW ENGINE INNODB STATUS
---TRANSACTION 1162101, not started
MySQL thread id 2666, OS thread handle 0x700006b9b000, query id 1244550 localhost root
---TRANSACTION 0, not started
MySQL thread id 2667, OS thread handle 0x700006dbb000, query id 1244551 localhost root
---TRANSACTION 1171509, ACTIVE 493 sec
8 lock struct(s), heap size 1184, 1 row lock(s), undo log entries 7
MySQL thread id 3336, OS thread handle 0x700006c23000, query id 1243406 localhost root
Trx read view will not see trx with id >= 1171510, sees < 1171510
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
mysql> show innodb status;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'innodb status' at line 1
mysql> show processlist;
+------+------+-----------+---------------------+------------+------+---------------------------------+----------------------------------------+
| Id   | User | Host      | db                  | Command    | Time | State                           | Info                                   |
+------+------+-----------+---------------------+------------+------+---------------------------------+----------------------------------------+
| 2666 | root | localhost | hoge_development    | Sleep      |   28 |                                 | NULL                                   |
| 2667 | root | localhost | NULL                | Sleep      |    7 |                                 | NULL                                   |
| 3336 | root | localhost | hoge_test           | Sleep      |  590 |                                 | NULL                                   |
| 3346 | root | localhost | hoge_test           | Query      |  304 | Waiting for table metadata lock | DROP DATABASE IF EXISTS `hoge_test`    |
| 3347 | root | localhost | hoge_test           | Field List |  178 | Waiting for table metadata lock |                                        |
| 3348 | root | localhost | NULL                | Query      |    0 | init                            | show processlist                       |
+------+------+-----------+---------------------+------------+------+---------------------------------+----------------------------------------+
6 rows in set (0.00 sec)

mysql> kill 3336;
Query OK, 0 rows affected (0.00 sec)

mysql> show processlist;
+------+------+-----------+---------------------+---------+------+-------+------------------+
| Id   | User | Host      | db                  | Command | Time | State | Info             |
+------+------+-----------+---------------------+---------+------+-------+------------------+
| 2666 | root | localhost | hoge_development    | Sleep   |   50 |       | NULL             |
| 2667 | root | localhost | NULL                | Sleep   |   29 |       | NULL             |
| 3348 | root | localhost | NULL                | Query   |    0 | init  | show processlist |
+------+------+-----------+---------------------+---------+------+-------+------------------+
3 rows in set (0.00 sec)

mysql> exit
Bye

transactionの途中でトランザクションが切れてしまった時にそのトランザクションを殺す方法 - Garbage in, gospel out

Share on

END
END
@aiandrox

目次