Mysql – Innodb Isolation levels and locking performance

innodbisolation-levellockingMySQL

I am reading a lot about locking and isolation levels in MySQL and InnoDB.

I understood the technical differences and basic approach, but I still fail to understand a situation where for example READ UNCOMMITTED will give me better performance than READ COMMITTED.

Here is my simulation:

CREATE TABLE `t` (
    `i1` INT(11) NOT NULL,
    `i2` INT(11) NULL DEFAULT NULL,
PRIMARY KEY (`i1`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
insert into t values(10,10), (20,20), (30,30), (40, 40), (50,50),(60,60);

T1:

MariaDB [maint]> begin;
Query OK, 0 rows affected (0.00 sec)
MariaDB [maint]> update t set i2=100 where i1=60;
Query OK, 1 row affected (0.00 sec)

T2 with READ UNCOMMITTED:

MariaDB [maint]> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
MariaDB [maint]> select i2 from t where i1 =60;
+------+
| i2   |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

So the logic as expected, and no locks.

T2 with READ COMMITTED:

MariaDB [maint]> SET TRANSACTION ISOLATION LEVEL READ COMMITTED; START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
MariaDB [maint]> select * from t where i1 =60;
+----+------+
| i1 | i2   |
+----+------+
| 60 |   60 |
+----+------+
1 row in set (0.00 sec)

The logic works as I expected, and again no lock.

So simply the only difference is that READ UNCOMMITTED just took the data before commit and READ COMMITTED took a snapshot.

Can someone help me create a simulation or explain a situation in which UNCOMMITTED will have no locks whereas COMMITTED would have? Same for REPEATABLE READ vs COMMITTED.

Best Answer

In my opinion... If you are tweaking isolation levels for performance then you have inefficient/inadequate code/schema/indexes and should focus on that instead.

Think about it... You can perform hundreds of 'simple' queries per second from dozens of connections. So, if your queries are 'simple' and you don't have too many of them, then they will rarely collide -- hence, the isolation mode won't matter!

select i2 from t where i1 =60; -- The obvious index is INDEX(i1). Better would be PRIMARY KEY(i1), but that may not be appropriate. If i1 were not the PK, then the best is a "covering" index: INDEX(i1,i2) -- the query is performed in the index, not touching the data.

You will be hard pressed to run enough of the queries you mentioned fast enough from enough connections to have them stumble over each other. In fact, you are more likely to be blocked by the "Query cache". For serious production work, that is usually turned completely off: query_cache_size = 0 and query_cache_type = 0.

Back to my first point... Changing the Isolation Level changes the results that you might get from queries! So, doing it for performance reasons could mess with the application output.

Related Question