Caused by: java.sql.BatchUpdateException: Transaction error, need to rollback. errno:1205 Lock wait timeout exceeded; try restarting transaction

更新的时候报

Caused by: java.sql.BatchUpdateException: Transaction error, need to rollback. errno:1205 Lock wait timeout exceeded; try restarting transaction

update_time=now() where streaming_no=?

  

找半天,给streaming_no加上索引就好了

mysql> show index from T_VSOP_CUSTOMER_INFO_RECORD
-> ;
+-----------------------------+------------+--------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------------------+------------+--------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| T_VSOP_CUSTOMER_INFO_RECORD | 0 | PRIMARY | 1 | id | A | 2428448 | NULL | NULL | | BTREE | | |
| T_VSOP_CUSTOMER_INFO_RECORD | 1 | index_streaming_no | 1 | streaming_no | A | 2671571 | NULL | NULL | | BTREE | | |
+-----------------------------+------------+--------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set

 

Caused by: java.sql.BatchUpdateException: Transaction error, need to rollback. errno:1205 Lock wait timeout exceeded; try restarting transaction

--未加索引的外键会存在的问题
1.引起全表锁。
2.当使用了on delete cascade,而且没有对子表加索引,要从父表中删除多行时,每删除一行就要扫描一次子表。
3.当你从父表查询子表,你会发现没有索引的话会使查询变慢。

--什么时候不需要对外键增加索引
1.不会从父表中删除行。
2.不会去更新父表的主键。
3.不会从父表连接到子表。

如果更新的where条件没有索引 则会产生表锁

上一篇:LeetCode#227.Basic Calculator II


下一篇:Cannot find a valid baseurl for repo: base