CODEWITHSUNDEEP
mysqllockinginnodb
broadband
broadband

Reputation: 3488

MySQL innodb select for update concurrency

MySQL documentation says that SELECT FOR UPDATE sets an IX lock. IX lock is intention exclusive lock and when issued it means "Transaction T intends to set X (exclusive) locks on scanned rows". This means that before SELECT FOR UPDATE succeeds it must first get IX and then X. MySQL glossary says this about intention exclusive lock:

intention lock

A kind of lock that applies to the table level, used to indicate what kind of lock the transaction intends to acquire on rows in the table. Different transactions can acquire different kinds of intention locks on the same table, but the first transaction to acquire an intention exclusive (IX) lock on a table prevents other transactions from acquiring any S or X locks on the table. Conversely, the first transaction to acquire an intention shared (IS) lock on a table prevents other transactions from acquiring any X locks on the table. The two-phase process allows the lock requests to be resolved in order, without blocking locks and corresponding operations that are compatible. For more details on this locking mechanism, see Section 14.3.5.3, “InnoDB Lock Modes”.

Also IX and IX are compatible (lock type compatibility matrix) which means that if transaction 1 issues IX and right after another concurrent transaction issues IX it will succeed.

Is there a possibility that two concurrent IX are issued at the exact same moment and MySQL grants/acquires IX for both transactions for the same table. Or MySQL grants at any point only one IX if concurrent IX are issued. I'm thinking that MySQL grants only one of them, even if call is made and triggered at MySQL side at the exact same time.

EDIT: basically if I generalize my question: If two (concurrent) sql statements that lock rows (e.g. update, select for update, select lock in share mode, insert, delete) come to MySQL at the exact same time, I suppose MySQL treats them sequentially. Just want to make sure, that I am thinking right how MySQL works internally.

Upvotes: 2

Views: 843

Answers (1)

O. Jones
O. Jones

Reputation: 108641

Locking operations necessarily are serialized. At the nanosecond level in the server's logical workflow there's no such thing as "multiple lock requests at precisely the same moment." Even if there were, the server's logic would arbitrarily place them into some order and grant them one after the other.

A really smart next-generation massively parallel server might be able to figure out that different lock requests were guaranteed never to interfere with each other, and handle them truly in parallel. But for now, there's no such thing as simultaneous.

Upvotes: 2

Related Questions