plinux, this patch looks interesting, but could you elaborate a few things:

• why wait_timeout didn't work for you?
• why do you need separate timeouts for read-only and read-write transactions? What is the common setting for those?
• why do you need additional trx_idle_timeout?
• why trx_idle_timeout has higher priority than more specific trx_readonly_idle_timeout and trx_changes_idle_timeout?
• per my understanding these 3 new idle timeouts have longer effect than one may expect, e.g. if transaction is committed but no subsequent statement received yet. Right?

svoj

Idle transaction means the transaction is started and done some operations, but forget to query a COMMIT or ROLLBACK.
Wait_timeout can't sovle this problem.
Because for InnoDB, readonly transaction needn't to get any locks (MVCC), so maybe users can accept longer run time.
But for INSERT/DELETE or other write operations, the transaction will hold some locks, so usually the users can't accept it runs a long time.
So we need 2 different timeout option.

And trx_idle_timeout is a higher priority option, if users can accept the readonly and non-readonly transactions use the same timeout number, then it can use this option.

plinux, alright I agree different timeouts for read-only and read-write transactions are useful. Per my understanding one of options is to adjust wait_timeout before every transaction, but it requires application changes and is not acceptable for you.

Still I don't completely understand a few implementation things:

• after I do just "BEGIN" wait_timeout applies, which is more or less alright. Correct?
• after I do "BEGIN; SET ..." wait_timeout applies, which is more or less alright. Correct?
• after I do "BEGIN; INSERT...; COMMIT;" trx_changes_idle_timeout applies (because nobody restored it to wait_timeout), which looks confusing. Correct?
• I'm fine about having trx_idle_timeout as a fallback for trx_readonly_idle_timeout and trx_changes_idle_timeout, but I think the fact that it has higher priority is confusing. Will it work for you if we prefer setting of trx_readonly_idle_timeout and trx_changes_idle_timeout over trx_idle_timeout?

New variables added:
idle_transaction_timeout
idle_readonly_transaction_timeout
idle_readwrite_transaction_timeout

Otherwise it is similar to original patch.