Change Isolation Mode on MySQL Servers?

Whit shared this question 2 years ago
Answered

I accidentally entered this an idea yesterday, when really I needed it as a question...doh! Feel free to delete that idea since it is not something YF really controls, more of a database setting.

I would like to lobby us to change the isolation status on the server where the yellowfin database resides from REPEATABLE READ to READ COMMITTED, more like the Oracle standard. The reason for this is all the lock wait timeouts we see from YF. You might recall in the past I have questioned all the lock wait timeouts we see, and in lieu of upping that timeout, I would like to see what the isolation change would do. READ COMMITTED releases the locked rows once the index doesn’t match the scan, where REPEATABLE READ holds onto all row locks, basically the whole table until the commit is made. I believe we can alleviate some of the log ERRORS by making this change.

Do you forsee any issues with this? Are there any transactions that could be impacted because results can be different for two reads in the same transaction when using READ COMMITTED?

Here are some links for info:

https://www.percona.com/blog/2012/08/28/differences-between-read-committed-and-repeatable-read-transaction-isolation-levels/

https://www.percona.com/blog/2015/01/14/mysql-performance-implications-of-innodb-isolation-modes/

Comments (3)

photo
1

Hi Whit,


there shouldn't be any issues with switching from REPEATABLE READ to READ COMMITTED, and should you find any then we will certainly look into them.


All the best with your lobbying!


regards,

Big Dave

photo
1

Thanks, Dave!  

On Sun, Apr 23, 2017 at 11:24 PM, Yellowfin Support wrote:

photo
1

Hi Whit,

please ignore this...I'm just adding a comment so that you're not the last commenter (if you're the last one then the ticket stays on my work list!)

regards,

David