Bug #114949 Feature request for lock_wait_timeout high resolution second
Submitted: 10 May 5:34 Modified: 15 May 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

[10 May 5:34] Tsubasa Tanaka
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)
[10 May 6:57] MySQL Verification Team
Hello tanaka-San,

Thank you for the feature request!

regards,
Umesh
[13 May 8:20] Jakub Lopuszanski
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)
[15 May 8:31] Tsubasa Tanaka
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.
[15 May 9:45] Jakub Lopuszanski
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.
[15 May 10:30] Tsubasa Tanaka
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.