[MDEV-28318] SHOW ANALYZE|EXPLAIN: Lock wait timeout with long GIS computations Created: 2022-04-14  Updated: 2023-12-07

Status: Stalled
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.2, 10.3, 10.4, 10.5, 10.6, 10.8
Fix Version/s: 10.11

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-27021 Extend SHOW EXPLAIN to support SHOW A... Closed

 Description   

Take a statement doing long GIS computation (it takes minutes):

SELECT
 
st_buffer(st_buffer(ST_GEOMFROMTEXT('POLYGON((10 10, 10 20, 20 20, 20 10, 10 10))'),1),
 
(st_length(multilinestringfromtext('  MULTILINESTRING( ( 2 2, 2 8, 8 8, 8 2, 2 2 ), ( 4 4, 4 6, 6 6, 6 4, 4 4 ) ) ')) ));

and try to run SHOW EXPLAIN (or SHOW ANALYZE) on it. The result will be:

MariaDB [test]> show analyze format=json for  398;
 
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Note that KILL or KILL QUERY are able to kill the statement.

 Comments   
Comment by Sergei Petrunia [ 2022-04-14 ]

Investigation:

GIS code uses a "non-standard" way to check if the computation is terminated:

String *Item_func_buffer::val_str(String *str_value)
 
{
 
  ...
 
  operation.killed= (int *) &(current_thd->killed);

...

#define GCALC_TERMINATED(state_var) (state_var && (*state_var))
 
#define GCALC_SET_TERMINATED(state_var, val) state_var= val

..

int Gcalc_scan_iterator::step()
 
{
 
...
 
  if (GCALC_TERMINATED(killed))
 
    GCALC_DBUG_RETURN(0xFFFF);

This logic allows to check if the thread is killed but doesn't check if there's a need to process SHOW EXPLAIN query.

Comment by Sergei Petrunia [ 2022-04-27 ]

Note that this is reproducible with older versions as well. Any version that supports SHOW EXPLAIN is affected.

Generated at Thu Feb 08 09:59:47 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.