Caused by: java.sql.BatchUpdateException: Transaction error, need to rollback. errno:1205 Lock wait ...

更新的时候报

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

　

 

 

 

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


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

 

 

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

转载于:https://www.cnblogs.com/JAYIT/p/6226627.html