How can a row be read when the table is read/write locked?

Question

I am running these queries on MySQL 5.6.13. I using repeatable read isolation level. The table looks like below:

enter image description here

In Session A terminal I have issued below statement

UPDATE manufacurer
SET lead_time = 2
WHERE mname = 'Hayleys';

In Session B terminal I tried to update the value lead_time of ACL Cables to 2. But since the previous UPDATE command from Session A is not yet committed (and Session A has an exclusive lock on manufacturer table), this update waits. This I can understand.

But when I try to execute a SELECT statement on Session B as below,

SELECT * FROM manufacturer
WHERE mcode = 'ACL';

it correctly query the manufacturer table and give out the row. How can this happen? Because Session A still hold the exclusive lock on manufacturer table and as I understand when an exclusive lock is held on a table no other transactions can read from or write to it till the previous transaction is committed.

In the title of your question you distinguish between read and write lock while in the question you talk about a lock that prevents read **and** write. Please update your question / title for consistency. — Max Leske, Nov 12 '13 at 10:05
Since you are not modifiying the row in the Session B. Data is returned. — Mad Dog Tannen, Nov 12 '13 at 10:17
@KayNelson Umm..If I understand correct eventhough I am not modifying the row, I am still trying to read from it and exclusive locks does not let other sessions to **read or write** untill the previous transaction is committed. — Les_Salantes, Nov 12 '13 at 10:23
Do you wan't to implement that behaviour or wondering why it is already happening? Either way, refer [MySQL locking](http://dev.mysql.com/doc/refman/5.0/en/internal-locking.html) docs. — alko, Nov 12 '13 at 10:26
read docs; I updated question to reflect your comments. please see if you agree with updates — alko, Nov 12 '13 at 10:35
@Thili InnoDB provides row level locking. Your `UPDATE` only touches the row `WHERE mname = 'Hayleys'`. Your SELECT is for data in a different row `WHERE mcode = 'ACL'`. So that row is safe to read - though this also depends on which indexes you have on your table - more rows than exactly the rows that are updated might be locked. — nos, Nov 12 '13 at 13:33

Fengya Li · Answer 1 · 2013-11-12T10:31:44.353

2

There are kinds of lock in mysql: row-level lock and table-level lock.

What you need is row-level lock,which allows read the lines beyond the ones updating.

And to implement the row-level lock,you have to define the engine type of your table to 'InnoDB':

alter table TABLE_NAME engine=innodb;

edited Nov 12 '13 at 10:31

answered Nov 12 '13 at 10:22

Fengya Li

4,459
1
14
12

1

Its called ROW level locking i think. – Mad Dog Tannen Nov 12 '13 at 10:26

score 2 · Accepted Answer · answered Nov 12 '13 at 10:48

Found below information on this page

http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html#isolevel_repeatable-read

Scope of Transaction Characteristics

You can set transaction characteristics globally, for the current session, or for the next transaction:

With the GLOBAL keyword, the statement applies globally for all subsequent sessions. Existing sessions are unaffected.

With the SESSION keyword, the statement applies to all subsequent transactions performed within the current session.

Without any SESSION or GLOBAL keyword, the statement applies to the next (not started) transaction performed within the current session.

Have this been taken into consideration?

REPEATABLE READ

This is the default isolation level for InnoDB. For consistent reads, there is an important difference from the READ COMMITTED isolation level: All consistent reads within the same transaction read the snapshot established by the first read. This convention means that if you issue several plain (nonlocking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other.

In this article its decribes very well.

http://www.mysqlperformanceblog.com/2012/08/28/differences-between-read-committed-and-repeatable-read-transaction-isolation-levels/

It is important to remember that InnoDB actually locks index entries, not rows. During the execution of a statement InnoDB must lock every entry in the index that it traverses to find the rows it is modifying. It must do this to prevent deadlocks and maintain the isolation level.

Are the tables well indexed? Can you run a SHOW ENGINE innodb STATUS to confirm that the lock is held?

How can a row be read when the table is read/write locked?

2 Answers2