Hi, Said! On Mar 11, Said Ramirez wrote:
Sergei, The longest transaction I currently see is ~ 3.5 hours. Which is a long time, and I can look into to that. I haven't seen transactions last days yet. Would having a transaction last hours not cause the old rows from being purged once those transactions have closed? If it were an old or stale transaction keeping the old row versions from being purged, a server restart would fix the issue, no ?
Yes, a long lasting transaction would prevent old row version from being purged even after those transactions have ended. Because a default transaction isolation level is consistent read, which means, that if you've seen some version of a row in a transaction, you should always continue seeing this exactly version of this row until your transaction ends. And InnoDB will have to keep this version and it cannot purge it. Yes, a server restart would fix it.
We also do see a fair amount of deadlocks. Could a thread that is causing the deadlock cause old row versions from being purged even after the this thread is closed?
No. Regards, Sergei