CODEWITHSUNDEEP
mysqllocking
AarioAi
AarioAi

Reputation: 635

Why Intention Exclusive Locks are compatible with Intension Exclusive Locks in MySQL?

enter image description here

According to the reference SELECT ... LOCK IN SHARE MODE sets an IS lock and SELECT ... FOR UPDATE sets an IX lock. in MySQL 5.7.

I'm really confused about IX is compatible with IX. What is it supporsed to mean?

On the other hand, I tried below:

Sess1 db> BEGIN;
          SELECT * FROM t WHERE id = 1 FOR UPDATE;   -- id is a primary key
Sess2 db> BEGIN;
          SELECT * FROM t WHERE id = 1 FOR UPDATE;  -- Oops, it's blocking!

Sess1 db> BEGIN;
          SELECT * FROM t FOR UPDATE;    
Sess2 db> BEGIN;
          SELECT * FROM t WHERE id = 1 FOR UPDATE;  -- Oops, blocking too!

As Uppon, the IX is blocking other's IX on the same index if I don't misunderstand SELECT .. FOR UPDATE; is an IX lock.

So, how to understand IX is compatible with IX in MySQL?

Or how to understand SELECT ... LOCK IN SHARE MODE sets an IS lock and SELECT ... FOR UPDATE sets an IX lock. in MySQL 5.7 reference (https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html)

Upvotes: 3

Views: 1103

Answers (2)

Keiser He
Keiser He

Reputation: 11

  1. The lock compatibility table on the picture is for the same level locks.(ex. a table level IX lock is compatible with another table level IX lock)
  2. To acquire a low level lock(ex. row level X/S lock), you need to acquire higher level intentional locks(ex. table IX lock)

A table level IX lock is compatible with another table level IX lock, but not compatible with table level X/S lock.(you can acquire table S/X lock by sql "lock tables some_table read/write")

When requesting a row level X/S lock(ex. select ... for update), what it actually does is
request order for writing a record:
---> IX(Table);X(record)
release in opposite order.

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562791

In the example you show, it's the X locks that are conflicting.

Update: I was reminded by a MySQL engineer that IS/IX locks are implemented in the storage engine layer. MySQL has a wacky architecture that implements some semantics in a storage-independent way, but delegates some other operations down into the storage engine. And the storage engine may do things on its own too, which have no analog at the SQL layer.

IS/IX locks are implemented only in the InnoDB storage engine. So they are a special beast, and I shouldn't have compared them to table-level locks acquired with LOCK TABLES. It's not clear how they interact, if at all.

My original answer follows, for better or for worse...

The IX lock is for all practical purposes a table-level lock. It's okay to allow multiple sessions to acquire IX locks concurrently, because they might not conflict at the row level. If they do, their respective X locks will resolve that conflict. But that won't be a problem if they actually update different rows.

But an IX lock conflicts with other real table locks like those created by LOCK TABLES or ALTER TABLE or DROP TABLE. You don't want anyone doing row-level changes during an ALTER TABLE for example, and vice-versa: ALTER TABLE must wait for all transactions that hold IX locks to finish.

Re your comment:

Try this:

session1> LOCK TABLES MyTable READ;

session2> SELECT * FROM MyTABLE;                    /* no problem */
session2> SELECT * FROM MyTABLE LOCK IN SHARE MODE; /* no problem */
session2> SELECT * FROM MyTABLE FOR UPDATE;         /* waits for lock */

Compare with:

session1> LOCK TABLES MyTable WRITE;

session2> SELECT * FROM MyTABLE;                    /* no problem */
session2> SELECT * FROM MyTABLE LOCK IN SHARE MODE; /* waits for lock */
session2> SELECT * FROM MyTABLE FOR UPDATE;         /* waits for lock */

Therefore:

  • A table IS lock requested by LOCK IN SHARE MODE conflicts with a table write lock, but a table read lock will share. This is consistent with https://dev.mysql.com/doc/refman/5.7/en/lock-tables.html which says, "Multiple sessions can acquire a READ lock for the table at the same time."
  • A table IX lock requested by FOR UPDATE conflicts with both a table write lock and a table read lock.

Upvotes: 2

Related Questions