-- url of the page with the problem -- :
www.earmaster.com
-- HikaShop version -- : 6.6.0
-- Joomla version -- : 5.4.9 Stable
-- PHP version -- : 8.5.12
-- Browser(s) name and version -- : Chrome 156.0
-- Error-message(debug-mod must be tuned on) -- : Lock wait timeout exceeded; try restarting transaction
We regularly got this unhandled exception on ordinary front-end pages, often non-shop pages visited by new visitors:
Code:
Joomla\Database\Exception\ExecutionFailureException
Lock wait timeout exceeded; try restarting transaction
#2 plugins/system/hikashopgeolocation/hikashopgeolocation.php(327): JFalangDatabase->execute()
#3 plugins/system/hikashopgeolocation/hikashopgeolocation.php(185): plgSystemHikashopgeolocation->getZone()
#4 plugins/system/hikashopgeolocation/hikashopgeolocation.php(86): plgSystemHikashopgeolocation->process()
Each occurrence means a visitor waits about 50 seconds and then gets an error page.
Cause
Line 327 is the cleanup query at the end of getZone():
Code:
DELETE FROM #__hikashop_geolocation
WHERE geolocation_type = 'visit' AND geolocation_created < (now - 1 year)
This runs for every visitor without an existing session: every new visitor, and every bot or client that doesn't keep cookies. The table only has an index on (geolocation_type, geolocation_ref_id). So the DELETE has to scan and lock every "visit" row, not just the expired ones. On our site that's about 1.87 million rows. The IP lookup in the same function has no index on geolocation_ip either. When several new visitors arrive at the same time, these queries queue behind each other until one times out.
Workaround we applied
Adding two indexes solved it for us:
Code:
ALTER TABLE #__hikashop_geolocation
ADD INDEX idx_type_created (geolocation_type, geolocation_created),
ADD INDEX idx_ip (geolocation_ip(45)),
ALGORITHM=INPLACE, LOCK=NONE;
EXPLAIN confirms the DELETE now uses idx_type_created.
Suggestions
- Add these indexes (or equivalent ones) to the table definition and the update script, so they survive updates and other sites get them too.
- Run the purge occasionally, for example once a day or through a scheduled task, instead of on every new visitor's first page view.
- Rows with geolocation_type = 'bot' are never purged. We have about 245,000 of them after a year, and the number keeps growing.
- Possible typo: a few lines above the DELETE in getZone(), if(empty($zones)) looks like it should be if(!empty($zones)). In practice the fallback query below it usually finds the zone anyway, but it's worth checking.
