0

Environment : Mysql5.7 InnoDB

A Connection

start transaction; --> <1>

SELECT * FROM table_a WHERE id = 1 lock in share mode; --> <2>

B Connection

UPDATE table_a SET name = 'blah' WHERE id = 1; --> <3>

Function Flow : <1> -> <2> -> <3>

Conclusion : <2> query result is blahblah. But, B Query(<3>) waits.

B Query is no lock.

Why do I get this result? Does Mysql use exclusive locks by default when doing UPDATE queries?

henry-jo
  • 283
  • 1
  • 3
  • 14

1 Answers1

1

UPDATE queries needs to hold an exclusive lock on the rows it examines, so it will wait until there is no other lock on those rows.

SELECT queries do not block UPDATE queries, unless the SELECT is a locking query with the FOR UPDATE, FOR SHARE, or LOCK IN SHARE MODE clauses.

If you use the transaction isolation level SERIALIZABLE, then all SELECT statements implicitly have LOCK IN SHARE MODE, so they will block UPDATEs. But this is not the default configuration, so you would have to request it deliberately.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • I made a mistake. A query is in Shared Mode. I understand that Exclusive Lock does not have access when in Shared Lock Mode. But, Why B Query(simple update query) waiting when using Shared Lock? – henry-jo Dec 10 '19 at 06:44
  • 1
    Because an exclusive lock means that no other lock may exist. The UPDATE must wait until it is the only one that holds a lock of any type on the rows it examines. – Bill Karwin Dec 10 '19 at 07:27
  • I'm sorry, Does your answer mean UPDATE = Exclusive Lock? – henry-jo Dec 10 '19 at 07:36
  • 1
    I see why my first sentence of my answer was ambiguous. I've edited it, hopefully to be more clear. – Bill Karwin Dec 10 '19 at 14:53
  • Hi @Bill Karwin, does DELETE also hold an exclusive lock? –  Sep 29 '22 at 14:42
  • 1
    @Daniel, read https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html – Bill Karwin Sep 29 '22 at 15:10
  • marked as useful answer, great @Bill Karwin, thanks a lot I'll check it out. –  Sep 29 '22 at 17:05