MySQL Bugs: #114949: Feature request for lock_wait

Bug #114949	Feature request for lock_wait_timeout high resolution second
Submitted:	10 May 2024 5:34	Modified:	15 May 2024 10:30
Reporter:	Tsubasa Tanaka (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S4 (Feature request)
Version:	8.0.37, 8.4.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
This is feature request.

lock_wait_timeout effects MDL(metadata-lock) conflictions.
I'd like to request this parameter get to be able to set fractional seconds.
This improves decreasing our MDL confliction impact.

https://dev.mysql.com/doc/refman/8.4/en/server-system-variables.html#sysvar_lock_wait_time...

How to repeat:
This is feature request.

### Now
mysql80 15> SELECT @@innodb_lock_wait_timeout;
+----------------------------+
| @@innodb_lock_wait_timeout |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.00 sec)

### I hope
mysql80 15> SELECT @@innodb_lock_wait_timeout;
+----------------------------+
| @@innodb_lock_wait_timeout |
+----------------------------+
|                   1.000000 |
+----------------------------+
1 row in set (0.00 sec)

Hello tanaka-San,

Thank you for the feature request!

regards,
Umesh

Can you please share a reproducible example of the conflict you have in mind?

(AFAICT innodb_lock_wait_timeout doesn't affect MDLs - MDLs are usually taken by Server layer, while innodb_lock_wait_timeout only affects locks on recordsand tables taken by InnoDB engine itself)

Simply, I hope to fail ALTER TABLE smoothly.

### Terminal 1
mysql80 25> BEGIN;
Query OK, 0 rows affected (0.01 sec)

mysql80 25> SELECT * FROM t1;  -- Get t1 MDL
+-----+-----+
| num | val |
+-----+-----+
|   1 | one |
|   2 | two |
+-----+-----+
2 rows in set (0.01 sec)

### Terminal2
mysql80 26> ALTER TABLE t1 Engine = InnoDB;  -- I hope this ALTER fails 200ms or 500ms or so
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Because of, during ALTER TABLE is waiting for MDL, the other transactions are blocked by ALTER TABLE's EXCLUSIVE MDL wait.
1 second ALTER TABLE timeout is bit long as I hope.

OK, then I think you are confusing two variables:

lock_wait_timeout 
https://dev.mysql.com/doc/refman/8.4/en/server-system-variables.html#sysvar_lock_wait_time...
which is about MDL maintained by Server layer, and which, I believe affect your scenario with ALTER.

innodb_lock_wait_timeout
https://dev.mysql.com/doc/refman/8.4/en/innodb-parameters.html#sysvar_innodb_lock_wait_tim...
which is about data locks maintained by InnoDB layer, and which for some reason you are quering with `SELECT @@innodb_lock_wait_timeout`. I don't think it is relevant for your scenario.

Ah, now I understand what I was wrong.

My request is about `lock_wait_timeout`, my example (@@innodb_lock_wait_timeout) 
is wrong. It have to be `@@lock_wait_timeout` .
Sorry for bothering you.