New Topic

Change Isolation Mode on MySQL Servers?

Whit shared this question 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/