mysql报错

[孤云何处闲]

org.springframework.jdbc.UncategorizedSQLException:
### Error updating database. Cause: java.sql.SQLException: The total number of locks exceeds the lock table size
### The error may involve com.xm.yjxq.dao.IYjxqDao.jsWamsyj-Inline
### The error occurred while setting parameters
### SQL: insert into t_yjxq(jxdymc,jxdybm,yjzmc,yjzbm,yzxmc,yzxh,xs,dj,num,jzl,bonus,createDate,type,dbFlag) #门诊无艾默生医技科室奖金（不包含消化内科内镜中心无艾默生医技数据，也不包含神内的脑电图室数据） select t1.jxdymc,t1.jxdybm,t1.yjzmc,t1.yjzbm,t1.yzxmc,t1.yzxh,t1.xs,t1.dj,sum(z2.sum_yzsl) num,sum(z2.sum_yzsl * t1.xs) jzl,sum(z2.sum_yzsl * t1.xs * t1.dj) bonus, z2.newDate createDate,'0' as type,'1' as dbFlag from ( ( select y.jxdymc,y.jxdybm,y.yjzmc,y.yjzbm,y.jsksmc,y.jsksh,y.yzxmc,y.yzxh,y.xs,y.dj from t_yjxm y where y.flag = '1' and y.deleteFlag = '1' and y.jsksmc not in ( '脑电图室','介入病房','放射导管室') and y.yjzbm not in (select yj.yjzCode from t_yj_jzlcl yj where yj.flag = '1' and yj.deleteFlag = '1') ) t1 left join ( select z.jsksmc,z.jsksh,z.yzxmc,z.yzxh,substr(z.date,1,7) newDate,sum(z.yzsl) sum_yzsl from t_mz_charge z where z.date like '2018-11%' group by z.jsksmc,z.jsksh,z.yzxmc,z.yzxh,substr(z.date,1,7) ) z2 on t1.jsksh = z2.jsksh and t1.yzxh = z2.yzxh ) where z2.sum_yzsl is not null group by t1.jxdymc,t1.jxdybm,t1.yjzmc,t1.yjzbm,t1.jsksmc,t1.jsksh,t1.yzxmc,t1.yzxh,t1.dj union all #住院无艾默生医技科室奖金（不包含消化内科内镜中心无艾默生医技数据，也不包含神内的脑电图室数据） select t1.jxdymc,t1.jxdybm,t1.yjzmc,t1.yjzbm,t1.yzxmc,t1.yzxh,t1.xs,t1.dj,sum(z2.sum_yzsl) num,sum(z2.sum_yzsl * t1.xs) jzl,sum(z2.sum_yzsl * t1.xs * t1.dj) bonus, z2.newDate createDate,'0' as type,'2' as dbFlag from ( ( select y.jxdymc,y.jxdybm,y.yjzmc,y.yjzbm,y.jsksmc,y.jsksh,y.yzxmc,y.yzxh,y.xs,y.dj from t_yjxm y where y.flag = '1' and y.deleteFlag = '1' and y.jsksmc not in ( '脑电图室','介入病房','放射导管室') and y.yjzbm not in (select yj.yjzCode from t_yj_jzlcl yj where yj.flag = '1' and yj.deleteFlag = '1' ) ) t1 left join ( select z.jsksmc,z.jsksh,z.yzxmc,z.yzxh,substr(z.date,1,7) newDate,sum(z.yzsl) sum_yzsl from t_zy_charge z where z.date like '2018-11%' group by z.jsksmc,z.jsksh,z.yzxmc,z.yzxh,substr(z.date,1,7) ) z2 on t1.jsksh = z2.jsksh and t1.yzxh = z2.yzxh ) where z2.sum_yzsl is not null group by t1.jxdymc,t1.jxdybm,t1.yjzmc,t1.yjzbm,t1.jsksmc,t1.jsksh,t1.yzxmc,t1.yzxh,t1.dj union all #急诊无艾默生医技科室奖金（不包含消化内科内镜中心无艾默生医技数据，也不包含神内的脑电图室数据） select t1.jxdymc,t1.jxdybm,t1.yjzmc,t1.yjzbm,t1.yzxmc,t1.yzxh,t1.xs,t1.dj,sum(z2.sum_yzsl) num,sum(z2.sum_yzsl * t1.xs) jzl,sum(z2.sum_yzsl * t1.xs * t1.dj) bonus, z2.newDate createDate,'0' as type,'3' as dbFlag from ( ( select y.jxdymc,y.jxdybm,y.yjzmc,y.yjzbm,y.jsksmc,y.jsksh,y.yzxmc,y.yzxh,y.xs,y.dj from t_yjxm y where y.flag = '1' and y.deleteFlag = '1' and y.jsksmc not in ( '脑电图室','介入病房','放射导管室') and y.yjzbm not in (select yj.yjzCode from t_yj_jzlcl yj where yj.flag = '1' and yj.deleteFlag = '1' ) ) t1 left join ( select z.jsksmc,z.jsksh,z.yzxmc,z.yzxh,substr(z.date,1,7) newDate,sum(z.yzsl) sum_yzsl from t_jz_charge z where z.date like '2018-11%' group by z.jsksmc,z.jsksh,z.yzxmc,z.yzxh,substr(z.date,1,7) ) z2 on t1.jsksh = z2.jsksh and t1.yzxh = z2.yzxh ) where z2.sum_yzsl is not null group by t1.jxdymc,t1.jxdybm,t1.yjzmc,t1.yjzbm,t1.jsksmc,t1.jsksh,t1.yzxmc,t1.yzxh,t1.dj union all #门诊无艾默生医技科室奖金(神经内科-脑电图室) #神经内科脑电图项目提取需“接收科室”与“病人科室”两个条件同时满足 #数据源 病人科室 接收科室 #门诊收费数据 神内门诊 脑电图室 #住院收费数据 神经内科 脑电图室 select t1.jxdymc,t1.jxdybm,t1.yjzmc,t1.yjzbm,t1.yzxmc,t1.yzxh,t1.xs,t1.dj,sum(z2.sum_yzsl) num,sum(z2.sum_yzsl * t1.xs) jzl,sum(z2.sum_yzsl * t1.xs * t1.dj) bonus, z2.newDate createDate,'0' as type,'1' as dbFlag from ( ( select y.jxdymc,y.jxdybm,y.yjzmc,y.yjzbm,y.jsksmc,y.jsksh,y.yzxmc,y.yzxh,y.xs,y.dj from t_yjxm y where y.type = '2' and y.flag = '1' and y.deleteFlag = '1' and y.jsksmc = '脑电图室' ) t1 left join ( select z.jsksmc,z.jsksh,z.yzxmc,z.yzxh,substr(z.date,1,7) newDate,sum(z.yzsl) sum_yzsl from t_mz_charge z where z.date like '2018-11%' and z.brksmc = '神内门诊' group by z.jsksmc,z.jsksh,z.yzxmc,z.yzxh,substr(z.date,1,7) ) z2 on t1.jsksh = z2.jsksh and t1.yzxh = z2.yzxh ) where z2.sum_yzsl is not null group by t1.jxdymc,t1.jxdybm,t1.yjzmc,t1.yjzbm,t1.jsksmc,t1.jsksh,t1.yzxmc,t1.yzxh,t1.dj union all #住院无艾默生医技科室奖金(神经内科-脑电图室) #神经内科脑电图项目提取需“接收科室”与“病人科室”两个条件同时满足 #数据源 病人科室 接收科室 #门诊收费数据 神内门诊 脑电图室 #住院收费数据 神经内科 脑电图室 select t1.jxdymc,t1.jxdybm,t1.yjzmc,t1.yjzbm,t1.yzxmc,t1.yzxh,t1.xs,t1.dj,sum(z2.sum_yzsl) num,sum(z2.sum_yzsl * t1.xs) jzl,sum(z2.sum_yzsl * t1.xs * t1.dj) bonus, z2.newDate createDate,'0' as type,'2' as dbFlag from ( ( select y.jxdymc,y.jxdybm,y.yjzmc,y.yjzbm,y.jsksmc,y.jsksh,y.yzxmc,y.yzxh,y.xs,y.dj from t_yjxm y where y.type = '2' and y.flag = '1' and y.deleteFlag = '1' and y.jsksmc = '脑电图室' ) t1 left join ( select z.jsksmc,z.jsksh,z.yzxmc,z.yzxh,substr(z.date,1,7) newDate,sum(z.yzsl) sum_yzsl from t_zy_charge z where z.date like '2018-11%' and z.brksmc = '神经内科' group by z.jsksmc,z.jsksh,z.yzxmc,z.yzxh,substr(z.date,1,7) ) z2 on t1.jsksh = z2.jsksh and t1.yzxh = z2.yzxh ) where z2.sum_yzsl is not null group by t1.jxdymc,t1.jxdybm,t1.yjzmc,t1.yjzbm,t1.jsksmc,t1.jsksh,t1.yzxmc,t1.yzxh,t1.dj union all #急诊无艾默生医技科室奖金(神经内科-脑电图室) #神经内科脑电图项目提取需“接收科室”与“病人科室”两个条件同时满足 #数据源 病人科室 接收科室 #门诊收费数据 神内门诊 脑电图室 #住院收费数据 神经内科 脑电图室 select t1.jxdymc,t1.jxdybm,t1.yjzmc,t1.yjzbm,t1.yzxmc,t1.yzxh,t1.xs,t1.dj,sum(z2.sum_yzsl) num,sum(z2.sum_yzsl * t1.xs) jzl,sum(z2.sum_yzsl * t1.xs * t1.dj) bonus, z2.newDate createDate,'0' as type,'3' as dbFlag from ( ( select y.jxdymc,y.jxdybm,y.yjzmc,y.yjzbm,y.jsksmc,y.jsksh,y.yzxmc,y.yzxh,y.xs,y.dj from t_yjxm y where y.type = '2' and y.flag = '1' and y.deleteFlag = '1' and y.jsksmc = '脑电图室' ) t1 left join ( select z.jsksmc,z.jsksh,z.yzxmc,z.yzxh,substr(z.date,1,7) newDate,sum(z.yzsl) sum_yzsl from t_jz_charge z where z.date like '2018-11%' and z.brksmc in ('神内门诊','神经内科') group by z.jsksmc,z.jsksh,z.yzxmc,z.yzxh,substr(z.date,1,7) ) z2 on t1.jsksh = z2.jsksh and t1.yzxh = z2.yzxh ) where z2.sum_yzsl is not null group by t1.jxdymc,t1.jxdybm,t1.yjzmc,t1.yjzbm,t1.jsksmc,t1.jsksh,t1.yzxmc,t1.yzxh,t1.dj
### Cause: java.sql.SQLException: The total number of locks exceeds the lock table size
; uncategorized SQLException for SQL []; SQL state [HY000]; error code [1206]; The total number of locks exceeds the lock table size; nested exception is java.sql.SQLException: The total number of locks exceeds the lock table size
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:371)
at com.sun.proxy.$Proxy13.insert(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:240)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:51)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:52)
at com.sun.proxy.$Proxy188.jsWamsyj(Unknown Source)
at com.xm.yjxq.service.YjxqServiceImpl.jsWamsyj(YjxqServiceImpl.java:144)
at com.xm.yjjx.controller.YjjxController.jsYjjx(YjjxController.java:229)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:219)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:745)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:686)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:925)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:936)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:827)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:620)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:812)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at com.xm.filter.allFilter.doFilter(allFilter.java:77)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequest

[柴米油盐酱醋0]

ERROR 1206 (HY000) at line 1: The total number of locks exceeds the lock table size 这是报错信息，应该是缓冲池太小了，将MYSQL配置文件my.cnf innodb_buffer_pool_size=2147483648 #这里设置的是2G 根据自己电脑内存调整后再重试。