BiTel

Форум BiTel
bgbilling.ru     docs.bitel.ru     wiki.bitel.ru     dbinfo.bitel.ru     bgcrm.ru     billing.bitel.ru     bitel.ru    
Текущее время: 28 мар 2024, 23:36

Часовой пояс: UTC + 5 часов [ Летнее время ]




Начать новую тему Ответить на тему  [ Сообщений: 20 ] 
Автор Сообщение
 Заголовок сообщения: deadlocks....
СообщениеДобавлено: 26 авг 2017, 17:47 
Не в сети
Клиент

Зарегистрирован: 21 май 2008, 10:54
Сообщения: 599
Откуда: 50-й рег.
Карма: 40
Добрый день!
Каждое начало дня сопровождается вот таким выхлопом в 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

_________________
"Все правые - в резерве!" (c) (translate.google.ru/#en/ru/all%20rigths%20reserved)


Вернуться к началу
 Профиль  
 
 Заголовок сообщения: Re: deadlocks....
СообщениеДобавлено: 29 авг 2017, 21:48 
Не в сети
Разработчик
Аватара пользователя

Зарегистрирован: 19 дек 2006, 21:04
Сообщения: 5970
Карма: 256
А очень много таких ошибок? Вообще по документации MySQL это считается нормальным (т.к. кроме lock на записи, он еще ставит gap-lock'и при выборке записей), нужно попробовать снова, что и делается.
Другое дело, если это мешает работе или происходит слишком часто - тогда наверно надо пытаться оптимизировать.


Вернуться к началу
 Профиль  
 
 Заголовок сообщения: Re: deadlocks....
СообщениеДобавлено: 30 авг 2017, 16:48 
Не в сети
Клиент

Зарегистрирован: 21 май 2008, 10:54
Сообщения: 599
Откуда: 50-й рег.
Карма: 40
Ошибки тока на границе суток....

_________________
"Все правые - в резерве!" (c) (translate.google.ru/#en/ru/all%20rigths%20reserved)


Вернуться к началу
 Профиль  
 
 Заголовок сообщения: Re: deadlocks....
СообщениеДобавлено: 30 авг 2017, 19:20 
Не в сети
Разработчик
Аватара пользователя

Зарегистрирован: 19 дек 2006, 21:04
Сообщения: 5970
Карма: 256
Ну это понятно, там начинает сплитить сессии -> работа с базой.
Много-мало, т.е. 3, 10, 100, все логи завалены этими ошибками?


Вернуться к началу
 Профиль  
 
 Заголовок сообщения: Re: deadlocks....
СообщениеДобавлено: 17 ноя 2017, 10:08 
Не в сети
Клиент

Зарегистрирован: 21 май 2008, 10:54
Сообщения: 599
Откуда: 50-й рег.
Карма: 40
Извиняюсь за некро-UP.

Цитата:
Ну это понятно, там начинает сплитить сессии -> работа с базой.


Да, похоже на то.

Вот типичный err-лог mysql на границе суток, когда на запись в его таблицы конкурируют 3 независимых BGInetAccess/BGInetAccounting сервера.
( сиё есть лишь фрагмент токмо ради наглядности ибо сессий сплитится много и откатов тракзакций весьма не мало )

Вложение:
err.log [33.17 КБ]
Скачиваний: 380

_________________
"Все правые - в резерве!" (c) (translate.google.ru/#en/ru/all%20rigths%20reserved)


Вернуться к началу
 Профиль  
 
 Заголовок сообщения: Re: deadlocks....
СообщениеДобавлено: 18 ноя 2017, 11:30 
Не в сети
Клиент

Зарегистрирован: 21 май 2008, 10:54
Сообщения: 599
Откуда: 50-й рег.
Карма: 40
Итак. очередной посуточный репорт.
Вкратце на помню . что мы наблюдаем за войной 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)


Да. масштабтируемость как-то хромает.... :facepalm:

Посоветуйте - как мне поступить ? :

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 .

До завтра...

_________________
"Все правые - в резерве!" (c) (translate.google.ru/#en/ru/all%20rigths%20reserved)


Вернуться к началу
 Профиль  
 
 Заголовок сообщения: Re: deadlocks....
СообщениеДобавлено: 19 ноя 2017, 15:33 
Не в сети
Клиент

Зарегистрирован: 21 май 2008, 10:54
Сообщения: 599
Откуда: 50-й рег.
Карма: 40
Приятного всем воскресения!
А мы продолжаем ....

Сдвинул время на 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)


Можно конечно и ему сдвинуть время на большую дельту. но как-то не правильно всё это...

Продолжаю наблюдения. :lupa: До понедельника...

_________________
"Все правые - в резерве!" (c) (translate.google.ru/#en/ru/all%20rigths%20reserved)


Вернуться к началу
 Профиль  
 
 Заголовок сообщения: Re: deadlocks....
СообщениеДобавлено: 20 ноя 2017, 11:39 
Не в сети
Клиент

Зарегистрирован: 21 май 2008, 10:54
Сообщения: 599
Откуда: 50-й рег.
Карма: 40
Доброго всем понедельника.
Сегодняшняя ночь по народным поверьям - ночь деадлоков . :facepalm:

свежие утренние логи :
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 :


Вложения:
err.log [69.39 КБ]
Скачиваний: 596

_________________
"Все правые - в резерве!" (c) (translate.google.ru/#en/ru/all%20rigths%20reserved)
Вернуться к началу
 Профиль  
 
 Заголовок сообщения: Re: deadlocks....
СообщениеДобавлено: 20 ноя 2017, 16:38 
Не в сети
Разработчик
Аватара пользователя

Зарегистрирован: 19 дек 2006, 21:04
Сообщения: 5970
Карма: 256
И логи InnoDB как в предыдущем сообщении в личку еще можно?


Вернуться к началу
 Профиль  
 
 Заголовок сообщения: Re: deadlocks....
СообщениеДобавлено: 20 ноя 2017, 16:44 
Не в сети
Клиент

Зарегистрирован: 21 май 2008, 10:54
Сообщения: 599
Откуда: 50-й рег.
Карма: 40
Цитата:
И логи InnoDB как в предыдущем сообщении в личку еще можно?


what ? :?

_________________
"Все правые - в резерве!" (c) (translate.google.ru/#en/ru/all%20rigths%20reserved)


Вернуться к началу
 Профиль  
 
 Заголовок сообщения: Re: deadlocks....
СообщениеДобавлено: 24 ноя 2017, 10:48 
Не в сети
Клиент

Зарегистрирован: 21 май 2008, 10:54
Сообщения: 599
Откуда: 50-й рег.
Карма: 40
Доброе У !
Прошу прощения за 2-х дневное отсутствие в треде :lol: ( ничего нового в деадлоках не случилось....)

А вот вчера вечером произошла любопытная история, примечательная тем , что случилась она не на границе суток
и спор между собой вели 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

_________________
"Все правые - в резерве!" (c) (translate.google.ru/#en/ru/all%20rigths%20reserved)


Вернуться к началу
 Профиль  
 
 Заголовок сообщения: Re: deadlocks....
СообщениеДобавлено: 27 ноя 2017, 10:45 
Не в сети
Клиент

Зарегистрирован: 21 май 2008, 10:54
Сообщения: 599
Откуда: 50-й рег.
Карма: 40
Алекс - Юстасу.

Воскресный отчёт по деадлокам на 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: ( во вложении по тому как большой)...

Продолжаю наблюдения. :lupa:


Вложения:
error.log [28.56 КБ]
Скачиваний: 550

_________________
"Все правые - в резерве!" (c) (translate.google.ru/#en/ru/all%20rigths%20reserved)
Вернуться к началу
 Профиль  
 
 Заголовок сообщения: Re: deadlocks....
СообщениеДобавлено: 28 ноя 2017, 10:14 
Не в сети
Клиент

Зарегистрирован: 21 май 2008, 10:54
Сообщения: 599
Откуда: 50-й рег.
Карма: 40
Очередные ночные логи :

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:


Вложения:
err.log [21.32 КБ]
Скачиваний: 582

_________________
"Все правые - в резерве!" (c) (translate.google.ru/#en/ru/all%20rigths%20reserved)
Вернуться к началу
 Профиль  
 
 Заголовок сообщения: Re: deadlocks....
СообщениеДобавлено: 28 ноя 2017, 17:03 
Не в сети
Разработчик
Аватара пользователя

Зарегистрирован: 19 дек 2006, 21:04
Сообщения: 5970
Карма: 256
Попробуем сделать, чтобы удаление из inet_session_detail и account происходило отдельным потоком в отдельных транзакциях.


Вернуться к началу
 Профиль  
 
 Заголовок сообщения: Re: deadlocks....
СообщениеДобавлено: 28 ноя 2017, 17:04 
Не в сети
Разработчик
Аватара пользователя

Зарегистрирован: 19 дек 2006, 21:04
Сообщения: 5970
Карма: 256
Что указано в параметрах
innodb_flush_log_at_trx_commit
innodb_thread_concurrency
?


Вернуться к началу
 Профиль  
 
 Заголовок сообщения: Re: deadlocks....
СообщениеДобавлено: 28 ноя 2017, 17:15 
Не в сети
Клиент

Зарегистрирован: 21 май 2008, 10:54
Сообщения: 599
Откуда: 50-й рег.
Карма: 40
innodb_flush_log_at_trx_commit = 1
innodb_thread_concurrency = 0

_________________
"Все правые - в резерве!" (c) (translate.google.ru/#en/ru/all%20rigths%20reserved)


Вернуться к началу
 Профиль  
 
 Заголовок сообщения: Re: deadlocks....
СообщениеДобавлено: 28 ноя 2017, 17:24 
Не в сети
Разработчик
Аватара пользователя

Зарегистрирован: 19 дек 2006, 21:04
Сообщения: 5970
Карма: 256
Какая версия MySQL?


Вернуться к началу
 Профиль  
 
 Заголовок сообщения: Re: deadlocks....
СообщениеДобавлено: 28 ноя 2017, 17:32 
Не в сети
Клиент

Зарегистрирован: 21 май 2008, 10:54
Сообщения: 599
Откуда: 50-й рег.
Карма: 40
Код:
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

_________________
"Все правые - в резерве!" (c) (translate.google.ru/#en/ru/all%20rigths%20reserved)


Вернуться к началу
 Профиль  
 
 Заголовок сообщения: Re: deadlocks....
СообщениеДобавлено: 28 ноя 2017, 21:13 
Не в сети
Разработчик
Аватара пользователя

Зарегистрирован: 19 дек 2006, 21:04
Сообщения: 5970
Карма: 256
Цитата:
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.

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.
В документации указано, что его можно менять на работающем MySQL. Рекомендую попробовать значения 32, 16, 8, 4.


Вернуться к началу
 Профиль  
 
 Заголовок сообщения: Re: deadlocks....
СообщениеДобавлено: 04 дек 2017, 21:38 
Не в сети
Разработчик
Аватара пользователя

Зарегистрирован: 19 дек 2006, 21:04
Сообщения: 5970
Карма: 256
Выложили обновление, в котором удаление из inet_session_detail и inet_session_account происходит отдельно и после текущей транзакции. Теоретически должно уменьшить кол-во возможных дедлоков.


Вернуться к началу
 Профиль  
 
Показать сообщения за:  Поле сортировки  
Начать новую тему Ответить на тему  [ Сообщений: 20 ] 

Часовой пояс: UTC + 5 часов [ Летнее время ]


Кто сейчас на конференции

Сейчас этот форум просматривают: нет зарегистрированных пользователей и гости: 1


Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете добавлять вложения

Найти:
Перейти:  
cron
POWERED_BY
Русская поддержка phpBB
[ Time : 0.160s | 60 Queries | GZIP : On ]