1

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.

1

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.

  • I understand, but still my question is more of an "educational" question, There's obviously a performance impact between each isolation level, And thats the question I am asking, I want to create a simulation where I can see that performance impact – omri Mar 21 '16 at 9:00
  • A simulation will be tricky. First, look at touching thousands of rows -- a single row will get in and out too fast to even show any interaction between threads. Also, see if performance_schema has any low level metrics that are useful. – Rick James Mar 21 '16 at 15:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.