0

This is a Magento application connected to MySQL Db. When I ran show engine innodb status\GI get a list of TRANSACTIONSand it looks like some of them are pending and others are in a DEADLOCKand it doesn't matter if I kill them, they keep coming back. What can I do to prevent that? Help! Here's the log:

---TRANSACTION 284071526856416, not started estimating records in index range
mysql tables in use 10, locked 0
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 284071526852056, not started estimating records in index range
mysql tables in use 10, locked 0
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 284071526902632, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 284071526853800, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 284071526845080, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 39922654, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
1 lock struct(s), heap size 1136, 0 row lock(s)
MySQL thread id 1654, OS thread handle 6724, query id 46131 localhost ::1 root updating
UPDATE `log_visitor` SET `session_id` = 'imtbh6fl9rldnoi44vjnmepf70', `first_visit_at` = '2020-01-14 19:49:19', `last_visit_at` = '2020-01-14 19:49:27', `last_url_id` = '0', `store_id` = '1' WHERE (visitor_id='1763150')
---TRANSACTION 39922653, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 1669, OS thread handle 5184, query id 46128 localhost ::1 root updating
UPDATE `log_visitor` SET `session_id` = 'fvobv8l301a79ece0006vot6a5', `first_visit_at` = '2020-01-14 19:49:23', `last_visit_at` = '2020-01-14 19:49:27', `last_url_id` = '0', `store_id` = '1' WHERE (visitor_id='1763153')
---TRANSACTION 39922646, ACTIVE 2 sec updating or deleting
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 1623, OS thread handle 5048, query id 46079 localhost ::1 root updating
UPDATE `core_session` SET `session_expires` = '1579034965', `session_data` = '_secure_cookie_check|s:32:\"919e25a2e43969e5d6bfbede7bb4e55d\";core|a:5:{s:23:\"_session_validator_data\";a:4:{s:11:\"remote_addr\";s:14:\"159.138.159.72\";s:8:\"http_via\";s:0:\"\";s:20:\"http_x_forwarded_for\";s:0:\"\";s:15:\"http_user_agent\";s:166:\"Mozilla/5.0(Linux;Android 5.1.1;OPPO A33 Build/LMY47V;wv) AppleWebKit/537.36(KHTML,link Gecko) Version/4.0 Chrome/42.0.2311.138 Mobile Safari/537.36 Mb2345Browser/9.0\";}s:12:\"visitor_data\";a:15:{s:0:\"\";N;s:11:\"server_addr\";s:4:\"\n\0\";s:11:\"remote_addr\";s:
---TRANSACTION 39922645, ACTIVE 2 sec updating or deleting
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 1624, OS thread handle 6820, query id 46078 localhost ::1 root updating
UPDATE `core_session` SET `session_expires` = '1579034965', `session_data` = '_secure_cookie_check|s:32:\"d1e4c0600c606a203e398f2844d33ada\";core|a:5:{s:23:\"_session_validator_data\";a:4:{s:11:\"remote_addr\";s:15:\"159.138.153.198\";s:8:\"http_via\";s:0:\"\";s:20:\"http_x_forwarded_for\";s:0:\"\";s:15:\"http_user_agent\";s:166:\"Mozilla/5.0(Linux;Android 5.1.1;OPPO A33 Build/LMY47V;wv) AppleWebKit/537.36(KHTML,link Gecko) Version/4.0 Chrome/42.0.2311.138 Mobile Safari/537.36 Mb2345Browser/9.0\";}s:12:\"visitor_data\";a:15:{s:0:\"\";N;s:11:\"server_addr\";s:4:\"\n\0\";s:11:\"remote_addr\";s
Jacman
  • 101
  • 2

1 Answers1

0

If not impacting business, have you tried to kill the connection app-side?

We had an issue with locked/active sessions from an app to a MSSQL DB, however the only way we could bypass was to break the active connection from the app to the DB via failover. That was only because it shifted resources from node 1 to node 2. We attempted to kill the connection via MSSQL queries but couldn't get it to stop that way, so failover ended up being the solution for us.

Note: I am still new to DBA, but figured it's worth a try to share what I've experienced in my short time in this role.

vPilot
  • 47
  • 10