13518219792
研发创建了存储过程，需要部署定时任务，在上线前进行检查，执行定时脚本时，出现了如下报错
目前创新互联建站已为千余家的企业提供了网站建设、域名、虚拟主机、网站托管、企业网站设计、祥云网站维护等服务，公司将坚持客户导向、应用为本的策略，正道将秉承"和谐、参与、激情"的文化，与客户和合作伙伴齐心协力一起成长，共同发展。
ERROR: [SQLCODE] 1055 , [SQLSTATE] 42000 , [MESSAGE] Expression #1 of ORDER BY clause is not in GROUP BY
此时查看sql_mode，发现里面没有only_full_group_by，但是执行还会报错，怀疑是链接没断开，当前session没生效，断开重连还是一样的报错
遂查看存储过程 show create procedure dataabase.procname;
发现创建之前的sql_mode有only_full_group_by，为了能正常运行，在当前模式下，删除新建存储过程，即可重新调用，执行成功。
0、首先排除机器问题，如cpu、内存情况
1、根据日志找到sql语句，从两点，一是索引、二是语句的写法
2、使用mysql的explain+语句形式，排查是否引用索引，通过key、extra，key表示有没有用到索引，用到的是哪个索引，像like、or等是索引失效的，extra using index表示覆盖索引，usingwhere表示where条件用到了索引，通过explain结果，修改sql语句，该加索引加索引，该修改语句修改语句
3、语句编写上，子查询、关联查询大表小表
4、部署测试
可以使用 show processlist 命令查看当前所有连接信息。
使用 explain 命令查询 SQL 语句执行计划。
开启慢查询日志，查看慢查询的 SQL。
最近接了一个锅,进入新公司接手了一个进入交付阶段的项目.在code review的时候发现很多问题,然后开始修复bug.
在测试阶段突然发现几乎所有涉及到更新的操作都失败,下面贴出异常信息.
第一次 出现的时候百度了一下,猜想可能是多服务部署资源冲突,重启服务故障消失.所以没有特别重视
第二次 出现的时候只有测试环境部署,不存在多机资源冲突的问题,猜想是多线程资源交叉导致的,于是给可能导致资源竞争的地方加上了分布式锁.
由于无法重现故障,所以并没有确认问题得到解决.
第三次 故障依旧,当发现问题依然存在的时候,开始认真反思,发现自己解决问题的思路明显有问题,过于片面,一直都只在应用层面寻求解决问题的办法,而且解决问题的方式也只是在尝试百度出来的方法.并没有去思考更深层的问题.
在Mysql5.5中，information_schema 库中增加了三个关于锁的表（MEMORY引擎）；
INNODB_TRX ## 当前运行的所有事务
INNODB _LOCKS ## 当前出现的锁
INNODB_LOCK_WAITS ## 锁等待的对应关系
通过查询 INNODB_TRX 发现
当前事务中又两个RUNNING状态开始时间在一个小时之前
开始一直以为是锁表了
查看了 INNODB _LOCKS 事务信息之后发现有4行数据被锁住了一直没有释放
从这里开始发现问题了,应用已经抛了异常,事务理所当然的应该回滚才对,为什么资源依然没有释放,导致持续的阻塞呢?
其实最开始的异常信息就已经给出了答案,回到开始的地方,再看异常信息就很清楚了,应用里面的异常类是 MySQLTransactionRollBackException
是一个回滚异常, 这就说明在事务回滚的时候出了问题资源没有得到释放
然后开始查询 MySQLTransactionRollBackException 相关的信息
这个时候 innodb_rollback_on_timeout =0(默认配置)这个MySQL的配置开始进入我的视线,
举个栗子
事务在锁等待超时后是回滚事务内所有的statement还是最后一条语句；
0表示rollback最后一条语句，默认值； 有点坑爹啊( 细思极恐 )
1表示回滚事务内所有的statements；(此参数是只读参数，需在my.cnf中配置，并且重启生效；)
吃过一次亏,这次并没有盲目的相信百度到的信息
于是开始测试
一、验证innodb_rollback_on_timeout=off的情况
1.session A
开启事务,事务未提交,锁住id=1的数据
2.session B
开启事务,执行更新id=2的数据成功(事务未提交,锁住id=2),然后请求id=1等待锁超时,id=2的数据更改为222.
3.session C
请求id=2的数据50秒后显示等待锁超时
执行 SELECT * FROM information_schema.INNODB_TRX;
可发现有资源一直未释放,具体到测试数据中就是id=2的资源一直被锁定,线程一直被挂起.
总结:通过实验基本可以确定是业务资源交叉导致死锁之后资源没释放造成的持续阻塞，
二.验证innodb_rollback_on_timeout=on
修改配置后将验证innodb_rollback_on_timeout=off的步骤再走一遍
发现锁等待只能在业务层面尽量避免
on/off的区别在于session C进入时不会持续阻塞,session B异常后全部回滚
