MySQL Bugs: #116254: InnoDB Table lock descrition error

Bug #116254	InnoDB Table lock descrition error
Submitted:	27 Sep 2024 11:33	Modified:	27 Sep 2024 12:14
Reporter:	연호 정	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	8.4	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	descrition error

Description:
Hello!

I've been reading your documentation about MySQL Server, and I've encountered something that I think might be incorrectly written.

In chapter 15.3.6 LOCK TABLES and UNLOCK TABLES Statements, it states:

"When you call LOCK TABLES, InnoDB internally takes its own table lock, and MySQL takes its own table lock. InnoDB releases its internal table lock at the next commit, but for MySQL to release its table lock, you have to call UNLOCK TABLES. You should not have autocommit = 1, because then InnoDB releases its internal table lock immediately after the call of LOCK TABLES, and deadlocks can very easily happen. InnoDB does not acquire the internal table lock at all if autocommit = 1, to help old applications avoid unnecessary deadlocks."

However, there seems to be a contradiction:
1. First, it's stated that InnoDB acquires a table lock and then releases it right after getting it when autocommit = 1.
2. Then, in the next line, it's mentioned that InnoDB does not acquire the internal table lock at all when autocommit = 1.

Both of these situations are described as occurring when autocommit = 1. Is this an error in the documentation?

I'm using MySQL version X.X.X (if applicable).

Thank you for your attention to this matter.

How to repeat:
Hello!

I've been reading your documentation about MySQL Server, and I've encountered something that I think might be incorrectly written.

In chapter 15.3.6 LOCK TABLES and UNLOCK TABLES Statements, it states:

"When you call LOCK TABLES, InnoDB internally takes its own table lock, and MySQL takes its own table lock. InnoDB releases its internal table lock at the next commit, but for MySQL to release its table lock, you have to call UNLOCK TABLES. You should not have autocommit = 1, because then InnoDB releases its internal table lock immediately after the call of LOCK TABLES, and deadlocks can very easily happen. InnoDB does not acquire the internal table lock at all if autocommit = 1, to help old applications avoid unnecessary deadlocks."

However, there seems to be a contradiction:
1. First, it's stated that InnoDB acquires a table lock and then releases it right after getting it when autocommit = 1.
2. Then, in the next line, it's mentioned that InnoDB does not acquire the internal table lock at all when autocommit = 1.

Both of these situations are described as occurring when autocommit = 1. Is this an error in the documentation?

I'm using MySQL version X.X.X (if applicable).

Thank you for your attention to this matter.

Hi Mr. 연호 정,

Hi,

Thank you for your bug report, but why do you think that these two statements are contradictory ???

Here are the both of them ...... literally and ad nauseam:

1.  InnoDB releases its internal table lock immediately after the call of LOCK TABLES

2.  InnoDB does not acquire the internal table lock at all if autocommit = 1

Do note that LOCK TABLES happens at SQL level and all InnoDB locks are occurring in the storage engine level.

We do not yet see a problem here, but if you elucidate your observation a bit more, we might change our minds ........

Thank you for your response. However, I believe there's still a contradiction that needs clarification:

Both statements are specifically about InnoDB's internal table lock under the condition of autocommit = 1:

1. "InnoDB releases its internal table lock immediately after the call of LOCK TABLES"
   This implies that InnoDB acquires a lock and then immediately releases it.

2. "InnoDB does not acquire the internal table lock at all if autocommit = 1"
   This clearly states that InnoDB doesn't acquire a lock at all.

These two statements describe mutually exclusive behaviors for the same scenario (autocommit = 1). How can InnoDB both acquire and immediately release a lock, and also not acquire a lock at all, under the same condition?

I understand that LOCK TABLES operates at the SQL level, but both these statements are specifically about InnoDB's internal behavior at the storage engine level. Could you please explain how these two statements can be reconciled?

Hi,

Actually, we can explain it ........

In the first case, innoDB has started a transaction ....... after START TRANSACTION , application issues LOCK TABLES, which causes innoDB to immediately release its own internal lock.

If, however, there is only autocommit=1 and no LOCK TABLES, the InnoDB does not acquire it's internal table lock.

There is nothing unclear here ......

Thank you for your explanation. However, I believe there's still a significant issue that needs to be addressed:

The original documentation appears to be describing all behaviors under the condition of autocommit=1. However, your explanation introduces a separate scenario involving START TRANSACTION, which implicitly disables autocommit.

This discrepancy between the original document and your explanation is concerning because:

1. It may lead to misunderstandings about InnoDB's locking behavior.
2. It could cause developers to incorrectly implement their transaction and locking strategies.
3. It raises questions about the accuracy of other parts of the documentation.

Could you please address this inconsistency? Specifically:

1. Can you confirm if the original document should be updated to clearly distinguish between these two scenarios (autocommit=1 and START TRANSACTION)?
2. Could you provide clear examples for each scenario to illustrate the different behaviors?
3. How do these different behaviors impact real-world application development?
4. Is this behavior consistent across all versions of MySQL, or specific to certain versions?

Clarifying these points would greatly improve the document's accuracy and usefulness. Thank you for your attention to this matter.

Thank you for your explanation. However, I believe there's still a significant issue that needs to be addressed:

The original documentation appears to be describing all behaviors under the condition of autocommit=1. However, your explanation introduces a separate scenario involving START TRANSACTION, which implicitly disables autocommit.

This discrepancy between the original document and your explanation is concerning because:

1. It may lead to misunderstandings about InnoDB's locking behavior.
2. It could cause developers to incorrectly implement their transaction and locking strategies.
3. It raises questions about the accuracy of other parts of the documentation.

Could you please address this inconsistency? Specifically:

1. Can you confirm if the original document should be updated to clearly distinguish between these two scenarios (autocommit=1 and START TRANSACTION)?
2. Could you provide clear examples for each scenario to illustrate the different behaviors?
3. How do these different behaviors impact real-world application development?
4. Is this behavior consistent across all versions of MySQL, or specific to certain versions?

Clarifying these points would greatly improve the document's accuracy and usefulness. Thank you for your attention to this matter.

Hi,

We shall consult on this matter with our Documentation team.

Please keep in mind that this is Reference Manual and not User's Manual.

What you describe would go into User's Manual, which we do not write nor maintain.

However, it is Documentation team that will decide if anything should be done from our side.