| forum.bitel.ru http://forum.bitel.ru/ |
|
| deadlocks.... http://forum.bitel.ru/viewtopic.php?f=44&t=12596 |
Страница 1 из 1 |
| Автор: | ok-2004 [ 26 авг 2017, 17:47 ] |
| Заголовок сообщения: | deadlocks.... |
Добрый день! Каждое начало дня сопровождается вот таким выхлопом в error.log BGInetAccounting - сервера ( его ип 192.168.120.120 ): Код: radius 08-25/00:00:15 ERROR [rdsLstnr-p-7-t-10] InetNas - com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction ru.bitel.bgbilling.common.BGException: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:460) at ru.bitel.bgbilling.modules.inet.accounting.Accounting.sessionFinish(Accounting.java:1786) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.splitSessionImpl(InetConnectionRuntime.java:1188) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionCallRuntime.splitSession(InetConnectionCallRuntime.java:479) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.trySplitSession(InetConnectionRuntime.java:994) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionCallRuntime.trySplitSession(InetConnectionCallRuntime.java:453) at ru.bitel.bgbilling.modules.inet.accounting.Accounting.processRadiusPacket(Accounting.java:1853) at ru.bitel.bgbilling.modules.inet.radius.InetNas.updateConnection(InetNas.java:644) at ru.bitel.bgbilling.modules.inet.radius.InetNas.updateConnection(InetNas.java:73) at ru.bitel.bgbilling.kernel.network.radius.RadiusProcessor.accounting(RadiusProcessor.java:637) at ru.bitel.bgbilling.kernel.network.radius.RadiusProcessor.accountingRequest(RadiusProcessor.java:563) at ru.bitel.bgbilling.modules.inet.radius.InetRadiusListenerWorker.accountingRequest(InetRadiusListenerWorker.java:190) at ru.bitel.bgbilling.modules.inet.radius.InetRadiusListenerWorker.accountingRequest(InetRadiusListenerWorker.java:36) at ru.bitel.bgbilling.kernel.network.radius.RadiusListenerWorker.processAccountingRequest(RadiusListenerWorker.java:335) at ru.bitel.bgbilling.kernel.network.radius.RadiusListenerWorker.processPacket(RadiusListenerWorker.java:306) at ru.bitel.bgbilling.kernel.network.radius.RadiusListenerWorker.runImpl(RadiusListenerWorker.java:155) at ru.bitel.common.worker.WorkerTask.run(WorkerTask.java:86) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:748) at ru.bitel.common.worker.WorkerThread.run(WorkerThread.java:46) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.Util.getInstance(Util.java:387) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009) at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5094) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.merge(SessionFlushingManager.java:929) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:385) ... 20 more При этом на самом сервере БЖБ ( у него ип 192.168.120.211 ) "show engine innodb status" пишет: Код: ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2017-08-25 00:00:15 0x7f601ed32700 *** (1) TRANSACTION: TRANSACTION 312224371, ACTIVE 0 sec fetching rows mysql tables in use 1, locked 1 LOCK WAIT 37 lock struct(s), heap size 3520, 161 row lock(s), undo log entries 145 MySQL thread id 7702, OS thread handle 140050366265088, query id 3103557805 192.168.120.120 bill updating DELETE FROM inet_session_detail_10 WHERE sessionId=9458549 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 115 page no 237 n bits 432 index PRIMARY of table `bgbilling`.`inet_session_detail_10` /* Partition `p5` */ trx id 312224371 lock_mode X waiting *** (2) TRANSACTION: TRANSACTION 312224372, ACTIVE 0 sec starting index read, thread declared inside InnoDB 5000 mysql tables in use 1, locked 1 36 lock struct(s), heap size 3520, 100 row lock(s), undo log entries 84 MySQL thread id 5987, OS thread handle 140050810742528, query id 3103557809 192.168.120.120 bill updating DELETE FROM inet_session_detail_10 WHERE sessionId=9458557 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 115 page no 237 n bits 432 index PRIMARY of table `bgbilling`.`inet_session_detail_10` /* Partition `p5` */ trx id 312224372 lock mode S *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 115 page no 237 n bits 432 index PRIMARY of table `bgbilling`.`inet_session_detail_10` /* Partition `p5` */ trx id 312224372 lock_mode X waiting *** WE ROLL BACK TRANSACTION (2) ------------ Может это имеет какое-нибудь значение, но эту таблицу одновременно в начале каждых суток пытаются апдейтить 3 BGInetAccounting-сервера . Один считает радиус, два остальных - нетфлоу и на этих двух ошибки в error.log в это время одинаковы: Код: collector 08-25/00:00:07 ERROR [flow-p-7-t-6] FlowAgentInterface - com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction ru.bitel.bgbilling.common.BGException: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:460) at ru.bitel.bgbilling.modules.inet.accounting.Accounting.sessionFinish(Accounting.java:1786) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.splitSessionImpl(InetConnectionRuntime.java:1188) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.splitSession(InetConnectionRuntime.java:1095) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.trySplitSession(InetConnectionRuntime.java:994) at ru.bitel.bgbilling.modules.inet.accounting.FlowAgentInterface.processFlow(FlowAgentInterface.java:212) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.processPacketImpl0(FlowListenerWorker.java:287) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.processPacketImpl(FlowListenerWorker.java:178) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorkerNetflow.processPacket(FlowListenerWorkerNetflow.java:29) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.runImpl(FlowListenerWorker.java:60) at ru.bitel.common.worker.WorkerTask.run(WorkerTask.java:86) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:748) at ru.bitel.common.worker.WorkerThread.run(WorkerThread.java:46) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.Util.getInstance(Util.java:387) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009) at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5094) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.merge(SessionFlushingManager.java:929) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:385) ... 14 more Как тут быть ? ========================== Информация о версии: Клиент: вер. 6.2.862 / 30.05.2017 03:57:55 os: Linux; java: Java HotSpot(TM) 64-Bit Server VM, v.1.8.0_131 Сервер: вер. 6.2.1184 / 30.05.2017 04:17:11 os: Linux; java: Java HotSpot(TM) 64-Bit Server VM, v.1.8.0_131 bill: вер. 6.2.103 / 12.05.2017 18:50:32 card: вер. 6.2.43 / 16.03.2017 23:47:47 dialup: вер. 6.2.316 / 12.05.2017 18:52:28 drweb: вер. 6.2.71 / 09.03.2016 11:43:11 email: вер. 6.2.177 / 14.12.2016 14:27:42 inet: вер. 6.2.720 / 30.05.2017 03:55:47 ipn: вер. 6.2.264 / 09.01.2017 16:41:56 mps: вер. 6.2.225 / 17.05.2017 18:35:37 npay: вер. 6.2.200 / 30.05.2017 04:17:18 payonline: вер. 6.2.65 / 01.06.2017 15:55:49 rentsoft: вер. 6.2.63 / 04.10.2016 19:16:26 reports: вер. 6.2.217 / 02.05.2017 12:37:22 ru.bitel.bgbilling.plugins.crm: вер. 6.2.195 / 04.10.2016 19:16:05 ru.bitel.bgbilling.plugins.dispatch: вер. 6.2.97 / 12.05.2017 18:50:40 ru.bitel.bgbilling.plugins.documents: вер. 6.2.176 / 14.12.2016 14:33:16 ru.bitel.bgbilling.plugins.helpdesk: вер. 6.2.182 / 04.10.2016 19:16:06 trayinfo: вер. 6.2.171 / 10.03.2017 18:11:41 voiceip: вер. 6.2.192 / 12.05.2017 18:50:31 wm: вер. 6.2.189 / 04.10.2016 19:16:46 yamoney: вер. 6.2.58 / 04.10.2016 19:16:49 Окружение: Клиент: os: Linux amd64, 4.10.0-30-generic java: Java HotSpot(TM) 64-Bit Server VM, v.1.8.0_131 jre home: /home/oleg/BGBillingClient_6.2/jdk1.8.0_131/jre default tz: 09.08.2017 13:42 MSK +0300 (Europe/Moscow) user tz: 09.08.2017 13:42 MSK +0300 (Europe/Moscow) locale: ru_RU Сервер: os: Linux amd64, 4.4.0-78-generic java: Java HotSpot(TM) 64-Bit Server VM, v.1.8.0_131 jre home: /opt/java/jdk1.8.0_131/jre default tz: 09.08.2017 13:42 MSK +0300 (Europe/Moscow) db time: master: 09.08.2017 13:42 MSK db charset: connection: utf8(utf8_unicode_ci), database: utf8(utf8_unicode_ci) locale: en_US uptime: Started: 11.06.2017 08:18:27 Uptime: 59 d 05:24:01 |
|
| Автор: | Amir [ 29 авг 2017, 21:48 ] |
| Заголовок сообщения: | Re: deadlocks.... |
А очень много таких ошибок? Вообще по документации MySQL это считается нормальным (т.к. кроме lock на записи, он еще ставит gap-lock'и при выборке записей), нужно попробовать снова, что и делается. Другое дело, если это мешает работе или происходит слишком часто - тогда наверно надо пытаться оптимизировать. |
|
| Автор: | ok-2004 [ 30 авг 2017, 16:48 ] |
| Заголовок сообщения: | Re: deadlocks.... |
Ошибки тока на границе суток.... |
|
| Автор: | Amir [ 30 авг 2017, 19:20 ] |
| Заголовок сообщения: | Re: deadlocks.... |
Ну это понятно, там начинает сплитить сессии -> работа с базой. Много-мало, т.е. 3, 10, 100, все логи завалены этими ошибками? |
|
| Автор: | ok-2004 [ 17 ноя 2017, 10:08 ] |
| Заголовок сообщения: | Re: deadlocks.... |
Извиняюсь за некро-UP. Цитата: Ну это понятно, там начинает сплитить сессии -> работа с базой. Да, похоже на то. Вот типичный err-лог mysql на границе суток, когда на запись в его таблицы конкурируют 3 независимых BGInetAccess/BGInetAccounting сервера. ( сиё есть лишь фрагмент токмо ради наглядности ибо сессий сплитится много и откатов тракзакций весьма не мало ) Вложение:
|
|
| Автор: | ok-2004 [ 18 ноя 2017, 11:30 ] |
| Заголовок сообщения: | Re: deadlocks.... |
Итак. очередной посуточный репорт. Вкратце на помню . что мы наблюдаем за войной 3-х Accounting серверов с ип-ами ...120 , ...209 , ...212 за таблицы базы на границе суток. В качестве эксперимента передвинул часы на 120-м сервере на несколько секунд вперёд. обеспечив ему преймущество перед 209 и 212 серверами. Сиё действо помогло и ошибки на 120 сервере пропали. на 209-м : Код: accounting 11-18/00:01:03 ERROR [accwrkr-2-p-12-t-1] SessionFlushingWorker - com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction ru.bitel.bgbilling.common.BGException: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:460) at ru.bitel.bgbilling.modules.inet.accounting.worker.SessionFlushingWorker.runImpl(SessionFlushingWorker.java:160) at ru.bitel.common.worker.WorkerTask.run(WorkerTask.java:86) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:748) at ru.bitel.common.worker.WorkerThread.run(WorkerThread.java:46) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.Util.getInstance(Util.java:387) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009) at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5094) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flushDetailDelta(SessionFlushingManager.java:668) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flushDelta(SessionFlushingManager.java:517) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:432) ... 10 more и на 212-ом : Код: collector 11-18/00:00:15 ERROR [flow-p-7-t-2] FlowAgentInterface - com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction ru.bitel.bgbilling.common.BGException: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:460) at ru.bitel.bgbilling.modules.inet.accounting.Accounting.sessionFinish(Accounting.java:1786) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.splitSessionImpl(InetConnectionRuntime.java:1188) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.splitSession(InetConnectionRuntime.java:1095) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.trySplitSession(InetConnectionRuntime.java:994) at ru.bitel.bgbilling.modules.inet.accounting.FlowAgentInterface.processFlow(FlowAgentInterface.java:212) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.processPacketImpl0(FlowListenerWorker.java:287) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.processPacketImpl(FlowListenerWorker.java:178) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorkerNetflow.processPacket(FlowListenerWorkerNetflow.java:29) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.runImpl(FlowListenerWorker.java:60) at ru.bitel.common.worker.WorkerTask.run(WorkerTask.java:86) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:748) at ru.bitel.common.worker.WorkerThread.run(WorkerThread.java:46) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.Util.getInstance(Util.java:387) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009) at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5094) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.merge(SessionFlushingManager.java:916) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:385) остались... Error лог Mysql: Код: 2017-11-17T23:59:54.854746+03:00 31242 [Note] InnoDB: Transactions deadlock detected, dumping detailed information. 2017-11-17T23:59:54.854764+03:00 31242 [Note] InnoDB: *** (1) TRANSACTION: TRANSACTION 661290141, ACTIVE 0 sec fetching rows mysql tables in use 1, locked 1 LOCK WAIT 36 lock struct(s), heap size 3520, 97 row lock(s), undo log entries 81 MySQL thread id 31250, OS thread handle 140050813404928, query id 6510028895 192.168.120.212 bill updating DELETE FROM inet_session_account_10 WHERE sessionId=10018251 AND contractId=4505 2017-11-17T23:59:54.854785+03:00 31242 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 524 page no 6 n bits 400 index PRIMARY of table `bgbilling`.`inet_session_account_10` /* Partition `p1` */ trx id 661290141 lock_mode X waiting Record lock, heap no 169 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 4; hex 800011a1; asc ;; 1: len 8; hex 800000000098eb32; asc 2;; 2: len 4; hex 80000022; asc ";; 3: len 6; hex 0000276a7c9a; asc 'j| ;; 4: len 7; hex 5e000008410ac0; asc ^ A ;; 5: len 8; hex 800000000000cfeb; asc ;; 6: len 6; hex 800000000000; asc ;; 2017-11-17T23:59:54.854931+03:00 31242 [Note] InnoDB: *** (2) TRANSACTION: TRANSACTION 661290138, ACTIVE 0 sec starting index read, thread declared inside InnoDB 5000 mysql tables in use 2, locked 2 30 lock struct(s), heap size 3520, 67 row lock(s), undo log entries 54 MySQL thread id 31242, OS thread handle 140050132592384, query id 6510028908 192.168.120.212 bill Sending data INSERT INTO inet_session_log_account_10_201711 SELECT * FROM inet_session_account_10 WHERE sessionId=10021682 AND contractId=4513 2017-11-17T23:59:54.854948+03:00 31242 [Note] InnoDB: *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 524 page no 6 n bits 400 index PRIMARY of table `bgbilling`.`inet_session_account_10` /* Partition `p1` */ trx id 661290138 lock_mode X locks rec but not gap Record lock, heap no 169 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 4; hex 800011a1; asc ;; 1: len 8; hex 800000000098eb32; asc 2;; 2: len 4; hex 80000022; asc ";; 3: len 6; hex 0000276a7c9a; asc 'j| ;; 4: len 7; hex 5e000008410ac0; asc ^ A ;; 5: len 8; hex 800000000000cfeb; asc ;; 6: len 6; hex 800000000000; asc ;; Record lock, heap no 265 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 4; hex 800011a1; asc ;; 1: len 8; hex 800000000098eb32; asc 2;; 2: len 4; hex 80000023; asc #;; 3: len 6; hex 0000276a7c9a; asc 'j| ;; 4: len 7; hex 5e000008410af4; asc ^ A ;; 5: len 8; hex 800000012e4005e8; asc .@ ;; 6: len 6; hex 800000000000; asc ;; Record lock, heap no 311 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 4; hex 800011a1; asc ;; 1: len 8; hex 800000000098eb32; asc 2;; 2: len 4; hex 80000024; asc $;; 3: len 6; hex 0000276a7c9a; asc 'j| ;; 4: len 7; hex 5e000008410b28; asc ^ A (;; 5: len 8; hex 800000000e570d0d; asc W ;; 6: len 6; hex 800000000000; asc ;; 2017-11-17T23:59:54.855323+03:00 31242 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 524 page no 6 n bits 400 index PRIMARY of table `bgbilling`.`inet_session_account_10` /* Partition `p1` */ trx id 661290138 lock mode S waiting Record lock, heap no 169 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 4; hex 800011a1; asc ;; 1: len 8; hex 800000000098eb32; asc 2;; 2: len 4; hex 80000022; asc ";; 3: len 6; hex 0000276a7c9a; asc 'j| ;; 4: len 7; hex 5e000008410ac0; asc ^ A ;; 5: len 8; hex 800000000000cfeb; asc ;; 6: len 6; hex 800000000000; asc ;; 2017-11-17T23:59:54.855451+03:00 31242 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2) 2017-11-18T00:00:59.602976+03:00 31110 [Note] InnoDB: Transactions deadlock detected, dumping detailed information. 2017-11-18T00:00:59.602988+03:00 31110 [Note] InnoDB: *** (1) TRANSACTION: TRANSACTION 661297827, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 6 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 1 MySQL thread id 31247, OS thread handle 140050129397504, query id 6510080096 192.168.120.209 bill update INSERT INTO inet_session_detail_10 (sessionId, day, hour, trafficTypeId, deviceId, amount) VALUES (10025583,18,0,1,25,217096) 2017-11-18T00:00:59.603009+03:00 31110 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 117 page no 70 n bits 360 index PRIMARY of table `bgbilling`.`inet_session_detail_10` /* Partition `p7` */ trx id 661297827 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 180 PHYSICAL RECORD: n_fields 8; compact format; info bits 0 0: len 8; hex 800000000098fa7f; asc ;; 1: len 1; hex 92; asc ;; 2: len 1; hex 80; asc ;; 3: len 4; hex 80000000; asc ;; 4: len 4; hex 80000000; asc ;; 5: len 6; hex 0000276a8eef; asc 'j ;; 6: len 7; hex ac0000082f012f; asc / /;; 7: len 8; hex 8000000000000014; asc ;; 2017-11-18T00:00:59.603154+03:00 31110 [Note] InnoDB: *** (2) TRANSACTION: TRANSACTION 661297826, ACTIVE 0 sec inserting, thread declared inside InnoDB 5000 mysql tables in use 1, locked 1 7 lock struct(s), heap size 1136, 7 row lock(s), undo log entries 1 MySQL thread id 31110, OS thread handle 140050502739712, query id 6510080097 192.168.120.212 bill update INSERT INTO inet_session_detail_10 (sessionId, day, hour, trafficTypeId, deviceId, amount) VALUES (10025591,18,0,0,0,34) 2017-11-18T00:00:59.603170+03:00 31110 [Note] InnoDB: *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 117 page no 70 n bits 360 index PRIMARY of table `bgbilling`.`inet_session_detail_10` /* Partition `p7` */ trx id 661297826 lock_mode X locks gap before rec Record lock, heap no 180 PHYSICAL RECORD: n_fields 8; compact format; info bits 0 0: len 8; hex 800000000098fa7f; asc ;; 1: len 1; hex 92; asc ;; 2: len 1; hex 80; asc ;; 3: len 4; hex 80000000; asc ;; 4: len 4; hex 80000000; asc ;; 5: len 6; hex 0000276a8eef; asc 'j ;; 6: len 7; hex ac0000082f012f; asc / /;; 7: len 8; hex 8000000000000014; asc ;; 2017-11-18T00:00:59.603293+03:00 31110 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 117 page no 70 n bits 360 index PRIMARY of table `bgbilling`.`inet_session_detail_10` /* Partition `p7` */ trx id 661297826 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 180 PHYSICAL RECORD: n_fields 8; compact format; info bits 0 0: len 8; hex 800000000098fa7f; asc ;; 1: len 1; hex 92; asc ;; 2: len 1; hex 80; asc ;; 3: len 4; hex 80000000; asc ;; 4: len 4; hex 80000000; asc ;; 5: len 6; hex 0000276a8eef; asc 'j ;; 6: len 7; hex ac0000082f012f; asc / /;; 7: len 8; hex 8000000000000014; asc ;; 2017-11-18T00:00:59.603417+03:00 31110 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (1) Да. масштабтируемость как-то хромает....  Посоветуйте - как мне поступить ? : 1. Создать несколько экземпляров модуля inet. , дабы суффикс X таблиц inet_session_detail_X, inet_session _account_X и пр. был разный. Каждому экземпляру модуля inet - своя группа Access/Accounting . 2. Просить Вас в HD сплитнуть эти таблицы на inet_session_detail_X_Y, inet_session _account_X_Y и пр. .где Y - id Accounting сервера в дереве Inet . До завтра... |
|
| Автор: | ok-2004 [ 19 ноя 2017, 15:33 ] |
| Заголовок сообщения: | Re: deadlocks.... |
Приятного всем воскресения! А мы продолжаем .... Сдвинул время на Acccess/Accounting серверах: -в начале начинает сплитить сессии на границе суток 120-ый. У него деадлоков естественно не наблюдается. -через 10 сек в дело вступает 209-ый . И он чисто отрабатывает. -но время на 212-ом сдвинуто всего лишь на 1 сек относительно 209-ого, и ему не повезло : Код: 2017-11-18T23:59:38.340611+03:00 31248 [Note] InnoDB: Transactions deadlock detected, dumping detailed information. 2017-11-18T23:59:38.340628+03:00 31248 [Note] InnoDB: *** (1) TRANSACTION: TRANSACTION 665292795, ACTIVE 0 sec fetching rows mysql tables in use 1, locked 1 LOCK WAIT 36 lock struct(s), heap size 3520, 102 row lock(s), undo log entries 87 MySQL thread id 31251, OS thread handle 140050128332544, query id 6549380062 192.168.120.212 bill updating DELETE FROM inet_session_account_10 WHERE sessionId=10025171 AND contractId=11960 2017-11-18T23:59:38.340648+03:00 31248 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 523 page no 15 n bits 312 index PRIMARY of table `bgbilling`.`inet_session_account_10` /* Partition `p0` */ trx id 665292795 lock_mode X waiting Record lock, heap no 172 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 4; hex 80002ec0; asc . ;; 1: len 8; hex 800000000098f862; asc b;; 2: len 4; hex 80000022; asc ";; 3: len 6; hex 000027a78ffe; asc ' ;; 4: len 7; hex 60000008010213; asc ` ;; 5: len 8; hex 8000000000015180; asc Q ;; 6: len 6; hex 800000000000; asc ;; 2017-11-18T23:59:38.340807+03:00 31248 [Note] InnoDB: *** (2) TRANSACTION: TRANSACTION 665292798, ACTIVE 0 sec starting index read, thread declared inside InnoDB 5000 mysql tables in use 2, locked 2 31 lock struct(s), heap size 3520, 95 row lock(s), undo log entries 81 MySQL thread id 31248, OS thread handle 140050811541248, query id 6549380063 192.168.120.212 bill Sending data INSERT INTO inet_session_log_account_10_201711 SELECT * FROM inet_session_account_10 WHERE sessionId=10025058 AND contractId=11968 2017-11-18T23:59:38.340825+03:00 31248 [Note] InnoDB: *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 523 page no 15 n bits 312 index PRIMARY of table `bgbilling`.`inet_session_account_10` /* Partition `p0` */ trx id 665292798 lock_mode X locks rec but not gap Record lock, heap no 172 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 4; hex 80002ec0; asc . ;; 1: len 8; hex 800000000098f862; asc b;; 2: len 4; hex 80000022; asc ";; 3: len 6; hex 000027a78ffe; asc ' ;; 4: len 7; hex 60000008010213; asc ` ;; 5: len 8; hex 8000000000015180; asc Q ;; 6: len 6; hex 800000000000; asc ;; Record lock, heap no 173 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 4; hex 80002ec0; asc . ;; 1: len 8; hex 800000000098f862; asc b;; 2: len 4; hex 80000023; asc #;; 3: len 6; hex 000027a78ffe; asc ' ;; 4: len 7; hex 60000008010247; asc ` G;; 5: len 8; hex 80000000456ae84d; asc Ej M;; 6: len 6; hex 800000000000; asc ;; Record lock, heap no 174 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 4; hex 80002ec0; asc . ;; 1: len 8; hex 800000000098f862; asc b;; 2: len 4; hex 80000024; asc $;; 3: len 6; hex 000027a78ffe; asc ' ;; 4: len 7; hex 6000000801027b; asc ` {;; 5: len 8; hex 80000000055d7940; asc ]y@;; 6: len 6; hex 800000000000; asc ;; 2017-11-18T23:59:38.341208+03:00 31248 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 523 page no 15 n bits 312 index PRIMARY of table `bgbilling`.`inet_session_account_10` /* Partition `p0` */ trx id 665292798 lock mode S waiting Record lock, heap no 172 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 4; hex 80002ec0; asc . ;; 1: len 8; hex 800000000098f862; asc b;; 2: len 4; hex 80000022; asc ";; 3: len 6; hex 000027a78ffe; asc ' ;; 4: len 7; hex 60000008010213; asc ` ;; 5: len 8; hex 8000000000015180; asc Q ;; 6: len 6; hex 800000000000; asc ;; 2017-11-18T23:59:38.341331+03:00 31248 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2) 2017-11-18T23:59:47.927041+03:00 31251 [Note] InnoDB: Transactions deadlock detected, dumping detailed information. 2017-11-18T23:59:47.927053+03:00 31251 [Note] InnoDB: *** (1) TRANSACTION: TRANSACTION 665295344, ACTIVE 0 sec fetching rows mysql tables in use 1, locked 1 LOCK WAIT 36 lock struct(s), heap size 3520, 42 row lock(s), undo log entries 27 MySQL thread id 31242, OS thread handle 140050132592384, query id 6549394804 192.168.120.212 bill updating DELETE FROM inet_session_account_10 WHERE sessionId=10030373 AND contractId=4018 2017-11-18T23:59:47.927072+03:00 31251 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 525 page no 19 n bits 352 index PRIMARY of table `bgbilling`.`inet_session_account_10` /* Partition `p2` */ trx id 665295344 lock_mode X waiting Record lock, heap no 54 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 4; hex 80000fba; asc ;; 1: len 8; hex 8000000000990ccd; asc ;; 2: len 4; hex 80000022; asc ";; 3: len 6; hex 000027a799f1; asc ' ;; 4: len 7; hex 4a000003b21c61; asc J a;; 5: len 8; hex 8000000000003cb3; asc < ;; 6: len 6; hex 800000000000; asc ;; 2017-11-18T23:59:47.927206+03:00 31251 [Note] InnoDB: *** (2) TRANSACTION: TRANSACTION 665295345, ACTIVE 0 sec starting index read, thread declared inside InnoDB 5000 mysql tables in use 1, locked 1 35 lock struct(s), heap size 3520, 42 row lock(s), undo log entries 27 MySQL thread id 31251, OS thread handle 140050128332544, query id 6549394809 192.168.120.212 bill updating DELETE FROM inet_session_account_10 WHERE sessionId=10030285 AND contractId=4026 2017-11-18T23:59:47.927222+03:00 31251 [Note] InnoDB: *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 525 page no 19 n bits 352 index PRIMARY of table `bgbilling`.`inet_session_account_10` /* Partition `p2` */ trx id 665295345 lock_mode X locks rec but not gap Record lock, heap no 54 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 4; hex 80000fba; asc ;; 1: len 8; hex 8000000000990ccd; asc ;; 2: len 4; hex 80000022; asc ";; 3: len 6; hex 000027a799f1; asc ' ;; 4: len 7; hex 4a000003b21c61; asc J a;; 5: len 8; hex 8000000000003cb3; asc < ;; 6: len 6; hex 800000000000; asc ;; Record lock, heap no 55 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 4; hex 80000fba; asc ;; 1: len 8; hex 8000000000990ccd; asc ;; 2: len 4; hex 80000023; asc #;; 3: len 6; hex 000027a799f1; asc ' ;; 4: len 7; hex 4a000003b21c95; asc J ;; 5: len 8; hex 800000000c3380da; asc 3 ;; 6: len 6; hex 800000000000; asc ;; Record lock, heap no 56 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 4; hex 80000fba; asc ;; 1: len 8; hex 8000000000990ccd; asc ;; 2: len 4; hex 80000024; asc $;; 3: len 6; hex 000027a799f1; asc ' ;; 4: len 7; hex 4a000003b21cc9; asc J ;; 5: len 8; hex 8000000001e51986; asc ;; 6: len 6; hex 800000000000; asc ;; 2017-11-18T23:59:47.927668+03:00 31251 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 525 page no 19 n bits 352 index PRIMARY of table `bgbilling`.`inet_session_account_10` /* Partition `p2` */ trx id 665295345 lock_mode X waiting Record lock, heap no 54 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 4; hex 80000fba; asc ;; 1: len 8; hex 8000000000990ccd; asc ;; 2: len 4; hex 80000022; asc ";; 3: len 6; hex 000027a799f1; asc ' ;; 4: len 7; hex 4a000003b21c61; asc J a;; 5: len 8; hex 8000000000003cb3; asc < ;; 6: len 6; hex 800000000000; asc ;; 2017-11-18T23:59:47.927798+03:00 31251 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2) Можно конечно и ему сдвинуть время на большую дельту. но как-то не правильно всё это... Продолжаю наблюдения.  До понедельника...
|
|
| Автор: | ok-2004 [ 20 ноя 2017, 11:39 ] | ||
| Заголовок сообщения: | Re: deadlocks.... | ||
Доброго всем понедельника. Сегодняшняя ночь по народным поверьям - ночь деадлоков . свежие утренние логи : 209-ый: Код: collector 11-20/00:00:03 ERROR [flow-p-7-t-2] FlowAgentInterface - com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction ru.bitel.bgbilling.common.BGException: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:460) at ru.bitel.bgbilling.modules.inet.accounting.Accounting.sessionFinish(Accounting.java:1786) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.splitSessionImpl(InetConnectionRuntime.java:1188) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.splitSession(InetConnectionRuntime.java:1095) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.trySplitSession(InetConnectionRuntime.java:994) at ru.bitel.bgbilling.modules.inet.accounting.FlowAgentInterface.processFlow(FlowAgentInterface.java:212) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.processPacketImpl0(FlowListenerWorker.java:287) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.processPacketImpl(FlowListenerWorker.java:178) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorkerNetflow.processPacket(FlowListenerWorkerNetflow.java:29) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.runImpl(FlowListenerWorker.java:60) at ru.bitel.common.worker.WorkerTask.run(WorkerTask.java:86) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:748) at ru.bitel.common.worker.WorkerThread.run(WorkerThread.java:46) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.Util.getInstance(Util.java:387) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009) at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5094) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.merge(SessionFlushingManager.java:929) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:385) ... 14 more 212-ый : Код: collector 11-20/00:00:03 ERROR [flow-p-7-t-8] FlowAgentInterface - com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction ru.bitel.bgbilling.common.BGException: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:460) at ru.bitel.bgbilling.modules.inet.accounting.Accounting.sessionFinish(Accounting.java:1786) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.splitSessionImpl(InetConnectionRuntime.java:1188) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.splitSession(InetConnectionRuntime.java:1095) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.trySplitSession(InetConnectionRuntime.java:994) at ru.bitel.bgbilling.modules.inet.accounting.FlowAgentInterface.processFlow(FlowAgentInterface.java:212) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.processPacketImpl0(FlowListenerWorker.java:323) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.processPacketImpl(FlowListenerWorker.java:178) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorkerNetflow.processPacket(FlowListenerWorkerNetflow.java:29) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.runImpl(FlowListenerWorker.java:60) at ru.bitel.common.worker.WorkerTask.run(WorkerTask.java:86) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:748) at ru.bitel.common.worker.WorkerThread.run(WorkerThread.java:46) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.Util.getInstance(Util.java:387) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009) at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5094) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.merge(SessionFlushingManager.java:929) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:385) ... 14 more collector 11-20/00:00:13 ERROR [flow-p-7-t-1] FlowAgentInterface - com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction ru.bitel.bgbilling.common.BGException: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:460) at ru.bitel.bgbilling.modules.inet.accounting.Accounting.sessionFinish(Accounting.java:1786) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.splitSessionImpl(InetConnectionRuntime.java:1188) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.splitSession(InetConnectionRuntime.java:1095) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.trySplitSession(InetConnectionRuntime.java:994) at ru.bitel.bgbilling.modules.inet.accounting.FlowAgentInterface.processFlow(FlowAgentInterface.java:212) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.processPacketImpl0(FlowListenerWorker.java:323) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.processPacketImpl(FlowListenerWorker.java:178) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorkerNetflow.processPacket(FlowListenerWorkerNetflow.java:29) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.runImpl(FlowListenerWorker.java:60) at ru.bitel.common.worker.WorkerTask.run(WorkerTask.java:86) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:748) at ru.bitel.common.worker.WorkerThread.run(WorkerThread.java:46) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.Util.getInstance(Util.java:387) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009) at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5094) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.merge(SessionFlushingManager.java:929) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:385) ... 14 more accounting 11-20/00:00:28 ERROR [accwrkr-2-p-12-t-1] SessionFlushingWorker - com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction ru.bitel.bgbilling.common.BGException: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:460) at ru.bitel.bgbilling.modules.inet.accounting.worker.SessionFlushingWorker.runImpl(SessionFlushingWorker.java:160) at ru.bitel.common.worker.WorkerTask.run(WorkerTask.java:86) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:748) at ru.bitel.common.worker.WorkerThread.run(WorkerThread.java:46) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.Util.getInstance(Util.java:387) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009) at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5094) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flushDetailDelta(SessionFlushingManager.java:668) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flushDelta(SessionFlushingManager.java:517) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:432) ... 10 more accounting 11-20/00:00:55 ERROR [accwrkr-2-p-12-t-1] SessionFlushingWorker - com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction ru.bitel.bgbilling.common.BGException: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:460) at ru.bitel.bgbilling.modules.inet.accounting.worker.SessionFlushingWorker.runImpl(SessionFlushingWorker.java:160) at ru.bitel.common.worker.WorkerTask.run(WorkerTask.java:86) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:748) at ru.bitel.common.worker.WorkerThread.run(WorkerThread.java:46) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.Util.getInstance(Util.java:387) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009) at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5094) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flushDelta(SessionFlushingManager.java:594) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:432) ... 10 more error log Mysql :
|
|||
| Автор: | Amir [ 20 ноя 2017, 16:38 ] |
| Заголовок сообщения: | Re: deadlocks.... |
И логи InnoDB как в предыдущем сообщении в личку еще можно? |
|
| Автор: | ok-2004 [ 20 ноя 2017, 16:44 ] |
| Заголовок сообщения: | Re: deadlocks.... |
Цитата: И логи InnoDB как в предыдущем сообщении в личку еще можно? what ? 
|
|
| Автор: | ok-2004 [ 24 ноя 2017, 10:48 ] |
| Заголовок сообщения: | Re: deadlocks.... |
Доброе У ! Прошу прощения за 2-х дневное отсутствие в треде  ( ничего нового в деадлоках не случилось....)А вот вчера вечером произошла любопытная история, примечательная тем , что случилась она не на границе суток и спор между собой вели Access сервера 209 и 120-го хостов, а не Accounting сервера , как было раньше... Трагичность ситуации добавляет то обстоятельство , что победу за cat /var/lib/mysql/error.log : Код: 2017-11-23T20:41:03.518177+03:00 31169 [Note] InnoDB: Transactions deadlock detected, dumping detailed information. 2017-11-23T20:41:03.518193+03:00 31169 [Note] InnoDB: *** (1) TRANSACTION: TRANSACTION 684051147, ACTIVE 0 sec fetching rows mysql tables in use 1, locked 1 LOCK WAIT 14 lock struct(s), heap size 1136, 360 row lock(s) MySQL thread id 34083, OS thread handle 140050813404928, query id 6736432268 192.168.120.209 bill updating DELETE FROM inet_connection_10 WHERE connectionStart<'2017-11-22 20:00:00' AND status=0 2017-11-23T20:41:03.518213+03:00 31169 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 93 page no 22 n bits 112 index GEN_CLUST_INDEX of table `bgbilling`.`inet_connection_10` /* Partition `p1` */ trx id 684051147 lock_mode X waiting Record lock, heap no 24 PHYSICAL RECORD: n_fields 22; compact format; info bits 0 0: len 6; hex 000005043b44; asc ;D;; 1: len 6; hex 000028c5cac6; asc ( ;; 2: len 7; hex 4e0000043e29a3; asc N >) ;; 3: len 8; hex 80000000006b759a; asc ku ;; 4: len 8; hex 8000000000000000; asc ;; 5: len 4; hex 80000011; asc ;; 6: len 4; hex 800ec882; asc ;; 7: len 4; hex 80000000; asc ;; 8: SQL NULL; 9: len 8; hex 3831396563366363; asc 819ec6cc;; 10: len 11; hex 676f727961636865765f61; asc goryachev_a;; 11: len 4; hex 80000000; asc ;; 12: len 2; hex 8000; asc ;; 13: len 4; hex 80000aa3; asc ;; 14: len 12; hex 3139322e3136382e32302e31; asc 192.168.20.1;; 15: len 14; hex 3139322e3136382e32302e323139; asc 192.168.20.219;; 16: len 4; hex 80000242; asc B;; 17: len 4; hex 0a44f623; asc D #;; 18: len 5; hex 999e2f49bb; asc /I ;; 19: len 2; hex 8001; asc ;; 20: len 30; hex 332020202020202020202020202020202020202020202020202020202020; asc 3 ; (total 250 bytes); 21: len 2; hex 8003; asc ;; 2017-11-23T20:41:03.518678+03:00 31169 [Note] InnoDB: *** (2) TRANSACTION: TRANSACTION 684051146, ACTIVE 0 sec starting index read, thread declared inside InnoDB 5000 mysql tables in use 1, locked 1 24 lock struct(s), heap size 1136, 20 row lock(s), undo log entries 9 MySQL thread id 31169, OS thread handle 140050903742208, query id 6736432270 192.168.120.120 bill updating DELETE FROM inet_connection_10 WHERE id=7042458 AND deviceId=17 2017-11-23T20:41:03.518698+03:00 31169 [Note] InnoDB: *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 93 page no 22 n bits 112 index GEN_CLUST_INDEX of table `bgbilling`.`inet_connection_10` /* Partition `p1` */ trx id 684051146 lock mode S locks rec but not gap Record lock, heap no 24 PHYSICAL RECORD: n_fields 22; compact format; info bits 0 0: len 6; hex 000005043b44; asc ;D;; 1: len 6; hex 000028c5cac6; asc ( ;; 2: len 7; hex 4e0000043e29a3; asc N >) ;; 3: len 8; hex 80000000006b759a; asc ku ;; 4: len 8; hex 8000000000000000; asc ;; 5: len 4; hex 80000011; asc ;; 6: len 4; hex 800ec882; asc ;; 7: len 4; hex 80000000; asc ;; 8: SQL NULL; 9: len 8; hex 3831396563366363; asc 819ec6cc;; 10: len 11; hex 676f727961636865765f61; asc goryachev_a;; 11: len 4; hex 80000000; asc ;; 12: len 2; hex 8000; asc ;; 13: len 4; hex 80000aa3; asc ;; 14: len 12; hex 3139322e3136382e32302e31; asc 192.168.20.1;; 15: len 14; hex 3139322e3136382e32302e323139; asc 192.168.20.219;; 16: len 4; hex 80000242; asc B;; 17: len 4; hex 0a44f623; asc D #;; 18: len 5; hex 999e2f49bb; asc /I ;; 19: len 2; hex 8001; asc ;; 20: len 30; hex 332020202020202020202020202020202020202020202020202020202020; asc 3 ; (total 250 bytes); 21: len 2; hex 8003; asc ;; 2017-11-23T20:41:03.519154+03:00 31169 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 93 page no 22 n bits 112 index GEN_CLUST_INDEX of table `bgbilling`.`inet_connection_10` /* Partition `p1` */ trx id 684051146 lock_mode X locks rec but not gap waiting Record lock, heap no 24 PHYSICAL RECORD: n_fields 22; compact format; info bits 0 0: len 6; hex 000005043b44; asc ;D;; 1: len 6; hex 000028c5cac6; asc ( ;; 2: len 7; hex 4e0000043e29a3; asc N >) ;; 3: len 8; hex 80000000006b759a; asc ku ;; 4: len 8; hex 8000000000000000; asc ;; 5: len 4; hex 80000011; asc ;; 6: len 4; hex 800ec882; asc ;; 7: len 4; hex 80000000; asc ;; 8: SQL NULL; 9: len 8; hex 3831396563366363; asc 819ec6cc;; 10: len 11; hex 676f727961636865765f61; asc goryachev_a;; 11: len 4; hex 80000000; asc ;; 12: len 2; hex 8000; asc ;; 13: len 4; hex 80000aa3; asc ;; 14: len 12; hex 3139322e3136382e32302e31; asc 192.168.20.1;; 15: len 14; hex 3139322e3136382e32302e323139; asc 192.168.20.219;; 16: len 4; hex 80000242; asc B;; 17: len 4; hex 0a44f623; asc D #;; 18: len 5; hex 999e2f49bb; asc /I ;; 19: len 2; hex 8001; asc ;; 20: len 30; hex 332020202020202020202020202020202020202020202020202020202020; asc 3 ; (total 250 bytes); 21: len 2; hex 8003; asc ;; 2017-11-23T20:41:03.519553+03:00 31169 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (1) 209-ый в конце обиженно пробурчал : Код: access 11-23/20:41:08 ERROR [access-p-6-t-1] OldConnectionsRemover - com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
ru.bitel.bgbilling.common.BGException: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at ru.bitel.bgbilling.modules.inet.access.OldConnectionsRemover.runImpl(OldConnectionsRemover.java:72) at ru.bitel.bgbilling.modules.inet.access.OldConnectionsRemover.run(OldConnectionsRemover.java:33) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:748) at ru.bitel.common.worker.WorkerThread.run(WorkerThread.java:46) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.Util.getInstance(Util.java:387) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009) at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5094) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at ru.bitel.bgbilling.modules.inet.access.OldConnectionsRemover.runImpl(OldConnectionsRemover.java:65) ... 9 more |
|
| Автор: | ok-2004 [ 27 ноя 2017, 10:45 ] | ||
| Заголовок сообщения: | Re: deadlocks.... | ||
Алекс - Юстасу. Воскресный отчёт по деадлокам на 3-х Access/Accounting серверах с ип-ами 120 209 и 212 : 1. Деадлок в начале суток между 209-ым и 212-ым: 212: accounting/error.log Код: accounting 11-27/00:00:41 ERROR [accwrkr-2-p-11-t-1] SessionFlushingWorker - com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction ru.bitel.bgbilling.common.BGException: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:460) at ru.bitel.bgbilling.modules.inet.accounting.worker.SessionFlushingWorker.runImpl(SessionFlushingWorker.java:160) at ru.bitel.common.worker.WorkerTask.run(WorkerTask.java:86) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:748) at ru.bitel.common.worker.WorkerThread.run(WorkerThread.java:46) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.Util.getInstance(Util.java:387) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009) at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5094) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flushDelta(SessionFlushingManager.java:594) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:432) 209: accounting/error.log: Код: accounting 11-26/00:00:37 ERROR [accwrkr-2-p-12-t-1] SessionFlushingWorker - com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction ru.bitel.bgbilling.common.BGException: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:460) at ru.bitel.bgbilling.modules.inet.accounting.worker.SessionFlushingWorker.runImpl(SessionFlushingWorker.java:160) at ru.bitel.common.worker.WorkerTask.run(WorkerTask.java:86) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:748) at ru.bitel.common.worker.WorkerThread.run(WorkerThread.java:46) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.Util.getInstance(Util.java:387) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009) at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5094) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flushDetailDelta(SessionFlushingManager.java:668) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flushDelta(SessionFlushingManager.java:517) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:432) ... 10 more mysql error log: Код: 2017-11-26T00:00:31.502532+03:00 31243 [Note] InnoDB: Transactions deadlock detected, dumping detailed information. 2017-11-26T00:00:31.502548+03:00 31243 [Note] InnoDB: *** (1) TRANSACTION: TRANSACTION 692601294, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 6 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 1 MySQL thread id 33882, OS thread handle 140050812073728, query id 6820763161 192.168.120.212 bill update INSERT INTO inet_session_detail_10 (sessionId, day, hour, trafficTypeId, deviceId, amount) VALUES (10077500,26,0,0,0,39) 2017-11-26T00:00:31.502569+03:00 31243 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 114 page no 138 n bits 384 index PRIMARY of table `bgbilling`.`inet_session_detail_10` /* Partition `p4` */ trx id 692601294 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 140 PHYSICAL RECORD: n_fields 8; compact format; info bits 0 0: len 8; hex 800000000099c55c; asc \;; 1: len 1; hex 9a; asc ;; 2: len 1; hex 80; asc ;; 3: len 4; hex 80000000; asc ;; 4: len 4; hex 80000000; asc ;; 5: len 6; hex 000029483a4a; asc )H:J;; 6: len 7; hex b20000082b0110; asc + ;; 7: len 8; hex 8000000000000028; asc (;; 2017-11-26T00:00:31.502716+03:00 31243 [Note] InnoDB: *** (2) TRANSACTION: TRANSACTION 692601295, ACTIVE 0 sec inserting, thread declared inside InnoDB 5000 mysql tables in use 1, locked 1 6 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 1 MySQL thread id 31243, OS thread handle 140050126468864, query id 6820763162 192.168.120.209 bill update INSERT INTO inet_session_detail_10 (sessionId, day, hour, trafficTypeId, deviceId, amount) VALUES (10077524,26,0,0,0,31) 2017-11-26T00:00:31.502732+03:00 31243 [Note] InnoDB: *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 114 page no 138 n bits 384 index PRIMARY of table `bgbilling`.`inet_session_detail_10` /* Partition `p4` */ trx id 692601295 lock_mode X locks gap before rec Record lock, heap no 140 PHYSICAL RECORD: n_fields 8; compact format; info bits 0 0: len 8; hex 800000000099c55c; asc \;; 1: len 1; hex 9a; asc ;; 2: len 1; hex 80; asc ;; 3: len 4; hex 80000000; asc ;; 4: len 4; hex 80000000; asc ;; 5: len 6; hex 000029483a4a; asc )H:J;; 6: len 7; hex b20000082b0110; asc + ;; 7: len 8; hex 8000000000000028; asc (;; 2017-11-26T00:00:31.502857+03:00 31243 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 114 page no 138 n bits 384 index PRIMARY of table `bgbilling`.`inet_session_detail_10` /* Partition `p4` */ trx id 692601295 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 140 PHYSICAL RECORD: n_fields 8; compact format; info bits 0 0: len 8; hex 800000000099c55c; asc \;; 1: len 1; hex 9a; asc ;; 2: len 1; hex 80; asc ;; 3: len 4; hex 80000000; asc ;; 4: len 4; hex 80000000; asc ;; 5: len 6; hex 000029483a4a; asc )H:J;; 6: len 7; hex b20000082b0110; asc + ;; 7: len 8; hex 8000000000000028; asc (;; 2017-11-26T00:00:31.502982+03:00 31243 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2) 2. Маленький деадлок днём между 120-ым и 209-ом: 209:accounting/error.log : Код: accounting 11-26/13:18:40 ERROR [accwrkr-2-p-12-t-1] SessionFlushingWorker - com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction ru.bitel.bgbilling.common.BGException: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:460) at ru.bitel.bgbilling.modules.inet.accounting.worker.SessionFlushingWorker.runImpl(SessionFlushingWorker.java:160) at ru.bitel.common.worker.WorkerTask.run(WorkerTask.java:86) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:748) at ru.bitel.common.worker.WorkerThread.run(WorkerThread.java:46) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.Util.getInstance(Util.java:387) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009) at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5094) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flushDetailDelta(SessionFlushingManager.java:668) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flushDelta(SessionFlushingManager.java:517) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:432) ... 10 more mysql/error.log: Код: 2017-11-26T13:18:34.156512+03:00 31656 [Note] InnoDB: Transactions deadlock detected, dumping detailed information. 2017-11-26T13:18:34.156527+03:00 31656 [Note] InnoDB: *** (1) TRANSACTION: TRANSACTION 694807065, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 7 lock struct(s), heap size 1136, 7 row lock(s), undo log entries 1 MySQL thread id 31347, OS thread handle 140050811275008, query id 6842710310 192.168.120.120 bill update INSERT INTO inet_session_detail_10 (sessionId, day, hour, trafficTypeId, deviceId, amount) VALUES (10080648,26,13,0,0,301) 2017-11-26T13:18:34.156549+03:00 31656 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 110 page no 134 n bits 280 index PRIMARY of table `bgbilling`.`inet_session_detail_10` /* Partition `p0` */ trx id 694807065 lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; 2017-11-26T13:18:34.156600+03:00 31656 [Note] InnoDB: *** (2) TRANSACTION: TRANSACTION 694807064, ACTIVE 0 sec inserting, thread declared inside InnoDB 5000 mysql tables in use 1, locked 1 6 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1 MySQL thread id 31656, OS thread handle 140050811807488, query id 6842710312 192.168.120.209 bill update INSERT INTO inet_session_detail_10 (sessionId, day, hour, trafficTypeId, deviceId, amount) VALUES (10080664,26,13,0,0,32) 2017-11-26T13:18:34.156619+03:00 31656 [Note] InnoDB: *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 110 page no 134 n bits 280 index PRIMARY of table `bgbilling`.`inet_session_detail_10` /* Partition `p0` */ trx id 694807064 lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; 2017-11-26T13:18:34.156660+03:00 31656 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 110 page no 134 n bits 280 index PRIMARY of table `bgbilling`.`inet_session_detail_10` /* Partition `p0` */ trx id 694807064 lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; 2017-11-26T13:18:34.156696+03:00 31656 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2) 3. Большой деадлок в конце суток между 209-ым и 212-ым: 209: accounting/error.log: Код: collector 11-27/00:00:01 ERROR [flow-p-7-t-2] FlowAgentInterface - com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction ru.bitel.bgbilling.common.BGException: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:460) at ru.bitel.bgbilling.modules.inet.accounting.Accounting.sessionFinish(Accounting.java:1786) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.splitSessionImpl(InetConnectionRuntime.java:1188) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.splitSession(InetConnectionRuntime.java:1095) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.trySplitSession(InetConnectionRuntime.java:994) at ru.bitel.bgbilling.modules.inet.accounting.FlowAgentInterface.processFlow(FlowAgentInterface.java:212) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.processPacketImpl0(FlowListenerWorker.java:323) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.processPacketImpl(FlowListenerWorker.java:178) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorkerNetflow.processPacket(FlowListenerWorkerNetflow.java:29) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.runImpl(FlowListenerWorker.java:60) at ru.bitel.common.worker.WorkerTask.run(WorkerTask.java:86) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:748) at ru.bitel.common.worker.WorkerThread.run(WorkerThread.java:46) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.Util.getInstance(Util.java:387) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009) at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5094) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.merge(SessionFlushingManager.java:929) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:385) ... 14 more collector 11-27/00:00:04 ERROR [flow-p-7-t-6] FlowAgentInterface - com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction ru.bitel.bgbilling.common.BGException: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:460) at ru.bitel.bgbilling.modules.inet.accounting.Accounting.sessionFinish(Accounting.java:1786) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.splitSessionImpl(InetConnectionRuntime.java:1188) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.splitSession(InetConnectionRuntime.java:1095) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.trySplitSession(InetConnectionRuntime.java:994) at ru.bitel.bgbilling.modules.inet.accounting.FlowAgentInterface.processFlow(FlowAgentInterface.java:212) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.processPacketImpl0(FlowListenerWorker.java:323) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.processPacketImpl(FlowListenerWorker.java:178) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorkerNetflow.processPacket(FlowListenerWorkerNetflow.java:29) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.runImpl(FlowListenerWorker.java:60) at ru.bitel.common.worker.WorkerTask.run(WorkerTask.java:86) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:748) at ru.bitel.common.worker.WorkerThread.run(WorkerThread.java:46) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.Util.getInstance(Util.java:387) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009) at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5094) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.merge(SessionFlushingManager.java:929) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:385) ... 14 more collector 11-27/00:00:05 ERROR [flow-p-7-t-7] FlowAgentInterface - com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction ru.bitel.bgbilling.common.BGException: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:460) at ru.bitel.bgbilling.modules.inet.accounting.Accounting.sessionFinish(Accounting.java:1786) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.splitSessionImpl(InetConnectionRuntime.java:1188) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.splitSession(InetConnectionRuntime.java:1095) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.trySplitSession(InetConnectionRuntime.java:994) at ru.bitel.bgbilling.modules.inet.accounting.FlowAgentInterface.processFlow(FlowAgentInterface.java:212) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.processPacketImpl0(FlowListenerWorker.java:323) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.processPacketImpl(FlowListenerWorker.java:178) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorkerNetflow.processPacket(FlowListenerWorkerNetflow.java:29) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.runImpl(FlowListenerWorker.java:60) at ru.bitel.common.worker.WorkerTask.run(WorkerTask.java:86) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:748) at ru.bitel.common.worker.WorkerThread.run(WorkerThread.java:46) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.Util.getInstance(Util.java:387) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009) at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5094) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.merge(SessionFlushingManager.java:926) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:385) ... 14 more 212:accounting/error.log: Код: collector 11-27/00:00:01 ERROR [flow-p-7-t-10] FlowAgentInterface - com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction ru.bitel.bgbilling.common.BGException: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:460) at ru.bitel.bgbilling.modules.inet.accounting.Accounting.sessionFinish(Accounting.java:1786) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.splitSessionImpl(InetConnectionRuntime.java:1188) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.splitSession(InetConnectionRuntime.java:1095) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.trySplitSession(InetConnectionRuntime.java:994) at ru.bitel.bgbilling.modules.inet.accounting.FlowAgentInterface.processFlow(FlowAgentInterface.java:212) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.processPacketImpl0(FlowListenerWorker.java:323) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.processPacketImpl(FlowListenerWorker.java:178) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorkerNetflow.processPacket(FlowListenerWorkerNetflow.java:29) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.runImpl(FlowListenerWorker.java:60) at ru.bitel.common.worker.WorkerTask.run(WorkerTask.java:86) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:748) at ru.bitel.common.worker.WorkerThread.run(WorkerThread.java:46) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.Util.getInstance(Util.java:387) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009) at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5094) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.merge(SessionFlushingManager.java:916) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:385) ... 14 more mysql/error.log: ( во вложении по тому как большой)... Продолжаю наблюдения.
|
|||
| Автор: | ok-2004 [ 28 ноя 2017, 10:14 ] | ||
| Заголовок сообщения: | Re: deadlocks.... | ||
Очередные ночные логи : 209: /usr/local/BGInetAccounting/log/error.log : Код: collector 11-28/00:00:01 ERROR [flow-p-7-t-2] FlowAgentInterface - com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction ru.bitel.bgbilling.common.BGException: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:460) at ru.bitel.bgbilling.modules.inet.accounting.Accounting.sessionFinish(Accounting.java:1786) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.splitSessionImpl(InetConnectionRuntime.java:1188) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.splitSession(InetConnectionRuntime.java:1095) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.trySplitSession(InetConnectionRuntime.java:994) at ru.bitel.bgbilling.modules.inet.accounting.FlowAgentInterface.processFlow(FlowAgentInterface.java:212) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.processPacketImpl0(FlowListenerWorker.java:323) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.processPacketImpl(FlowListenerWorker.java:178) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorkerNetflow.processPacket(FlowListenerWorkerNetflow.java:29) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.runImpl(FlowListenerWorker.java:60) at ru.bitel.common.worker.WorkerTask.run(WorkerTask.java:86) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:748) at ru.bitel.common.worker.WorkerThread.run(WorkerThread.java:46) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.Util.getInstance(Util.java:387) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009) at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5094) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.merge(SessionFlushingManager.java:926) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:385) ... 14 more accounting 11-28/00:00:40 ERROR [accwrkr-1-p-11-t-3] WorkerTask - com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction ru.bitel.bgbilling.common.BGException: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:460) at ru.bitel.bgbilling.modules.inet.accounting.Accounting.sessionFinish(Accounting.java:1786) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.splitSessionImpl(InetConnectionRuntime.java:1188) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.splitSession(InetConnectionRuntime.java:1095) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.trySplitSession(InetConnectionRuntime.java:994) at ru.bitel.bgbilling.modules.inet.accounting.worker.SessionTrackingWorker.process(SessionTrackingWorker.java:167) at ru.bitel.bgbilling.modules.inet.accounting.worker.SessionTrackingWorker.runImpl(SessionTrackingWorker.java:78) at ru.bitel.common.worker.WorkerTask.run(WorkerTask.java:86) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:748) at ru.bitel.common.worker.WorkerThread.run(WorkerThread.java:46) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.Util.getInstance(Util.java:387) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009) at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5094) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.merge(SessionFlushingManager.java:916) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:385) ... 15 more 212: /usr/local/BGInetAccounting/log/error.log : Код: collector 11-28/00:00:07 ERROR [flow-p-7-t-2] FlowAgentInterface - com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction ru.bitel.bgbilling.common.BGException: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:460) at ru.bitel.bgbilling.modules.inet.accounting.Accounting.sessionFinish(Accounting.java:1786) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.splitSessionImpl(InetConnectionRuntime.java:1188) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.splitSession(InetConnectionRuntime.java:1095) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.trySplitSession(InetConnectionRuntime.java:994) at ru.bitel.bgbilling.modules.inet.accounting.FlowAgentInterface.processFlow(FlowAgentInterface.java:212) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.processPacketImpl0(FlowListenerWorker.java:323) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.processPacketImpl(FlowListenerWorker.java:178) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorkerNetflow.processPacket(FlowListenerWorkerNetflow.java:29) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.runImpl(FlowListenerWorker.java:60) at ru.bitel.common.worker.WorkerTask.run(WorkerTask.java:86) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:748) at ru.bitel.common.worker.WorkerThread.run(WorkerThread.java:46) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.Util.getInstance(Util.java:387) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009) at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5094) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.merge(SessionFlushingManager.java:929) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:385) ... 14 more collector 11-28/00:00:10 ERROR [flow-p-7-t-3] FlowAgentInterface - com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction ru.bitel.bgbilling.common.BGException: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:460) at ru.bitel.bgbilling.modules.inet.accounting.Accounting.sessionFinish(Accounting.java:1786) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.splitSessionImpl(InetConnectionRuntime.java:1188) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.splitSession(InetConnectionRuntime.java:1095) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.trySplitSession(InetConnectionRuntime.java:994) at ru.bitel.bgbilling.modules.inet.accounting.FlowAgentInterface.processFlow(FlowAgentInterface.java:212) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.processPacketImpl0(FlowListenerWorker.java:287) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.processPacketImpl(FlowListenerWorker.java:178) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorkerNetflow.processPacket(FlowListenerWorkerNetflow.java:29) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.runImpl(FlowListenerWorker.java:60) at ru.bitel.common.worker.WorkerTask.run(WorkerTask.java:86) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:748) at ru.bitel.common.worker.WorkerThread.run(WorkerThread.java:46) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.Util.getInstance(Util.java:387) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009) at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5094) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.merge(SessionFlushingManager.java:926) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:385) ... 14 more collector 11-28/00:00:10 ERROR [flow-p-7-t-4] FlowAgentInterface - com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction ru.bitel.bgbilling.common.BGException: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:460) at ru.bitel.bgbilling.modules.inet.accounting.Accounting.sessionFinish(Accounting.java:1786) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.splitSessionImpl(InetConnectionRuntime.java:1188) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.splitSession(InetConnectionRuntime.java:1095) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.trySplitSession(InetConnectionRuntime.java:994) at ru.bitel.bgbilling.modules.inet.accounting.FlowAgentInterface.processFlow(FlowAgentInterface.java:212) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.processPacketImpl0(FlowListenerWorker.java:287) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.processPacketImpl(FlowListenerWorker.java:178) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorkerNetflow.processPacket(FlowListenerWorkerNetflow.java:29) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.runImpl(FlowListenerWorker.java:60) at ru.bitel.common.worker.WorkerTask.run(WorkerTask.java:86) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:748) at ru.bitel.common.worker.WorkerThread.run(WorkerThread.java:46) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.Util.getInstance(Util.java:387) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009) at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5094) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.merge(SessionFlushingManager.java:926) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:385) ... 14 more collector 11-28/00:00:14 ERROR [flow-p-7-t-1] FlowAgentInterface - com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction ru.bitel.bgbilling.common.BGException: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:460) at ru.bitel.bgbilling.modules.inet.accounting.Accounting.sessionFinish(Accounting.java:1786) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.splitSessionImpl(InetConnectionRuntime.java:1188) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.splitSession(InetConnectionRuntime.java:1095) at ru.bitel.bgbilling.modules.inet.accounting.InetConnectionRuntime.trySplitSession(InetConnectionRuntime.java:994) at ru.bitel.bgbilling.modules.inet.accounting.FlowAgentInterface.processFlow(FlowAgentInterface.java:212) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.processPacketImpl0(FlowListenerWorker.java:323) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.processPacketImpl(FlowListenerWorker.java:178) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorkerNetflow.processPacket(FlowListenerWorkerNetflow.java:29) at ru.bitel.bgbilling.modules.inet.collector.FlowListenerWorker.runImpl(FlowListenerWorker.java:60) at ru.bitel.common.worker.WorkerTask.run(WorkerTask.java:86) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:748) at ru.bitel.common.worker.WorkerThread.run(WorkerThread.java:46) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.Util.getInstance(Util.java:387) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009) at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5094) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.merge(SessionFlushingManager.java:926) at ru.bitel.bgbilling.modules.inet.accounting.SessionFlushingManager.flush(SessionFlushingManager.java:385) ... 14 more /var/log/mysql/error.log:
|
|||
| Автор: | Amir [ 28 ноя 2017, 17:03 ] |
| Заголовок сообщения: | Re: deadlocks.... |
Попробуем сделать, чтобы удаление из inet_session_detail и account происходило отдельным потоком в отдельных транзакциях. |
|
| Автор: | Amir [ 28 ноя 2017, 17:04 ] |
| Заголовок сообщения: | Re: deadlocks.... |
Что указано в параметрах innodb_flush_log_at_trx_commit innodb_thread_concurrency ? |
|
| Автор: | ok-2004 [ 28 ноя 2017, 17:15 ] |
| Заголовок сообщения: | Re: deadlocks.... |
innodb_flush_log_at_trx_commit = 1 innodb_thread_concurrency = 0 |
|
| Автор: | Amir [ 28 ноя 2017, 17:24 ] |
| Заголовок сообщения: | Re: deadlocks.... |
Какая версия MySQL? |
|
| Автор: | ok-2004 [ 28 ноя 2017, 17:32 ] |
| Заголовок сообщения: | Re: deadlocks.... |
Код: version 5.7.18-15 version_comment Percona Server (GPL), Release '15', Revision 'bff2cd9' version_compile_machine x86_64 BGBilling 6.2 32 Gb RAM |
|
| Автор: | Amir [ 28 ноя 2017, 21:13 ] |
| Заголовок сообщения: | Re: deadlocks.... |
Цитата: innodb_flush_log_at_trx_commit = 1 Рекомендую попробовать установить 2 - это снижает кол-во вызовов sync диска с одного раза на каждый commit до одного раза в секунду,зависимость от дисковой операции снижается (но при поломке железа (именно железа, а не программной) или отключения от сетиможно потерять последние 1-2 секунды данных). На некоторых дисках/системах sync выполняется довольно медленно. https://dev.mysql.com/doc/refman/5.7/en ... trx_commit Цитата: innodb_thread_concurrency = 0 В текущей версии MySQL это означает, что он не ограничен в параллельных потоках выполнения InnoDB, но наверно это не лучший вариант (особенно, если они в итоге все упираются в работу с диском).https://dev.mysql.com/doc/refman/5.7/en ... oncurrency Цитата: If the number of concurrent user threads for a workload is less than 64, set innodb_thread_concurrency=0. В документации указано, что его можно менять на работающем MySQL. Рекомендую попробовать значения 32, 16, 8, 4.
If your workload is consistently heavy or occasionally spikes, start by setting innodb_thread_concurrency=128 and then lowering the value to 96, 80, 64, and so on, until you find the number of threads that provides the best performance. For example, suppose your system typically has 40 to 50 users, but periodically the number increases to 60, 70, or even 200. You find that performance is stable at 80 concurrent users but starts to show a regression above this number. In this case, you would set innodb_thread_concurrency=80 to avoid impacting performance. If you do not want InnoDB to use more than a certain number of vCPUs for user threads (20 vCPUs, for example), set innodb_thread_concurrency to this number (or possibly lower, depending on performance results). If your goal is to isolate MySQL from other applications, you may consider binding the mysqld process exclusively to the vCPUs. Be aware, however, that exclusive binding could result in non-optimal hardware usage if the mysqld process is not consistently busy. In this case, you might bind the mysqld process to the vCPUs but also allow other applications to use some or all of the vCPUs. innodb_thread_concurrency values that are too high can cause performance regression due to increased contention on system internals and resources. In some cases, the optimal innodb_thread_concurrency setting can be smaller than the number of vCPUs. |
|
| Автор: | Amir [ 04 дек 2017, 21:38 ] |
| Заголовок сообщения: | Re: deadlocks.... |
Выложили обновление, в котором удаление из inet_session_detail и inet_session_account происходит отдельно и после текущей транзакции. Теоретически должно уменьшить кол-во возможных дедлоков. |
|
| Страница 1 из 1 | Часовой пояс: UTC + 5 часов [ Летнее время ] |
| Powered by phpBB® Forum Software © phpBB Group http://www.phpbb.com/ |
|
