Lock wait timeout exceeded behind proxysql

[Muhammad Panji]

Hello Folks,

I have a proxysql setup in GKE, The proxysql run behind a network load balancer.

The backend is CloudSQL with mysql 5.7.

I got "General error: 1205 Lock wait timeout exceeded; try restarting transaction" for some queries to several tables.

When this happen, no long running to the impacted table. I suspect there the previous query which still hold the lock is part of a transaction but somehow the transaction is never finished, thus the lock is not released yet.

Even if we manually run the query after 1 hours, the lock is still there.

I found this presentation (https://www.slideshare.net/slideshow/proxysql-and-the-tricks-up-its-sleeve-percona-live-2022pdf/251937661) on page 41 mention

- mysql-max_transaction_time
- mysql-max_transaction_idle_time

The default value for both is 4 hours. Some developers mention they can manually run the query after 4-5 hours.

My questions are:

1. I plan to reduce both params above so the lock will be released earlier since the transaction is cancelled. The number of lock wait timeout will be reduced.

2. If updating the config above can solve the lock wait timeout issue. What is the best practice / rule of thumb on lowering the value? Are there any settings on MySQL side that I also need to adjust?

Thank you.

Regards,

Panji

[Satria Dwi Putra]

Hi Panji,

I suggest producing more detail regarding query transactions which are not finished, you can use tools like percona deadlock logger. Mostly locking issues need to be fixed on the application side. If you want to force on the database side it's better using an additional script to check slow query and check which query locks the other query and then kill the query, but you need to deal first with the application team to kill that query. 

for example if I want simplified this process like this : 

1. Script running periodically 
2. Query checking running 
3. If found slow or locking query notify to communication channel, send query details like PID , user, duration running etc..
4. If not found script exit 
5. Kill the query based on PID , if all stakeholders agree.

oh yeah regarding two parameters mysql-max_transaction_time and mysql-max_transaction_idle_time in my experience I just run with default value and never adjust in ProxySQL side. 

Hopefully it can help.

Regards.

Satria

--
You received this message because you are subscribed to the Google Groups "proxysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/proxysql/5ebf205e-2419-4995-8086-66c1e4c91230n%40googlegroups.com.