mysql isolation level

https://www.facebook.com/note.php?note_id=244956410932
There is a lot of documentation available on transaction isolation levels for InnoDB includingthe InnoDB transaction model, a description of the isolation levels, notes on locks, locks set by statements and notes on consistent non-locking reads.

There is another issue that has not been documented. The code that creates a read view(snapshot) for InnoDB transactions is a source of mutex contention on kernel_mutex. This code is run once per statement for read-committed and once per transaction for repeatable-read.

The function read_view_open_now is run to create a snapshot. It copies the list of uncommitted transactions into a per-transaction data structure. Memory is allocated from the per-transaction heap to store the copied data and that allocation might require a call to malloc. All of this work is done while holding kernel_mutex and other threads are unlikely to get much work done in InnoDB when kernel_mutex is locked.

Subscribe to feature request 49169 if you are interested in this. I am not sure if there is an easy fix. Work can be done to reduce the chance of allocating memory while copying the open transaction list. But splitting kernel_mutex into several locks might be hard.

When repeatable-read is used there are fewer calls to read_view_open_now and there is less contention on kernel_mutex. I ran sysbench to measure the difference in performance.

http://dev.mysql.com/doc/refman/5.5/en/innodb-transaction-isolation-levels.html#isolevel_read-committed
https://easyengine.io/tutorials/mysql/enable-innodb-file-per-table/
https://www.brightbox.com/blog/2013/10/31/on-mysql-locks/
http://stackoverflow.com/questions/20321407/mysql-table-lock-instead-of-row-locks
https://www.percona.com/blog/2015/01/14/mysql-performance-implications-of-innodb-isolation-modes/
https://www.percona.com/blog/2012/08/28/differences-between-read-committed-and-repeatable-read-transaction-isolation-levels/

https://www.simple-talk.com/sql/t-sql-programming/questions-about-t-sql-transaction-isolation-levels-you-were-too-shy-to-ask/

https://www.percona.com/blog/2012/07/31/innodb-table-locks/


http://stackoverflow.com/questions/20173670/clarifying-the-difference-between-row-level-lock-in-innodb-engine-and-table-leve


change isolation level:-
SELECT @@GLOBAL.tx_isolation, @@tx_isolation; 

SET global TRANSACTION ISOLATION LEVEL READ COMMITTED; 
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET global TRANSACTION ISOLATION LEVEL REPEATABLE READ; 

SET GLOBAL tx_isolation='READ-COMMITTED';

SET SESSION tx_isolation='READ-COMMITTED';

]# mysql -e "show variables" | grep isolation

tx_isolation    READ-COMMITTED

## disable binlog  OR
SET GLOBAL binlog_format = 'ROW';  

error: rlm_sql_mysql: MySQL error 'Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT''

SET GLOBAL binlog_format = 'ROW';  


If you are not planning to use your MySQL server for the replication consider turning the binary logging off by removing the option --log-bin from the command options for the mysqld utility starting the MySQL server.