Skip navigation links

MySQL Forums :: InnoDB :: Consistent Nonlocking Read with REPEATABLE READ and READ COMMTTED


Advanced Search

Consistent Nonlocking Read with REPEATABLE READ and READ COMMTTED
Posted by: emil marceta ()
Date: September 17, 2009 12:18AM

Two scenarios are presented, same SQL using two isolation levels: REPEATABLE READ (default) and the READ COMMITTED. I was expecting that both scenarios will result in Innodb consistent read and no locks. The REPEATABLE READ scenario locked, while READ COMMITTED used consistent no-lock read.

The version is 5.1.37 and the default configuration is used.

---
Scenario 1:

select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)



Session 1:


CREATE TABLE `test_tbl` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

START TRANSACTION;
INSERT into test_tbl values (NULL, 'Test Record 1');

--
-- pause here, switch to the Session 2
--

CREATE TEMPORARY TABLE `mem_tbl` ( `id` int(11) NOT NULL AUTO_INCREMENT, `test_tbl_id` int(11) NOT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE=MEMORY DEFAULT CHARSET=utf8;

INSERT INTO mem_tbl SELECT NULL, id from (select * from test_tbl) temp_populate;

-- blocks Why this blocks? Why it is not using a consistent read?
---
-- switch to the session 1, and do a COMMIT. Unblocks.
--

Scenario 2 (transaction-isolation=READ-COMMITTED in my.cnf):

select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+

Execute the scenario 1 and it never blocks. The Session 2 will read 0 records, since the Session 1 has not COMMITTed yet. So here the consistent read works.

Why is REPEATABLE READ different?

Thanks,
emil

Options: ReplyQuote


Subject Views Written By Posted
Consistent Nonlocking Read with REPEATABLE READ and READ COMMTTED 4867 emil marceta 09/17/2009 12:18AM
Re: Consistent Nonlocking Read with REPEATABLE READ and READ COMMTTED 8385 Moiz Ahmed 10/19/2009 11:30PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.