1

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-intention-locks

Record Locks

A record lock is a lock on an index record. For example, SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; prevents any other transaction from inserting, updating, or deleting rows where the value of t.c1 is 10.

Record locks always lock index records, even if a table is defined with no indexes. For such cases, InnoDB creates a hidden clustered index and uses this index for record locking. See Section 15.6.2.1, “Clustered and Secondary Indexes”.

An index is a data structure (behind the scenes it SEES like a small table where each record contains a column with the primary key of the original record, another column with the page where the original record is located in the original table among other columns) from what I understand,

so index record refers to a "node" of that index which is a data structure?

So, you mean record lock uses INDEXES "by default" to PROVIDE MORE PERFORMANCE?

1 Answers1

0

I guess, to understand that sentence, you need to know that InnoDB always stores table data in b-trees, e.g. in indexes, see Clustered and Secondary Indexes:

Each InnoDB table has a special index called the clustered index that stores row data.

[...]

If a table has no PRIMARY KEY or suitable UNIQUE index, InnoDB generates a hidden clustered index named GEN_CLUST_INDEX on a synthetic column that contains row ID values.

So this index exists anyway, and For such cases, InnoDB creates a hidden clustered index and uses this index for record locking, implying the index is created just for locking, might throw you a bit off track.

So to answer your question: MySQL does not lock the index instead of the record because it would provide more performance, but because "locking the record" and "locking the entry in the clustered index" are equivalent.

In addition, MySQL can and will also place locks on secondary indexes. These are your data structures that point to a record in the original table by providing the primary key (or the GEN_CLUST_INDEX). But note that no "page where the original record is located" is needed for this (for InnoDB).

Solarflare
  • 10,721
  • 2
  • 18
  • 35
  • Hi @Solarflare - "So to answer your question: MySQL does not lock the index instead of the record because it would provide more performance, but because "locking the record" and "locking the entry in the clustered index" are equivalent." so the definition of record lock is ONLY TO TELL US HOW RECORDS ARE KEPT BEHIND THE SCENES? –  Dec 29 '22 at 23:24
  • so, @Solarflare index record what does it mean? –  Dec 29 '22 at 23:26
  • 1
    I understood your question as: "mysql locks on indexes, not on records, is it for performance reasons?", and no, its not for performance. But it's hard to know what you know about mysql (e.g. what to base the answer on). But no, the definition of a record lock is not "to tell you how records are kept". Records are kept as entries in an index, that "is" the table. A record *lock* is something MySQL uses for transactions (the page you were visiting is a chapter in "InnoDB Locking and *Transaction Model*"), it "marks" index entries (=index records) for that. But that is too much for a comment. – Solarflare Dec 30 '22 at 00:10
  • So @Solarflare, the term "index record" is to "emphasize" that an innodb table is itself an index and knowing that now, technically it is no longer a simple row but it is called an "index record", right? –  Dec 30 '22 at 06:39
  • 1
    I think you are overthinking this. I'd guess what is emphasized is that the index looks like a table (e.g. the entries are records), not that the table looks like an index. But of course if the table would look differently, the index would not look like a table anymore... But to clarify: the row in the actual table will always be called "record", no matter how it is implemented. It is *also* an index record, but that is only relevant in certain situations (e.g. here). – Solarflare Dec 30 '22 at 10:37
  • sure @Solarflare, great, now it's a little clearer to me, I THINK I TOOK IT TOO LITERAL hehe, I guess. Thanks –  Dec 30 '22 at 17:39