We have recently moved our Magento database from GCP cloudsql to Galera Cluster.Front end is working fine where in backend we are getting lock_wait timeout error for any change from backend. We noticed this happens after importing any huge product change fro sheet.
error:
SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction, query was: UPDATE catalog_product_entity SET attribute_set_id = ?, type_id = ?, sku = ?, has_options = ?, required_options = ?, created_at = ?, updated_at = ? WHERE (entity_id = '12132')
Has anybody comeacrsoss this kind of issue or any optimization recommendation ??
I have updated below parameters and the issue got resolved.
innodb_autoextend_increment=512
thread_concurrency = 45
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 47
innodb_lock_wait_timeout=1800