0

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-record-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”.

¿what's the difference?

1 Answers1

1

There is no difference. Whether an index is explicit, a primary key, or hidden clustered, the record locks behave the same way.

danblack
  • 12,130
  • 2
  • 22
  • 41
  • hi @danblack, so does the explanation of HOW THE DIFFERENT LOCKING TYPES mentioned in the link also apply if the column HAS NO explicit INDEXES? –  Dec 31 '22 at 03:14
  • 1
    Yes, and for good usage of innodb with locking, try to always be explicit with a primary key. – danblack Dec 31 '22 at 03:52
  • Hi @danblack, The only difference between using indexes on a column or not is that with indexes there are less rows locked and without indexes you lock all the rows of the tables involved, right? –  Dec 31 '22 at 05:28
  • No, you lock all the applicable rows in all indexes, whether clustered (hidden or explict PK), or secondary indexes. – danblack Dec 31 '22 at 07:08