Bug #97885 Session state tracking does not take MDL locks into account

• Submitted: 4 Dec 2019 22:30
• Modified: 5 Dec 2019 11:00
• Reporter: Manuel Ung
• Status: Verified
• Category: MySQL Server: Locking
• Severity: S3 (Non-critical)
• Version: 8.0.18, 5.7.28
• OS: Any
• CPU Architecture: Any

[4 Dec 2019 22:30] Manuel Ung

Description:
As stated in the documentation (https://dev.mysql.com/doc/refman/8.0/en/session-state-tracking.html), session tracking is meant to track session state changes so that we know if a session can be transferred to a different replica.

However, if the user runs LOCK TABLES or GET_LOCK (both now acquire MDL locks), we are not indicating to the client that session state has changed. In this case, it would not be safe to resume the connection on a separate server.

How to repeat:
In mtr:

create table t (i int);
--enable_session_track_info

SET @@session.session_track_state_change=ON;
--echo # expect 1
SELECT GET_LOCK("session_state_change_user_lock", 3);

--reset_connection

SET @@session.session_track_state_change=ON;
--echo # expect 1
LOCK TABLES test.t READ;

--reset_connection

--disable_session_track_info
drop table t;

Suggested fix:
Acquiring an MDL lock should be considered a session state change.

[5 Dec 2019 11:00] MySQL Verification Team

Hello Manuel Ung,

Thank you for the bug report and feedback.

regards,
Umesh