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
uj5u.com熱心網友回復：
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
根據自己電腦記憶體調整后再重試。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/76747.html
標籤：MySQL
上一篇：請教：如何同一實體下跨庫訪問表？